MYSQL SELECT ... FOR UPDATE 不起作用？
我遇到了 MySQL 的 SELECT .. FOR UPDATE 问题，这是我尝试运行的查询：
SQL = "SELECT * " +
"FROM " + TableName + " " +
"WHERE out_status IN ("+outSStatus+") AND queued <= NOW() " +
"ORDER BY out_status, id_queue ASC "+ limitSql+
"FOR UPDATE";
此后，线程将执行 UPDATE 并将 out_status 更改为 99，然后它应该解锁该行。
我正在运行一个多线程java应用程序，因此有3个线程正在运行此SQL语句，但是当线程1运行此语句时，它不会锁定（隐藏）线程2和线程2的结果。 3. 因此线程2和线程2 3 得到相同的结果。
此外，每个线程都在其自己的 mysql 连接上。
有人可以帮我解决这个问题吗？或者也许有更好的解决方案？
非常感谢。
I am having issues with MySQL's SELECT .. FOR UPDATE, here is the query I am trying to run:
SQL = "SELECT * " +
"FROM " + TableName + " " +
"WHERE out_status IN ("+outSStatus+") AND queued <= NOW() " +
"ORDER BY out_status, id_queue ASC "+ limitSql+
"FOR UPDATE";
After this, the thread will do an UPDATE and change the out_status to 99, 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 (hide) its results from thread 2 & 3. Therefore threads 2 & 3 are getting the same results.
Also each thread is on its own mysql connection.
Can anybody please help me with this? OR perhaps have a better solution?
Much Appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以使用事务
You can use transactions
我有类似的问题。来自@Vikash 和@Adeel Ansari 的两个答案都是有效的建议。但是，我通过使用
InnoDB引擎而不是MyISAM解决了这个问题。MyISAM只允许在表级别上锁定，而不允许在行级别上锁定。使用InnoDB时，您仍然需要事务。I had a similar issue. Both answers, from @Vikash and @Adeel Ansari, are valid suggestions. However, I solved the issue by using
InnoDBengine instead ofMyISAM.MyISAMonly allows locking on table level, not on row level. You still need transactions, when usingInnoDB.它可能正在工作，但你无法注意到。假设线程 1 执行了该语句，并自动提交了事务，因为自动提交已打开。当然，线程 2 也可以运行它。
尝试使用 Connection类的这个方法然后看结果。
It might be working, and you are unable to notice. Say thread 1 executed the statement, and committed the transaction automatically, because
auto-commitwas on. Then of course thread 2 would be able to run that too.Try setting
auto-commitoff, using this method ofConnectionclass and then see the result.