防止使用 SELECT...LOCK IN SHARE MODE 的 php 应用程序中出现 mysql 死锁
堆栈：
如果我正确理解 SELECT ... LOCK IN SHARE MODE，您可以将其放入 mysql 事务中以选择您将在该事务期间使用的行，以便从其他会话的写入中“锁定”这些选定的行/删除操作（但其他会话仍然可以读取行），直到事务完成，此时使用 SELECT LOCK IN SHARE MODE 语句锁定的行将被释放，以便其他会话可以访问它们以进行写入/删除等。
这正是我想要什么对于我的评论表。每当将评论添加到我网站上的帖子时，我需要锁定与该帖子相关的所有评论行，同时更新所有锁定行上的一些元数据。如果同时提交两条评论，我不希望它们同时访问相关的评论行，因为它们基本上会互相搞砸（和元数据）。因此，我想将 SELECT LOCK IN SHARE MODE 合并到评论上传脚本中，以便在查询中运行锁定的第一个会话可以完全控制评论行，直到完成整个事务（并且稍慢的脚本必须等到从第一个脚本执行开始的整个事务）。
我很担心脚本 A 会锁定脚本 B 需要的数据，而脚本 B 会锁定脚本 A 需要的数据，从而造成死锁。如何在我的应用程序中解决这个问题？
另外，我在我的网站数据库中只使用innodb，所以我不需要担心表锁，对吗？
Stack:
If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction in order to "lock out" those selected rows from other session's writing/deleting actions (but other sessions can still read the rows) until your transaction completes, at which point the rows that were locked with the SELECT LOCK IN SHARE MODE statement are released so other sessions can access them for writing/deleting etc.
This is exactly what I want for my comments table. Whenever a comment is added to a post on my site, I need to lock all the comment rows tied to that post while I update some meta data on all the locked rows. And if two comments get submitted at the same time, I don't want them to both have access to the relevant comment rows at the same time because they will basically screw each other (and the meta data) up. So I want to incorporate SELECT LOCK IN SHARE MODE into the comment upload script so the first session to run the lock in query gets complete control over the comment rows until it finishes the entire transaction (and the script that was slightly slower has to wait until the entire transaction from the first script executes).
I am a but concerned about creating deadlocks where script A locks data that script B needs, and script B locks data that script A needs. How can I get around this in my application?
Also, I am using only innodb in my website database, so I don't need to worry about table locks correct?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在 MySQL 文档中，页面 14.6.8.1。 InnoDB 锁定模式 讨论了（靠近页面底部）第一个客户端使用“LOCK IN SHARE MODE”请求读锁和第二个客户端由于删除请求写锁而导致的死锁情况。第二个客户端被第一个客户端的读锁阻塞，因此它的写锁排队。但是当第一个客户端尝试删除时，会发生以下情况：
如果我理解正确，我认为第一个客户端可以使用 用于更新而不是“锁定共享模式”。这会导致第一个客户端从一开始就获得写锁，然后它就不必等待第二个客户端。
在查询和更新语句周围使用事务，锁将一直保持到您提交事务为止。不需要其他表锁。
In the MySQL documentation, the page 14.6.8.1. InnoDB Lock Modes discusses (near the bottom of the page) a deadlock situation caused by the first client requesting a read lock with "LOCK IN SHARE MODE" and the second client requesting a write lock because of a delete. The second client is blocked by the first client's read lock, so its write lock is queued up. But when the first client then tries to do a delete, the following happens:
If I understand this correctly, I think the problem can be solved by the first client using FOR UPDATE instead of "LOCK IN SHARE MODE". This causes the first client to get a write lock from the beginning, and then it never has to wait on the second client.
Use a transaction around the query and update statements, and the lock will be held until you commit the transaction. No other table locks should be necessary.