Java multiple threads database access

If you want to SQL SELECT a row from a database, and then later UPDATE the same row, you have 2 choices as a Java developer.

1. SELECT with ROWLOCK, or whatever the row lock syntax is for your particular data base.

2. SELECT the row, do your processing, and just before you're ready to update, SELECT the row again with ROWLOCK to see if any other thread made changes. If the two SELECTS return the same values, UPDATE. If not, throw an error or do your processing again.

The problem you are facing is transaction isolation.

Seems like you need to have each thread lock the row concerned in the where clause, which requires serializable isolation.

This should be dealt with primarily within the DB, by configuring the desired transaction isolation level. Then on top of this, you need to select your locking strategy (optimistic or pessimistic).

Without transaction isolation, you will have a hard time trying to ensure transaction integrity solely in the Java domain. Especially taking into consideration that even if the DB is currently accessed only from your Java app, this may change in the future.

Now as to which isolation level to choose, from your description it might seem that you need the highest isolation level, serializable. However, in practice this tends to be a real performance hog due to extensive locking. So you may want to reevaluate your requirements to find the best balance of isolation and performance for your specific situation.

I tumbled into this problem when working with a multi-threaded Java program that was using a Sqllite database. It uses file locking so I had to make sure that only one thread was doing work at the same time.

I basically ended up with using synchronized. When the ConnectionFactory returns a db connection, it also returns a lock object that one should lock when using the connection. So you could do synchronization lock manually, or create a subclass of the class below which does it for you:

 /**
  * Subclass this class and implement the persistInTransaction method to perform
  * an update to the database.
  */
 public abstract class DBOperationInTransaction {

     protected Logger logger = Logger.getLogger(DBOperationInTransaction.class.getName());

     public DBOperationInTransaction(ConnectionFactory connectionFactory) {
         DBConnection con = null;

         try {
             con = connectionFactory.getConnection();

             if(con == null) {
                 logger.log(Level.SEVERE, "Could not get db connection");
                 throw new RuntimException("Could not get db connection");
             }

             synchronized (con.activityLock) {
                 con.connection.setAutoCommit(false);
                 persistInTransaction(con.connection);
                 con.connection.commit();
             }

         } catch (Exception e) {
             logger.log(Level.SEVERE, "Failed to persist data:", e);
             throw new RuntimeException(e);
         } finally {
             if(con != null) {
                 //Close con.connection silently.
             }
         }
     }

     /**
      * Method for persisting data within a transaction. If any SQLExceptions
      * occur they are logged and the transaction is rolled back.
      * 
      * In the scope of the method there is a logger object available that any
      * errors/warnings besides sqlException that you want to log.
      * 
      * @param con
      *            Connection ready for use, do not do any transaction handling
      *            on this object.
      * @throws SQLException
      *             Any SQL exception that your code might throw. These errors
      *             are logged. Any exception will rollback the transaction.
      * 
      */
     abstract protected void persistInTransaction(Connection con) throws SQLException;

 }

And the DBConnection struct:

 final public class DBConnection {
     public final Connection connection;
     public final String activityLock;

     public DBConnection(Connection connection, String activityLock) {
         this.connection = connection;
         this.activityLock = activityLock;
     }

 }