mysql - innoDB 如何获取多行和子查询的锁？

Question

当我们的服务器处理两个特定请求 1 和 2 时，我们遇到了死锁情况。

数据库有表order_item和product. 对order_item有外键约束product。

(1) 启动手动 SQL 事务并插入多个order_items. product因此，此事务按顺序请求对各个 PRIMARY 索引进行多个锁定。

INSERT INTO order_item (...) VALUES (...)
INSERT INTO order_item (...) VALUES (...)
...

(2) 是一个大的 UPDATE 语句，它更新大多数products，但也扫描order_items 和其他一些表。

UPDATE product
LEFT JOIN (SELECT ... FROM order_item ... INNER JOIN product ...)
...

现在我们陷入了僵局。SHOW ENGINE INNODB STATUS表明两个事务都持有锁product并等待更多锁，即使 (2) 实际上是单个 SQL 语句。

*** (1) TRANSACTION:
TRANSACTION 39503904, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 52 lock struct(s), heap size 8400, 45 row lock(s), undo log entries 22
MySQL thread id 3651，操作系统线程句柄 123145451790336，查询 id 1271704 localhost 根更新
INSERT INTO order_item...
*** (1) HOLDS THE LOCK(S)：
记录锁空间 id 317598 页号 30 n 位 80 表producttrx 的索引 id 39503904 锁模式 S 锁定记录但不锁定间隙
*** (1) 等待此锁定被授予：
记录锁定空间 id 317598 页号 29 n 位 72 表producttrx 的索引主 id 39503904 锁定模式 S 锁定记录但不等待间隙\

*** (2) TRANSACTION:
TRANSACTION 39503901, ACTIVE 0 sec fetching rows
mysql tables in use 4, locked 4
LOCK WAIT 495 lock struct(s), heap size 73936, 21867 row lock(s), undo log entries 184
MySQL thread id 3647，OS 线程句柄 123145450577920，查询 id 1271692 localhost root
UPDATE product...
*** (2) HOLDS THE LOCK(S)：
记录锁空间 id 317598 页号 29 n 位 72 表producttrx 的索引 id 39503901 lock_mode X
*** (2) 等待此锁定被授予：
记录锁定空间 id 317598 页号 30 n 位 80 表producttrx 的索引主 id 39503901 lock_mode X waiting\

据我了解，死锁转储表示作为单个 UPDATE 语句的事务 (2) 也尝试按顺序获取锁，即使它是单个语句。这就是内部请求多行锁的方式吗？是因为子查询（连接）吗？我在文档中找不到这个细节。我们使用的是 MySQL 8.0.19。

标签： mysqllockingdeadlock