MySQL死锁信息调试

Question

我有 2 个表 EXPERIMENT 和 ENTITIES。 ENTITIES 有一个 id 字段，它引用表 EXPERIMENT 的主 ID。

我同时插入了多个实验以及子实体，但出现了死锁。

show engine innodb status 显示调试信息。我无法找到为什么会发生僵局。我猜这是因为子实体正在验证实验表中的Foreign_Key，但这看起来不应该产生死锁。

我对 id 和 SERIALIZABLE 事务隔离使用自动增量。

以下是 innodb 状态的相关部分:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-14 12:34:45 0x7000060f3000
*** (1) TRANSACTION:
TRANSACTION 162916, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 183, OS thread handle 123145405919232, query id 2464 localhost 127.0.0.1 root update
INSERT INTO _ENTITIES (TYPE, FK_EXPERIMENT_ID, CONTENT, CREATED_USER_ID) VALUES ('VARIATION', 42, '{"variantName":"Variant 1","actions":[{"blockId":0,"type":"SendEmail","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"EMAIL","params":{"verified":true,"selectedTemplate":"Design-Paid-Portfolio-A"},"name":"Action Block 1","treatmentId":"","default":true},{"blockId":1,"type":"wait","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"wait","params":{"unit":"hour","data":10,"verified":true},"name":"Action Block 1","treatmentId":"","default":true}],"variantPercentage":80}', 'uk
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6728 page no 4 n bits 96 index ENTITIES_EXPERIMENT_ID of table `test_database`.`_entities` trx id 162916 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 162906, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 164, OS thread handle 123145403969536, query id 2549 localhost 127.0.0.1 root update
INSERT INTO _ENTITIES (TYPE, FK_EXPERIMENT_ID, CONTENT, CREATED_USER_ID) VALUES ('VARIATION', 33, '{"variantName":"Variant 1","actions":[{"blockId":0,"type":"SendEmail","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"EMAIL","params":{"verified":true,"selectedTemplate":"Design-Paid-Portfolio-A"},"name":"Action Block 1","treatmentId":"","default":true},{"blockId":1,"type":"wait","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"wait","params":{"unit":"hour","data":10,"verified":true},"name":"Action Block 1","treatmentId":"","default":true}],"variantPercentage":80}', 'uk
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6728 page no 4 n bits 96 index ENTITIES_EXPERIMENT_ID of table `test_database`.`_entities` trx id 162906 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6728 page no 4 n bits 96 index ENTITIES_EXPERIMENT_ID of table `test_database`.`_entities` trx id 162906 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------

我应该如何解释这一点以及为什么会发生死锁？

使用的代码是:

Integer generatedId = experimentDAO.add(experimentQO);
......

for..
    entitiesDAO.add(entitiesQO);
....
ExperimentQO experimentQO = experimentDAO.get(generatedId);

添加实体时发生异常。

Answer 1

我看到的是:

• Trx #1 正在等待 ENTITIES_EXPERIMENT_ID 索引上的 X 模式插入意向锁(一种间隙锁)。
• Trx #2 在 ENTITIES_EXPERIMENT_ID 索引上持有 S 锁，该索引阻塞了 Trx #1
• Trx #2 还在等待 ENTITIES_EXPERIMENT_ID 索引上的插入意向锁。

我们可能假设 Trx #1 也在同一索引上持有 S 锁。 S锁是共享的，因此多个事务可以同时获取同一行(或间隙)上的S锁。

如果两个事务都先获取了S锁，然后都尝试请求X锁，那么它们就会陷入双方都在等待对方的情况，无法打破死锁。

有可能两个 INSERT 语句第一步都获取了 S 锁。或者，您可能在 INSERT 之前执行了一些在同一事务中获取 S 锁的其他查询，因此两个事务仍持有各自的 S 锁。

您尚未显示表定义，因此可能存在一些外键约束，这会导致间接引用的行获取 S 锁。