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