MySQL案例：insert死锁与唯一索引

2022-06-07 16:21:05 浏览数 (2)

背景

死锁是每个 MySQL DBA 都经常会遇到的问题，之前也写过关于死锁的详细解析。多数时候死锁容易在 update 中发生，且一般是涉及到二级索引。而本次遇到的问题是发生在 insert 上的死锁，与常规的场景不太一样，因此单独拿出来分析一下。

问题描述

细节信息已脱敏，死锁信息来自于搭建的测试环境，使用的是腾讯云数据库 MySQL，版本为 5.7.18-txsql-log 20211031。死锁的详细信息如下：

代码语言：txt复制
------------------------
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 语句构成。

测试环境复现

表和数据可以参考如下语句进行生成：

代码语言：txt复制
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 之间查看系统表：

代码语言：txt复制
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。

总结一下

问题的解决方案总是多种多样的，要综合实际的情况来做一些取舍。其实参考死锁的几个要素，业务侧也可以通过一些调整来避免这个问题，就留作拓展思考给各位读者吧。

数据库 云数据库SQLServer 云数据库MySQLmysql 最佳实践 案例分析 死锁

0 人点赞