JDBC Lock row using SELECT FOR UPDATE, not working

Question

I am having problems with MySQL SELECT .. FOR UPDATE, here is the query I'm trying to run:

SELECT * FROM tableName WHERE HostName='UnknownHost' ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE 

After that, the corresponding thread will perform an UPDATE and change the host name, and then it must unlock the line.

I am running a multi-threaded java application, so the three threads work with this SQL statement, but when thread 1 starts it, it does not block its results from threads 2 and 3. Therefore, threads 2 and 3 get the same results and they can update the same string.

Each thread also has its own mysql connection.

I am using Innodb with transactional isolation = READ-COMMITTED, and Autocommit is turned off before making the selection for upgrade

Can I skip something? Or maybe there is a better solution? Many thanks.

The code:

 public BasicJDBCDemo() { Le_Thread newThread1=new Le_Thread(); Le_Thread newThread2=new Le_Thread(); newThread1.start(); newThread2.start(); } 

Subject:

 class Le_Thread extends Thread { public void run() { tring name = Thread.currentThread().getName(); System.out.println( name+": Debut."); long oid=Util.doSelectLockTest(name); Util.doUpdateTest(oid,name); } } 

Choose:

 public static long doSelectLockTest(String threadName) { System.out.println("[OUTPUT FROM SELECT Lock ]...threadName="+threadName); PreparedStatement pst = null; ResultSet rs=null; Connection conn=null; long oid=0; try { String query = "SELECT * FROM table WHERE Host=? ORDER BY Timestamp asc limit 1 FOR UPDATE"; conn=getNewConnection(); pst = conn.prepareStatement(query); pst.setString(1, DbProperties.UnknownHost); System.out.println("pst="+threadName+"__"+pst); rs = pst.executeQuery(); if (rs.first()) { String s = rs.getString("HostName"); oid = rs.getLong("OID"); System.out.println("oid_oldest/host/threadName=="+oid+"/"+s+"/"+threadName); } } catch (SQLException ex) { ex.printStackTrace(); } finally { DBUtil.close(pst); DBUtil.close(rs); DBUtil.close(conn); } return oid; } 

Please, help....:

Result:

  Thread-1: Debut.
 Thread-2: Debut.
 [OUTPUT FROM SELECT Lock] ... threadName = Thread-1
 New connection ..
 [OUTPUT FROM SELECT Lock] ... threadName = Thread-2
 New connection ..
 pst = Thread-2: SELECT * FROM b2biCheckPoint WHERE HostName = 'UnknownHost' ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE
 pst = Thread-1: SELECT * FROM b2biCheckPoint WHERE HostName = 'UnknownHost' ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE
 oid_oldest / host / threadName == 1 / UnknownHost / Thread-2
 oid_oldest / host / threadName == 1 / UnknownHost / Thread-1
 [Performing UPDATE] ... oid = 1, thread = Thread-2
 New connection ..
 [Performing UPDATE] ... oid = 1, thread = Thread-1
 pst_threadname = Thread-2: UPDATE b2bicheckpoint SET HostName = '1_host_Thread-2', UpdateTimestamp = 1294940161838 where OID = 1
 New connection ..
 pst_threadname = Thread-1: UPDATE b2bicheckpoint SET HostName = '1_host_Thread-1', UpdateTimestamp = 1294940161853 where OID = 1

Answer 1

You are very confused, but at least everything looks better after your changes. There are several ways to do this, but the best way I've found is to actually use the JDBC ResultSet.update* methods:

First you need to prepare the SELECT ... FOR UPDATE for the ResultSet.CONCUR_UPDATABLE argument, for example:

 ps = conn.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); 

Then you should actually update the table using the ResultSet:

 if(rs.next()) { rs.updateString(columnIndex, "new_hostname"); rs.updateRow(); } 

Third, you probably need to use the transaction that I see in your update. We hope that your DbUtil.close methods will not throw any exceptions, check for null, etc. Also, if your method becomes more complex, you should also have rollback logic.

You do not need to modify my.ini for any reason.

Answer 2

The connection you create that chooses to upgrade should be the same as the upgrade. Otherwise, it is not part of the same transaction and releases the lock, so your other threads begin to execute it. So in your code you need to do this:

 if (rs.first()) { String s = rs.getString("HostName"); oid = rs.getLong("OID"); System.out.println("oid_oldest/host/threadName=="+oid+"/"+s+"/"+threadName); } Util.doUpdateTest(oid,name,conn); conn.commit(); 

Answer 3

Ok, here is my new selectLockTest method:

the code:

 public static long doSelectLockTest(String threadName) { System.out.println("[OUTPUT FROM SELECT Lock ]...threadName=" + threadName); PreparedStatement pst = null; ResultSet rs = null; Connection conn = null; long oid = 0; try { String query = "SELECT * FROM table WHERE Host=? ORDER BY UpdateTime asc limit 1 FOR UPDATE"; conn = getNewConnection(); conn.setAutoCommit(false); pst = conn.prepareStatement(query); pst.setString(1, DbProperties.UnknownHost); rs = pst.executeQuery(); if (rs.first()) { String s = rs.getString("HostName"); oid = rs.getLong("OID"); //Start update then commit if (oid != 0) { query = "UPDATE b2bicheckpoint SET HostName=?,UpdateTimestamp=? where OID = ?"; pst = conn.prepareStatement(query); pst.setString(1, oid + "_host_" + threadName); pst.setLong(2, getCurrentLongTime()); pst.setLong(3, oid); System.out.println("Select_Prestatement=" + threadName + "__" + pst); int result = pst.executeUpdate(); conn.commit(); conn.setAutoCommit(true); } } } catch (SQLException ex) { ex.printStackTrace(); } finally { DBUtil.close(pst); DBUtil.close(rs); DBUtil.close(conn); } return oid; } 

Result:

 [OUTPUT FROM SELECT Lock ]...threadName=Thread-1 [OUTPUT FROM SELECT Lock ]...threadName=Thread-2 Select_Prestatement=Thread-1_ : SELECT * FROM ..... FOR UPDATE Select_Prestatement=Thread-2_: SELECT * FROM ...... FOR UPDATE Select_Prestatement=Thread-1_: UPDATE table SET HostName='host_Thread-1' where OID = 1 

This means that two variants of SELECT FOR UPDATE were performed (on stream), but only one update was performed, and the thread_1 host was saved. This is better, but is this the expected behavior?

thanks

Answer 4

The Select statement cannot result in a result set that is caused by any group or order by an operation that causes sorting. The choice should keep the active position on the table up, which no sorting will provide.