如何避免Mysql RR 隔离级别下的 INSERT 死锁

背景

        我们公司架构师，在使用 Mysql 做分布式锁的时候，因 insert 唯一键冲突，造成死锁。引起我对这部分知识点的兴趣和研究。

        死锁日志的详细信息如下：

LATEST DETECTED DEADLOCK
------------------------
2024-08-19 16:32:45 0x7f92b0ca2700
*** (1) TRANSACTION:
TRANSACTION 329612456, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3365552, OS thread handle 140263881365248, query id 1432399623 192.168.18.207 root update
-- step6：插入 ConsumeLogAlarmLock
insert into t_distributed_lock (lock_key) values ('ConsumeLogAlarmLock')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10113 page no 4 n bits 72 index uk_lock_key of table `shouba_gy`.`t_distributed_lock` trx id 329612456 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 329612441, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3365470, OS thread handle 140268007991040, query id 1432399406 192.168.18.207 root update
-- step4：插入 ConsumeLogAlarmLock
insert into t_distributed_lock (lock_key) values ('ConsumeLogAlarmLock')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10113 page no 4 n bits 72 index uk_lock_key of table `shouba_gy`.`t_distributed_lock` trx id 329612441 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 10113 page no 4 n bits 72 index uk_lock_key of table `shouba_gy`.`t_distributed_lock` trx id 329612441 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)

前置知识

• insert 会对插入的这条记录加排他记录锁，在 insert 记录锁之前还会加一种 GAP 锁，叫做插入意向锁，如果出现唯一键冲突，还会加一个共享记录锁(S Next-Key Lock)。具体可以去
  MySQL 官方文档 查看
• Mysql 5.7 和Mysql 8.0 涉及事务和锁的3 个基表

       Mysql 5.7

                information_schema.innodb_locks

                information_schema.innodb_lock_waits  -- 查看当前等待的锁

                information_schema.innodb_trx -- 查询当前的事务信息

        Mysql 8.0

                performance_schema.data_locks

                performance_schema.data_lock_waits  -- 查看当前等待的锁

                information_schema.INNODB_TRX -- 查询当前的事务信息

        MySQL 5.7 中，information_schema.innodb_locks 包含这些数据：

                InnoDB 事务已申请但未获得的锁。

                InnoDB 事务已持有并且阻塞了其它事务的锁。

        MySQL 8.0 中，performance_schema.data_locks 包含这些数据：

                InnoDB 事务已申请但未获得的锁。

                InnoDB 事务正在持有的锁。

• mysql 锁冲突矩阵

锁的兼容矩阵

`	Gap	Insert Intention	Record	Next-Key
Gap	兼容	兼容	兼容	兼容
Insert Intention	冲突	兼容	兼容	冲突
Record	兼容	兼容	冲突	冲突
Next-Key	兼容	兼容	冲突	冲突

注：横向是已经持有的锁，纵向是正在请求的锁

type	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

              

前置准备

Mysql 版本: 5.7.17

隔离级别为：REPEATABLE-READ

表结构：

-- 建表
CREATE TABLE `t_distributed_lock` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lock_key` varchar(255) NOT NULL COMMENT '锁名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_lock_key` (`lock_key`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

-- 插入数据
insert into t_distributed_lock2 (lock_key) values ('ConsumeLogAlarmLock');

Sql 语句执行流程：

时刻	Session 1	Session 2	Session 3
T1	begin; delete from t_distributed_lock where lock_key = 'ConsumeLogAlarmLock';
T2		begin; insert into t_distributed_lock (lock_key) values ('ConsumeLogAlarmLock');
T3			begin; insert into t_distributed_lock (lock_key) values ('ConsumeLogAlarmLock');
T4	COMMIT;
			DEADLOCK,ROLLBACK;

 原因分析

T1 时刻    

session1 插入记录成功，此时对应的索引记录被隐式锁保护，未生成锁结构。

T2 时刻

session2 插入记录检测到插入值和 session1 唯一键冲突。

• session2 帮助 session1 对 lock_key = ConsumeLogAlarmLock 的记录产生了一个显式的锁结构。

• session2 自身产生 S 型的 NEXT-KEY LOCK，请求范围为 (-∞,ConsumeLogAlarmLock ]，但是其只能获取到 (-∞,ConsumeLogAlarmLock ) 的 GAP LOCK，而被 session1 的 lock_key = ConsumeLogAlarmLock 的记录锁阻塞。

T3 时刻

session3 插入记录检测到插入值和 session1 唯一键冲突。

• session3 自身产生 S 型的 NEXT-KEY LOCK，请求范围为 (-∞,ConsumeLogAlarmLock ]，但是其只能获取到 (-∞,ConsumeLogAlarmLock ) 的 GAP LOCK，而被 session1 的 lock_key = ConsumeLogAlarmLock 的记录锁阻塞。

T4 时刻

• session1 执行 COMMIT，释放X锁。session2 和 session3 都获得 S Next-Key Lock；
• session2 和 session3 继续执行插入操作，这个时候 INSERT INTENTION LOCK（插入意向锁）出现了，并且由于插入意向锁会被 gap 锁阻塞，所以 session2 和 session3 互相等待，造成死锁。

至此，形成闭环锁等待，死锁条件达成：session2 和 session3 分别想要在插入的间隙 (-∞,ConsumeLogAlarmLock) 获得插入意向锁，但分别被对方持有的 GAP 锁阻塞。

参考文章

技术分享 | 如何避免 RC 隔离级别下的 INSERT 死锁 - 墨天轮

MySQL :: MySQL 8.0 Reference Manual :: 17.7.3 Locks Set by Different SQL Statements in InnoDB

解决死锁之路 - 常见 SQL 语句的加锁分析 - aneasystone's blog