一例MySQL的insert死锁

原文链接: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锁,建表语句如下:

1
2
3
4
5
6
7
8
9
10
11
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这条记录。为了实现悲观锁,我们把整个流程放入了事务里,这样可以保证其他会话锁等待而不是报唯一键冲突的错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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; 查看最近的死锁日志,为了便于理解加了一些注释:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
------------------------
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):

trx1trx2trx3状态
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],具体就不深究了。

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

trx1trx2trx3
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

posted @ 2024-02-19 23:12  翎野君  阅读(117)  评论(0编辑  收藏  举报