JDBC Lock a row using SELECT FOR UPDATE, doesn't work
- by Rachid
I am having issues with MySQL's SELECT .. FOR UPDATE, here is the query I am trying to run:
SELECT * FROM tableName WHERE HostName='UnknownHost'
ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE
After this, the concerned thread will do an UPDATE and change the HostName, which is then it should unlock the row.
I am running a multi-threaded java application, so 3 threads are running this SQL statement, but when thread 1 runs this, it doesn't lock its results from thread 2 & 3. Therefore threads 2 & 3 are getting the same results and they could update the same row.
Also each thread is on its own mysql connection.
I'm using Innodb, with transaction-isolation = READ-COMMITTED, and the Autocommit is off before executing the select for update
may I miss something? OR perhaps there is a better solution?
Thanks a lot.
Code :
public BasicJDBCDemo()
{
Le_Thread newThread1=new Le_Thread();
Le_Thread newThread2=new Le_Thread();
newThread1.start();
newThread2.start();
}
Thread :
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);
}
}
Select :
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
