MySQL案例：insert死锁与唯一索引
原创
背景
死锁是每个 MySQL DBA 都经常会遇到的问题，之前也写过关于死锁的详细解析。多数时候死锁容易在 update 中发生，且一般是涉及到二级索引。而本次遇到的问题是发生在 insert 上的死锁，与常规的场景不太一样，因此单独拿出来分析一下。
问题描述
细节信息已脱敏，死锁信息来自于搭建的测试环境，使用的是腾讯云数据库 MySQL，版本为 5.7.18-txsql-log 20211031。死锁的详细信息如下：
------------------------
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 的事务发生了死锁。而解决方案在技术上并不复杂，只需要把发生死锁的唯一索引替换成普通索引就可以了，但是要注意这种替换操作对业务的影响。
拓展一下
从一般的角度来考虑，这个额外的 S 锁似乎是不必要的，所以仔细搜索一下 MySQL bug 的信息，发现一个远古时代的 bug 单：Unexplainable InnoDB unique index locks on DELETE + INSERT with same values 中也描述了同样的问题，后来官方尝试进行了“修复”，不过之后又非常戏剧性的把这个“修复”给修复掉了：Duplicates in Unique Secondary Index Because of Fix of Bug#68021。
总结一下
问题的解决方案总是多种多样的，要综合实际的情况来做一些取舍。其实参考死锁的几个要素，业务侧也可以通过一些调整来避免这个问题，就留作拓展思考给各位读者吧。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
