Java – JDBC uses select for update to lock a row, which does not work

I have a select problem with MySQL For update, this is the query I tried to run:

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

After that, the related thread will update and change the hostname, and then it should unlock the row

I am running a multithreaded Java application, so three threads are running this SQL statement, but when thread 1 runs it, it will not get its result from thread 2 & therefore, thread 2 & 3 get the same result, and they can update the same line

Each thread also has its own MySQL connection

I am using InnoDB, transaction isolation = read committed, and auto commit is closed before select for update

May I miss anything? Maybe there's a better solution? Thank you.

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);        
    }

}

choice:

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

Solution

You're very confused, but at least things look better after editing There are many ways to do this, but I found that the best way is to actually use JDBC's resultset Update * method:

First, you need to use resultset CONCUR_ The updatable parameter prepares the select... For UPDATE statement as follows:

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

Then, you must actually update the table with the resultset:

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

Third, you may need to use the transactions I can see in the update I hope your dbutil The close method does not throw any exceptions, check for nulls, and so on In addition, if your method becomes more complex, you should also have rollback logic

You should not modify my for any reason ini.