Insert语句导致Mysql数据库死锁问题

type

status

date

slug

summary

1、问题背景

生产环境的服务偶尔会出现死锁问题，频率不是很高，大概几小时才出现一次。

查看 Mysql 数据库的运行情况，只关注死锁部分。

简单分析这段死锁日志的含义是：两个 insert 事务各自都持有唯一索引记录的 S Next-key 锁，并且都想要进一步获取 X insert intention lock 的时候，因为 gap lock 和 insert intention lock 互相冲突，造成死锁了。

2、分析

先说一下项目背景：我们在项目里面使用数据库作为分布式锁，t_payment_lock 这个表就是作为分布式锁表使用的。我们使用容器部署服务，一个服务会部署多个 pod。当定时任务执行的时候，多个 pod 之间竞争分布式锁，只有获取到分布式锁的 pod 才会执行定时任务。

我们使用 t_payment_lock 作为分布式锁的使用方式是这样的：每一次锁的过程，就是先 insert 一条记录，再执行业务逻辑，最后 delete 这条记录。这种基于数据库方式实现的分布式锁还是比较常见的。

查看表 t_payment_lock 的 DDL，发现表有两个索引，主键索引和二级索引（也是唯一索引）uk_lockkey。

查看死锁发生时候的 sql 执行错误日志，发现发生死锁的时候，在多条 insert 语句中间混入一条 delete 语句。大概如下所示，后面的两条 SQL 执行报死锁错误。

这里就有点蹊跷了。先说明间隙锁的几个特性：

间隙锁可以共存。一个事务获取的间隙锁并不会阻止另一个事务在同一个间隙上获取间隙锁。

间隙锁虽然分为间隙共享锁（Gap S-lock）和间隙排它锁（Gap X-lock），但是使用起来并没有差别，共享间隙锁和排他间隙锁彼此之间不冲突。间隙锁支持在不同事务可以在同一间隙添加间隙共享锁和间隙排他锁。

正常来说 insert 语句会加入插入意向锁，这种锁本身并不会阻塞其他事务的 insert 语句，也就是说插入意向锁是互相兼容，这也是 insert 语句本身的优化，能够保证大量执行 insert 语句的性能。

delete 语句会加入间隙锁，间隙锁和上面的插入意向锁是互斥的，也就是说间隙锁会阻塞其他事务的 insert 语句，这里就有可能造成死锁。

查看 mysql 官方文档关于加锁的经典部分 https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html，这里提到两个点：

第一个是 insert 加锁流程：

先在插入的索引间隙里，加 insert intention gap lock

如果存在重复 key 冲突，给冲突的索引加锁 S record lock。

如果不存在重复 key 冲突，给插入的记录加 X record lock。

第二个也是最重要的一个点：如果两个事务插入唯一索引冲突，这两个事务就会对重复的记录加共享行锁。如果其他事务本身已经持有该重复记录的互斥行锁，当该事务释放互斥行锁的时候，持有共享行锁的两个事务就会发生死锁。

官方还提供了例子如下所示，这个例子和我们死锁的日志基本一致，基本确认找到问题。

3、问题复现

1、事务一执行。先执行 insert 语句并 commit，然后执行 delete 语句但不 commit。

查看数据库的锁情况，此时只有一个主键索引的 Record 锁（ X,REC_NOT_GAP表示已经由 Next-Key 退化为 Record 锁）。

2、事务二执行 Insert 语句，会被阻塞。

查看数据库的锁情况，发现多了一个二级索引的 Record 锁（序号2）和一个二级索引的共享锁（序号3），且该共享锁的 LOCK_STATUS 是 WAITING 状态，说明事务二被阻塞在获取共享锁上。

3、事务三执行 Insert 语句，会被阻塞。

查看锁，发现多了一个二级索引共享锁（序号4），且该共享锁的 LOCK_STATUS 也是 WAITING 状态。事务二和事务三都在等待获取共享行锁，和官方说明的情况一致。

4、事务四执行 commit 语句。

事务二或者事务三其中一个会发生死锁，然后回滚事务。

再查看数据库的锁情况。表示没有回滚的那个事务已经获取到了共享锁。

最后再来梳理一下这个死锁场景：

trx1	trx2	trx3
begin;	begin;	begin;
DELETE FROM t_payment_lock …;	ㅤ	ㅤ
二级索引持有X record lock	ㅤ	ㅤ
ㅤ	INSERT INTO t_payment_lock …;	ㅤ
ㅤ	发现唯一键冲突，尝试获取S next-key lock	ㅤ
ㅤ	ㅤ	INSERT INTO t_payment_lock …;
ㅤ	ㅤ	发现唯一键冲突，尝试获取S next-key lock
commit；	ㅤ	ㅤ
事务提交，释放所有锁	ㅤ	ㅤ
ㅤ	获取到S next-key lock	ㅤ
ㅤ	ㅤ	获取到S next-key lock，因为S锁是共享锁，两个trx都可以获取
ㅤ	尝试获取X insert intention lock，与trx3的next-key lock冲突	ㅤ
ㅤ	ㅤ	尝试获取X insert intention lock，与trx2的next-key lock冲突

4、解决方案

关于这个现象，早在 2009 年就有 report：MySQL Bugs: #43210: Deadlock detected on concurrent insert into same table (InnoDB)，但仅仅解释了一下原因，然后修改了文档说明，从此以后一直到 MySQL 8.0，这个死锁案例始终出现在官方手册里，看起来官方并不认为这是 bug 而是 feature。所以最好是不要用 MySQL 实现通用的分布式锁。

如果已经实现了，根据 MySQL 官方对死锁的处理建议 https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks-handling.html

死锁时常发生，只要不是太频繁影响到系统，可以先不管。我们的 TDSQL 在测试和生产库都是开启了死锁检测的。

也就是说发生死锁的时候会立刻处理（回滚其中一个事务）。可以观察死锁日志，只要不是非常频繁影响到数据库性能，可以先不管。

如果一定要处理，可以在 insert 语句之前加一个 select … for update 语句对唯一索引加行排他锁。由于排他锁之间是互斥的，可以避免出现多个事务同时获取到锁这种情况，也就不会出现后面的死锁问题。

5、参考

https://fanyilun.me/2022/03/09/一例MySQL的insert死锁/