Java multiple reads on same SQL table

Question

I want to migrate my app to clusters so I have to deal with mysql locking. For this post, I have simple table with 'id' and 'flag' whether is record updated or not. There are 3 threads reading table of 5 records. What I want to achieve is that each thread reads only record not being read by other thread. (Note that threads are only for testing as in real life, there will be separate process/apps to doing this).

What I've read about Mysql locking is that is should disable autoCommit and SELECT record with LOCK.

Here is my example code for thread read:

public Long read(Connection c) throws Exception {
    c.setAutoCommit(false);
    c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    PreparedStatement sth = c.prepareStatement("SELECT * FROM test WHERE flag!='Y' LIMIT 1 FOR UPDATE SKIP LOCKED") ;
    ResultSet rst = sth.executeQuery() ;
    Long id = null ;
    if (rst.next()) {
        id = rst.getLong("id");
        sth = c.prepareStatement("UPDATE test SET FLAG='Y' WHERE id=" + id) ;
        sth.executeUpdate();
        System.out.println(getName() + ": Commited id " + id);
    }
    c.commit();
    sth.close();
    return id ;
}

When running test with 3 threads the output is:

LockThread#2: Commited id 1
LockThread#2: Commited id 2
LockThread#3: Commited id 1
LockThread#2: Commited id 3
LockThread#1: Commited id 1
LockThread#1: Commited id 4
LockThread#3: Commited id 4
LockThread#2: Commited id 4
LockThread#3: Commited id 5
LockThread#2: Commited id 5
LockThread#1: Commited id 5

So the same record is not read only by one thread. Is there any other trick to make an SQL transaction in Java. How can I lock record upon retrieval, so next thread will read next non-locked record.

Answer 1

You should try with manual transaction like:

c.setAutoCommit(false);
c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
Statement sth = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE) ;
sth.execute("BEGIN");
ResultSet rst = sth.executeQuery("SELECT * FROM test WHERE flag!='Y' LIMIT 1 FOR UPDATE SKIP LOCKED") ;
Long id = null ;
if (rst.next()) {
    id = rst.getLong("id") ;
    rst.updateString("flag", "Y");
    rst.updateRow();
}
sth.execute("COMMIT");
return id ;