Java - MySQL concurrency solution needed

Question

I am developing a java application that uses MySQL to write and read some data. After a while (very long time) my jdbc connection closes, i was reading on many forums and i still cannot make it last forever.

What i want to do is the next ting: After 5,6,24 hours (still don't know how many hours) i will close the connection and connect again. The problem is that if another thread tries to use that connection to write something it will fail on exception. So i want to do is the next thing if the jdbc connection is restarting all the threads that need to use the jdbc wait until the reconnect is finished. Also i don't want to block the thread if another thread uses that connection, only if is restarting. Also i am afraid of deadlocks :).

I was reading about java concurrency and how to manage it from many forums and still don't know what to use and how to use it.

Useful forums:

http://www.vogella.com/tutorials/JavaConcurrency/article.html

http://en.wikipedia.org/wiki/Java_concurrency

http://tutorials.jenkov.com/java-concurrency/index.html

Edit:

To make it more clear what i want to do

Java Database Class:

    public class Database {

        //Locks
        final Object readLock = new Object();
        final Object writeLock = new Object();
        final Object readWriteLock = new Object();
        boolean enableReadLock = false;
        boolean enableWriteLock = false;
        //Database parametars
        String user; //DB username /корисник на базата
        String password; //DB password /лозинка за базата
        String dbname; //DB name / име на базата
        String ip; //DB Server IP address / адреса на серверот каде е базата
        Connection connection; //mysql connection /


        public Database(String user, String password, String dbname, String ip) throws ClassNotFoundException {
            Class.forName("com.mysql.jdbc.Driver");
            this.user = user;
            this.password = password;
            this.dbname = dbname;
            this.ip = ip;
        }


        public Database(String user, String password, String dbname) throws ClassNotFoundException {
            Class.forName("com.mysql.jdbc.Driver");
            this.user = user;
            this.password = password;
            this.dbname = dbname;
            this.ip = "localhost";
        }


        public void setReadLock(boolean enable) {
            enableReadLock = enable;
        }

        /**
         * Enable or disable write lock
         *
         * @param enable true -> enable , false -> disable
         */
        public void setWriteLock(boolean enable) {
            enableWriteLock = enable;
        }

        /**
         * Enable or disable read-write lock
         *
         * @param enable true -> enable , false -> disable
         */
        public void setReadWriteLock(boolean enable) {
            enableReadLock = enable;
            enableWriteLock = enable;
        }

        public void reconnect() throws SQLException {
             connection.close();
             connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbname, user, password);
    }

        public void connect() throws SQLException {
            connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbname, user, password);
        }


        public void disconnect() throws SQLException {
            connection.close();
        }



        /**
         * Test connection with mysql server
         *
         * @return (boolean) true -> OK, false -> NOK
         */
        public boolean testConnection() {
            String SQL = "SELECT 1";
            try {
                PreparedStatement statement = connection.prepareStatement(SQL);
                ResultSet rs = statement.executeQuery();
                while (rs.next()) {
                }
            } catch (SQLException ex) {
                return false;
            } catch (NullPointerException ex){
                return false;
            }
            return true;
        }


        public ArrayList<HashMap<String, String>> executeSelect(String SQL) throws SQLException {
            if (enableReadLock && enableWriteLock) { //Доколку има read-write lock
                synchronized (readWriteLock) {
                    return select(SQL);
                }
            } else {
                if (enableReadLock) { //Доколку има read-lock
                    synchronized (readLock) {
                        return select(SQL);
                    }
                } else {
                    return select(SQL);
                }
            }
        }


        private ArrayList<HashMap<String, String>> select(String SQL) throws SQLException {
            ArrayList<HashMap<String, String>> results = new ArrayList<>();
            HashMap<String, String> row;
            PreparedStatement statement = connection.prepareStatement(SQL);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int no_columns = rsmd.getColumnCount();
            while (rs.next()) {
                row = new HashMap<>();
                for (int i = 1; i <= no_columns; i++) {
                    row.put(rsmd.getColumnName(i), rs.getString(i));
                }
                results.add(row);
            }
            statement.close();
            rs.close();
            return results;
        }


        public long executeInsert(String SQL) throws SQLException {
            if (enableReadLock && enableWriteLock) {
                synchronized (readWriteLock) {
                    return insert(SQL);
                }
            } else {
                if (enableWriteLock) {
                    synchronized (writeLock) {
                        return insert(SQL);
                    }
                } else {
                    return insert(SQL);
                }
            }
        }


        private long insert(String SQL) throws SQLException {
            long ID = -1;
            PreparedStatement statement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate();
            ResultSet rs = statement.getGeneratedKeys();
            if (rs.next()) {
                ID = rs.getLong(1);
            }
            statement.close();
            return ID;
        }


        public int executeUpdate(String SQL) throws SQLException {
            if (enableReadLock && enableWriteLock) {
                synchronized (readWriteLock) {
                    return update(SQL);
                }
            } else {
                if (enableWriteLock) {
                    synchronized (writeLock) {
                        return update(SQL);
                    }
                } else {
                    return update(SQL);
                }
            }
        }


        private int update(String SQL) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(SQL);
            int rez = statement.executeUpdate(SQL);
            statement.close();
            return rez;
        }


        public int executeDelete(String SQL) throws SQLException {
            if (enableReadLock || enableWriteLock) {
                synchronized (readWriteLock) {
                    synchronized (readLock) {
                        synchronized (writeLock) {
                            return delete(SQL);
                        }
                    }
                }
            } else {
                return delete(SQL);
            }
        }


        private int delete(String SQL) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(SQL);
            int rez = statement.executeUpdate(SQL);
            statement.close();
            return rez;
        }
    }

In reconnect method i will need some lock or something that will make everyone who call select, update, insert or delete methods to wait (block) until the reconnect is finished.

RMachnik · Accepted Answer · 2014-02-20 10:39:46Z

Try this oracle jdbc multithreading tutorial. Or simply user Apache connection pool

Multi-threading will improve your performance but there are a couple of things you need to know:

Each thread needs its own JDBC connection. Connections can't be shared between threads because each connection is also a transaction. Upload the data in chunks and commit once in a while to avoid accumulating huge rollback/undo tables. Cut tasks into several work units where each unit does one job. To elaborate the last point: Currently, you have a task that reads a file, parses it, opens a JDBC connection, does some calculations, sends the data to the database, etc.

What you should do:

One (!) thread to read the file and create "jobs" out of it. Each job should contains a small, but not too small "unit of work". Push those into a queue The next thread(s) wait(s) for jobs in the queue and do the calculations. This can happen while the threads in step #1 wait for the slow hard disk to return the new lines of data. The result of this conversion step goes into the next queue One or more threads to upload the data via JDBC. The first and the last threads are pretty slow because they are I/O bound (hard disks are slow and network connections are even worse). Plus inserting data in a database is a very complex task (allocating space, updating indexes, checking foreign keys)

Using different worker threads gives you lots of advantages:

It's easy to test each thread separately. Since they don't share data, you need no synchronization. The queues will do that for you You can quickly change the number of threads for each step to tweak performance

Thanks @Rafik991 it will change my concept, for better performance i hope.

Pablo Francisco Pérez Hidalgo · Accepted Answer · 2014-02-20 11:46:33Z

1

I think the easiest solution is to mark all your methods using the resource "connection" with the synchronized flag. http://docs.oracle.com/javase/tutorial/essential/concurrency/syncmeth.html

For example:

Class YourClass
{
  public synchronized boolean reconect(...) { ...}
  public synchronized String getData (...) {...}
}

This locks this object for the flagged methods so you should to encapsulate your connection in a small class like this or to just lock your connection object: http://docs.oracle.com/javase/tutorial/essential/concurrency/locksync.html

Class YourClass
{
  public boolean reconect(...) {
   synchronized(con) 
   {
     ...
   }
  }

  public String getData (...) {
    synchronized(con) 
    {
     ...
    }
  }
  private Connection con;
}

These synchronized zones will never run at the same time.

EDIT: Considering you want protect reconnect against other db operations, you should consider the use of a semaphore: http://docs.oracle.com/javase/7/docs/api/java/util/concurrent/Semaphore.html

Class YourClass
{
  YourClass() {
    sem = new Semaphore(1);
  }

  public boolean reconect(...) {
     sem.acquire();
     ...
     sem.release();
  }

  public String getData (...) {
    synchronized(sem) 
    {
      if(sem.availablePermits()>0) sem.reducePermits(1);
    }
    ...
    ...
    synchronized(sem) 
    {
      sem.release();
    }
  }
  private Connection con;
  Semaphore sem;
}

edited Feb 20, 2014 at 11:46

answered Feb 20, 2014 at 10:38

Pablo Francisco Pérez Hidalgo

27.8k8 gold badges39 silver badges64 bronze badges

3 Comments

AdrianES Over a year ago

This is easy i agree :) but if a thread want to use method getData() (in your case) and another thread is using it (not the reconnect method) it will wait until the other thread release the lock (synchronized). Correct me if i understood something wrong.

Pablo Francisco Pérez Hidalgo Over a year ago

@AdrianES You are right but I don't think that is such a bad behaviour in your code. If what you want is to avoid reconnect and "the other methods" to run at the same time you could protect an Integer (counter) over increment, decrement and acquire operations (using the second mechanism described above) only unlocking acquisituin requester when this counter is 0... just like a semaphore

AdrianES Over a year ago

Can u edit your code and show me how u can use this counter? I also think that i should combine 2 concurrency mechanisms to get the thing i need.

Collectives™ on Stack Overflow

Java - MySQL concurrency solution needed

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related