死锁是每个 MySQL DBA 都经常会遇到的问题，之前也写过关于死锁的详细解析。多数时候死锁容易在 update 中发生，且一般是涉及到二级索引。而本次遇到的问题是发生在 insert 上的死锁，与常规的场景不太一样，因此单独拿出来分析一下。
问题概述
死锁的详细信息如下：
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2022-06-07 10:51:03 0x7f49b48f7700
*** (1) TRANSACTION: TRANSACTION 254912, ACTIVE 47 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 9 row lock(s), undo log entries 4 MySQL thread id 13410025, OS thread handle 139954474706688, query id 68147670 10.0.0.6 root update insert into t(cnt) values('abc-105-sz'),('abc-125-sz')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1621 page no 4 n bits 80 index unq_cnt of table `test`.`t` trx id 254912 lock mode S waiting Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 10; hex 6162632d3133302d737a; asc abc-130-sz;; 1: len 4; hex 80000028; asc (;;
*** (2) TRANSACTION: TRANSACTION 254929, ACTIVE 43 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3 MySQL thread id 13410024, OS thread handle 139954538641152, query id 68149502 10.0.0.6 root update insert into t(cnt) values('abc-120-sz'),('abc-130-sz')
*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1621 page no 4 n bits 80 index unq_cnt of table `test`.`t` trx id 254929 lock_mode X locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 10; hex 6162632d3132302d737a; asc abc-120-sz;; 1: len 4; hex 8000001e; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 10; hex 6162632d3133302d737a; asc abc-130-sz;; 1: len 4; hex 80000028; asc (;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1621 page no 4 n bits 80 index unq_cnt of table `test`.`t` trx id 254929 lock mode S waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 10; hex 6162632d3132352d737a; asc abc-125-sz;; 1: len 4; hex 80000023; asc #;;
*** WE ROLL BACK TRANSACTION (2)从信息中可以看到，死锁发生时的语句为两个 Insert 语句。通过审计的方式，找到这个 insert 操作属于一个业务请求发起的事务，由一个 delete 语句和 insert 语句构成。
测试环境复现
表和数据可以参考如下语句进行生成：
CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cnt` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_cnt` (`cnt`) ) ENGINE=InnoDB;
insert into t values(10,'abc-100-sz'),(15,'abc-105-sz'),(20,'abc-110-sz'),(25,'abc-115-sz'),(30,'abc-120-sz'),(35,'abc-125-sz'),(40,'abc-130-sz'),(45,'abc-135-sz'),(50,'abc-140-sz');可以使用如下语句来复现：
Time |
Session 1 |
Session 2 |
T1 |
begin |
begin |
T2 |
delete from t where cnt in ('abc-105-sz','abc-125-sz'); |
|
T3 |
|
delete from t where cnt in ('abc-120-sz','abc-130-sz'); |
T4 |
insert into t(cnt) values('abc-105-sz'),('abc-125-sz'); |
|
T5 |
Lock wait |
insert into t(cnt) values('abc-120-sz'),('abc-130-sz'); |
T6 |
Lock wait |
DeadLock found |
T7 |
commit |
rollback |
问题原因
由于可以稳定复现，因此可以通过系统表来观察锁争用的情况。在 T4 阶段，insert 执行的时候就会进入锁等待的状态，因此选择在 T4 与 T5 之间查看系统表：
mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+--------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+--------------+
| 261051:1621:4:8 | 261051 | S | RECORD | `test`.`t` | unq_cnt | 1621 | 4 | 8 | 'abc-130-sz' |
| 261065:1621:4:8 | 261065 | X | RECORD | `test`.`t` | unq_cnt | 1621 | 4 | 8 | 'abc-130-sz' |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+--------------+可以发现 Session 1 的 insert 语句对唯一索引的abc-130-sz这一行数据加上了 S 锁，和 Session 2 中 delete 持有的 X 锁发生了冲突。但是 Session 1 操作的数据中，其实并没有涉及到这一行数据。
仔细观察唯一索引的数据，可以看到 Session 1 插入的数据abc-125-sz在逻辑分布上与abc-130-sz是相邻的，通过多次尝试，可以确认这个 insert 语句不仅在当前插入的数据上加了锁，还在相邻的下一行数据上要加上 S 锁。
因此回顾 Session 2 的 insert 操作，会看到 insert 的操作中，刚好也有一行数据与 Session 1 发生了冲突。
解决方案
因此这个 insert 中额外获取的锁导致了这个 delete+insert 的事务发生了死锁，只需要把发生死锁的唯一索引替换成普通索引就可以了，但是要注意这种替换操作对业务的影响。