一例MySQL的insert死锁

标签：insert lock intention 死锁 key MySQL id

原文链接：https://fanyilun.me/2022/03/09/%E4%B8%80%E4%BE%8BMySQL%E7%9A%84insert%E6%AD%BB%E9%94%81/

分享一个最近遇到的一例MySQL死锁。关于MySQL的锁，几年前写过一篇原理类的文章，基础知识建议移步MySQL加锁分析。

背景

我们使用MySQL实现了一个通用的分布式DB锁，建表语句如下：

CREATE TABLE `tbl_lock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `biz_type` varchar(32) NOT NULL COMMENT '锁的业务类型',
  `biz_key` varchar(255) NOT NULL COMMENT '锁的唯一key',
  `host_name` varchar(64) DEFAULT NULL COMMENT '持有锁的主机',
  `expire_time` datetime DEFAULT NULL COMMENT '超时时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_biz_type_key` (`biz_key`,`biz_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='DB锁表'

每一次锁的过程，就是先insert一条记录，再执行业务逻辑，最后delete这条记录。为了实现悲观锁，我们把整个流程放入了事务里，这样可以保证其他会话锁等待而不是报唯一键冲突的错误。

begin;
## 加锁
INSERT INTO tbl_lock (
        id,
        gmt_create,
        gmt_modified,
        biz_type,
        biz_key,
        host_name,
        expire_time
        )
        VALUES (
        null, NOW(), NOW(),'FLOW_INSTANCE', 'LCG-16463618958170A24',
        'MacBook-Pro-10.local', '2022-03-04 15:06:43.576'
        );
## do something...
(...)
## 释放锁
DELETE
        FROM tbl_lock
        WHERE biz_key = 'LCG-16463618958170A24'
        AND biz_type = 'FLOW_INSTANCE';
commit;

单论分布式锁的实现，通常建议使用redis、zk、etcd之类的方案。之所以使用了这种db分布式锁的实现方式，主要是实现简单，不需要引入其他组件。而且网上也有一些文章，这种方式实现db分布式锁还是比较常见的。

死锁案例

这种DB分布式锁使用了一段时间后，发现在并发高的情况下会出现死锁。

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

死锁发生后，可以直接执行 show engine innodb status; 查看最近的死锁日志，为了便于理解加了一些注释：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-03-08 18:03:08 140288584578816
*** (1) TRANSACTION:
TRANSACTION 227612, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 271, OS thread handle 140289841530624, query id 143790 140.205.147.81 zizhuo update
INSERT INTO tbl_lock (
        id,
        gmt_create,
        gmt_modified,
        biz_type,
        biz_key,
        host_name,
        expire_time
        )
        VALUES (
        null, NOW(), NOW(),'FLOW_INSTANCE', 'LCG-16463618958170A24',
        'MacBook-Pro-10.local', '2022-03-04 15:06:43.576'
        )

*** (1) HOLDS THE LOCK(S):
// 锁加在名为uk_biz_type_key的索引，也就是我们建的二级唯一索引
// lock mode S代表的是S NEXT-KEY LOCK，MySQL日志里没有"but not gap"字样就代表LOCK_ORDINARY类型的NEXT-KEY LOCK，锁的是当前记录+记录之前一个Gap
RECORD LOCKS space id 6 page no 5 n bits 72 index uk_biz_type_key of table `zizhuo_test`.`tbl_lock` trx id 227612 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;;

// 行锁的位置，就是插入的唯一索引的位置(FLOW_INSTANCE,LCG-16463618958170A24)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 21; hex 4c43472d3136343633363138393538313730413234; asc LCG-16463618958170A24;;
 1: len 13; hex 464c4f575f494e5354414e4345; asc FLOW_INSTANCE;;
 2: len 8; hex 000000000000000d; asc         ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
// 等待获取X insert intention lock，插入意向锁，也是一种间隙锁，这个间隙和上面的gap重叠了
RECORD LOCKS space id 6 page no 5 n bits 72 index uk_biz_type_key of table `zizhuo_test`.`tbl_lock` trx id 227612 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的锁和事务1一模一样，就不解释了
*** (2) TRANSACTION:
TRANSACTION 227616, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 272, OS thread handle 140288893617920, query id 143812 140.205.147.81 zizhuo update
INSERT INTO tbl_lock (
        id,
        gmt_create,
        gmt_modified,
        biz_type,
        biz_key,
        host_name,
        expire_time
        )
        VALUES (
        null, NOW(), NOW(),'FLOW_INSTANCE', 'LCG-16463618958170A24',
        'MacBook-Pro-10.local', '2022-03-04 15:06:43.576'
        )

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 5 n bits 72 index uk_biz_type_key of table `zizhuo_test`.`tbl_lock` trx id 227616 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 21; hex 4c43472d3136343633363138393538313730413234; asc LCG-16463618958170A24;;
 1: len 13; hex 464c4f575f494e5354414e4345; asc FLOW_INSTANCE;;
 2: len 8; hex 000000000000000d; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 5 n bits 72 index uk_biz_type_key of table `zizhuo_test`.`tbl_lock` trx id 227616 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事务各自都持有唯一索引记录的S Next-key锁，并且都想要进一步获取X insert intention lock的时候，因为gap lock和insert intention lock互相冲突，造成死锁。
结合使用场景，我们尝试了几次，复现了死锁发生的过程（其中insert和delete语句和上面的例子完全一致，每个事务都在插入相同的unique key）：

trx1	trx2	trx3	状态
begin;	begin;	begin;
INSERT INTO tbl_lock …;			trx1执行成功
	INSERT INTO tbl_lock …;		trx2等待trx1的锁
		INSERT INTO tbl_lock …;	trx3等待trx1的锁
DELETE FROM tbl_lock …;			trx1执行成功
commit;			trx1提交成功；trx2和trx3形成死锁
✓	×	×

查了一下，这是一种经典的MySQL死锁场景，MySQL 8.0 Reference Manual :: 15.7.3 Locks Set by Different SQL Statements in InnoDB[1]：三个事务同时insert，第一个事务回滚会造成后两个事务死锁。
根据官方文档的叙述，insert加锁流程如下：

先在插入的索引间隙里，加insert intention gap lock
如果存在重复key冲突，给冲突的索引加S锁。
如果不存在重复key冲突，给插入的记录加X record lock

额外解释一下，insert intention gap lock（插入意向锁）的作用。首先，插入意向锁可以理解成一种特殊的gap lock，不要和intention lock搞混。为了防止幻读，MySQL使用了Gap Lock（Next-Key Lock也包含Gap Lock，以下均使用Gap Lock表述）来给索引的间隙加锁，例如select * from my_table where id>7 for update;就会给(7,+∞)的聚簇索引加Gap Lock。Gap Lock的作用就是阻止其他事务向锁住的gap里插入数据，因此Gap Lock只和insert intention gap lock相冲突，这样在Gap Lock存在期间，insert语句就会通过加insert intention gap lock这种方式，锁等待来避免幻读。同样是加在间隙的锁，为什么把Gap Lock和insert intention gap lock区分开？其实insert直接加Gap Lock也可以实现避免幻读，但是锁冲突就变大了，insert intention gap lock的区分设计就是为了提高并发插入的性能，因为insert intention gap lock之间相互不冲突，如innodb-insert-intention-locks文档所述。

之前也提过，MySQL在RR隔离级别会通过Gap Lock避免幻读，RC隔离级别理论上不需要Gap Lock，但是其他场景如唯一索引校验也会用到Gap Lock，所以在RC级别依然有insert intention gap lock，也就依然会出现本文中的死锁场景。就比如上面提到的insert加锁流程第二步，给冲突索引加的S锁，实际上，如果是聚簇索引RC隔离级别，这个S锁就是普通的record lock行锁；聚簇索引RR隔离级别，加next-key lock；但是如果是二级唯一索引，无论是RC还是RR隔离级别，都是加next-key lock[2]。

所以我也试了一下，如果冲突的不是二级索引，而是利用聚簇索引来做DB锁的key会怎么样。其实MySQL官网举的例子就是用的聚簇索引，一样会出现死锁，只不过锁冲突就不是在s next-key lock和insert intention gap lock间隙锁之间了，而是在S locks rec but not gap和X locks rec but not gap行锁之间了。

另外，这个insert加锁流程也是为了便于理解简化过的，实际innodb实现过程要更复杂，在不存在锁冲突的情况下，insert本身不会加锁（或者叫隐式锁）[3]，具体就不深究了。

最后再梳理一下这个死锁的过程：

trx1	trx2	trx3
begin;	begin;	begin;
INSERT INTO tbl_lock …;
二级索引持有X record lock（通过日志查看此时实际并没有加insert intention lock）
	INSERT INTO tbl_lock …;
	发现唯一键冲突，尝试获取S next-key lock
		INSERT INTO tbl_lock …;
		发现唯一键冲突，尝试获取S next-key lock
DELETE FROM tbl_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冲突

关于这个现象，早在2009年就有report：MySQL Bugs: #43210: Deadlock detected on concurrent insert into same table (InnoDB)，但仅仅解释了一下原因，然后修改了文档说明，从此以后一直到MySQL8.0，这个死锁案例始终出现在官方手册里，看起来官方并不认为这是bug而是feature。对于我们开发者来说就比较棘手，只能避免此类写法。例如本文中的分布式锁，即使不放在事务里，悲观锁改成乐观锁，delete语句与两个insert语句同时执行，依然会出现死锁。看起来MySQL只适合根据不同的业务逻辑，采用select … for update的方式针对性加锁。当然，从性能和其他角度考虑，最好不要用MySQL实现通用的分布式锁。

参考资料

[1] https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
[2] https://www.bookstack.cn/read/aliyun-rds-core/4adfb6141be60032.md
[3] https://www.aneasystone.com/archives/2018/06/insert-locks-via-mysql-source-code.html

标签：insert,lock,intention,死锁,key,MySQL,id
From： https://www.cnblogs.com/lingyejun/p/18022167

背景

死锁案例

参考资料

相关文章

赞助商

阅读排行