使用 join 时 MySQL 锁定
在 2 表连接查询中选择“FOR UPDATE”时，我对 mysql/innodb 锁定有疑问。示例：
2 个表 - items、queue。 queue.id 是与 items.id（外键）的 1:1 连接。 queue.status 是带有索引的枚举。队列的行数很少，而项目表相对较大。
SELECT *
FROM `items`
INNER JOIN queue
ON items.id = queue.id
WHERE queue.status = 'new'
FOR UPDATE;
- 表
items中的选定行是否会被独占锁定？ - 还获得了哪些其他锁？ （显然
queue表中所有具有 stats='new' 的行除外）。 - 是否存在可能导致死锁的共享锁。我在某处读到，插入将下一个键共享锁放在自动增量索引上，然后当使用 SELECT ... FOR UPDATE （在同一事务中）时，锁将其升级为独占，这很容易发生死锁 - 2 个线程可以获得共享锁，然后它们都将等待对方释放锁以获得排它锁。在这种情况下有可能吗（也听说外键可以做共享锁）。
I have question about mysql/innodb locking when selecting "FOR UPDATE" in a 2 table join query. Example:
2 tables - items, queue. queue.id is 1:1 connection to items.id (FOREIGN KEY). queue.status is enum with index. queue has very little number of rows, while items table is relatively large.
SELECT *
FROM `items`
INNER JOIN queue
ON items.id = queue.id
WHERE queue.status = 'new'
FOR UPDATE;
- Will selected rows in table
itemsbe exclusively locked? - What other locks are obtained? (except obviously all rows in
queuetable that have stats='new'). - Are there any shared locks that could cause deadlock. I've read somewhere that insert puts next key shared lock on the autoincrement index and then when using
SELECT ... FOR UPDATE(in the same transaction) the lock is upgraded it to exclusive, it's easy a deadlock to happen - 2 threads can get the shared lock and then they both will wait each other to release the lock in order to get the exclusive lock. is it possible in this case (also heard that foreign keys make shared locks).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，每个选定的行（*）都将被锁定。
您确实不需要担心僵局。创建一个需要相当多的时间，而当它真的发生时，主要是客户的错。
Yes every selected row(*) will be locked.
You really do not need to worry about a deadlock. It takes quite a lot to create one and when it does happend, it's mainly the clients fault.