JDBC使用SELECT FOR UPDATE锁定行，但不起作用

Question

我在使用MySQL的SELECT .. FOR UPDATE时遇到了问题，以下是我尝试运行的查询：

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

接下来，相关的线程将执行 UPDATE 并更改 HostName，之后应该解锁该行。

我正在运行一个多线程的 Java 应用程序，所以有 3 个线程运行此 SQL 语句，但当线程 1 运行此语句时，它没有锁定线程 2 和 3 的结果。因此，线程 2 和 3 获取相同的结果，并且它们可以更新同一行。

此外，每个线程都在自己的 mysql 连接上。

我使用 Innodb，并设置 transaction-isolation = READ-COMMITTED，而在执行 select for update 之前关闭了 Autocommit。

我可能遗漏了什么？或者也许有更好的解决方案？非常感谢。

代码：

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

线程：

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

}

选择：

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

请帮忙...

结果：

Thread-1: 开始。 Thread-2: 开始。 [从 SELECT Lock 获取的输出]...threadName=Thread-1 新连接.. [从 SELECT Lock 获取的输出]...threadName=Thread-2 新连接.. 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 [执行 UPDATE] ... oid = 1, thread=Thread-2 新连接.. [执行 UPDATE] ... oid = 1, thread=Thread-1 pst_threadname=Thread-2: UPDATE b2bicheckpoint SET HostName='1_host_Thread-2',UpdateTimestamp=1294940161838 where OID = 1 新连接.. pst_threadname=Thread-1: UPDATE b2bicheckpoint SET HostName='1_host_Thread-1',UpdateTimestamp=1294940161853 where OID = 1

- Rachid

---

你是如何通过编程实现这个的？ - Buhake Sindi

Answer 1

你感到非常困惑，但在你的编辑后，情况至少看起来更好了。有多种方法可以做到这一点，但我发现最好的方法是实际使用JDBC的ResultSet.update*方法：

首先，你需要准备好带有ResultSet.CONCUR_UPDATABLE参数的SELECT ... FOR UPDATE语句，就像这样：

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

接下来，您需要使用ResultSet实际更新表格：

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

第三，你可能需要使用事务，我可以在你的更新中看到。希望你的DbUtil.close方法不会抛出任何异常，检查是否为null等。另外，如果你的方法变得更加复杂，也应该有回滚逻辑。

你不应该因为任何原因修改my.ini。

- Christopher Schultz

Answer 2

你创建的选择更新的连接需要与用于执行更新的连接相同。否则它不属于同一事务，并释放锁定，因此其他线程也开始执行它。因此，在你的代码中，你需要这样做：

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

- Hiro2k

---

嗨Hiro2k，感谢您的回答。我无法更改数据库服务器配置，我的意思是我无法更改my.ini文件。但是在MySQL手册中，他们说如果设置了READ-COMMITTED，则只会锁定索引，这在我的情况下是可以的，因为HostName列是一个索引.... - Rachid

请您能否把您的示例代码发给我，这样我可以与我的代码进行比较？ - Rachid

嗨，Rachid，我不能分享代码，但我刚想起你可以使用setTransactionIsolation更改连接的隔离级别。试试看它是否适用于你。 - Hiro2k

我只是为了测试而更改了my.ini，但它仍然表现相同，我的意思是两个线程都可以在同一行上运行select FOR UPDATE，然后执行更新。因此，两个更新在同一行上执行！正如我在控制台中看到的：pst_threadname = Thread2_：UPDATE b2bicheckpoint SET HostName ='1_host_Thread-2'，UpdateTimestamp = 1294940161838 WHERE OID = 1pst_threadname = Thread1_：UPDATE b2bicheckpoint SET HostName ='1_host_Thread-1'，UpdateTimestamp = 1294940161853 WHERE OID = 1 - Rachid

提交会释放您的行上的锁定。然而，我刚看到您的代码的部分执行更新，它在自己的方法中，所以你的问题就在那里！您需要将已创建的连接传递给该方法，而不是创建一个新连接。否则就像您启动了新事务一样。 - Hiro2k

Answer 3

选择语句不能产生由任何分组或排序操作引起的工作结果集，这会导致排序。选择必须保持对表的活动位置，任何排序都无法提供。

- Douglas Partch