原文链接: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):
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