我在使用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