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