关于数据库:MySQL事务死锁问题排查-京东云技术团队

一、背景

在预发环境中，由音讯驱动最终触发执行事务来写库存，然而导致MySQL产生死锁，写库存失败。

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: rpc error: code = Aborted desc = Deadlock found when trying to get lock; try restarting transaction (errno 1213) (sqlstate 40001) (CallerID: ): Sql: "/* uag::omni_stock_rw;xx.xx.xx.xx:xxxxx;xx.xx.xx.xx:xxxxx;xx.xx.xx.xx:xxxxx;enable */  insert into stock_info(tenant_id, sku_id, store_id, available_num, actual_good_num, order_num, created, modified, SAVE_VERSION, stock_id) values (:vtg1, :vtg2, :_store_id0, :vtg4, :vtg5, :vtg6, now(), now(), :vtg7, :__seq0) /* vtgate:: keyspace_id:e267ed155be60efe */", BindVars: {__seq0: "type:INT64 value:"29332459" "_store_id0: "type:INT64 value:"50650235" "vtg1: "type:INT64 value:"71" "vtg2: "type:INT64 value:"113817631" "vtg3: "type:INT64 value:"50650235" "vtg4: "type:FLOAT64 value:"1000.000" "vtg5: "type:FLOAT64 value:"1000.000" "vtg6: "type:INT64 value:"0" "vtg7: "type:INT64 value:"20937611645" "}

初步排查，在同一时刻有两条申请进行写库存的操作。

工夫前后相差1s，但最终执行后果是，这两个事务互相死锁，均失败。

事务定义非常简单，伪代码形容如下：

start transaction// 1、查问数据data = select for update(tenantId, storeId, skuId);if (data == null) {    // 插入数据    insert(tenantId, storeId, skuId);} else {    // 更新数据    update(tenantId, storeId, skuId);}end transaction

该数据库表的索引构造如下：

索引类型	索引组成列
PRIMARY KEY	(`stock_id`)
UNIQUE KEY	(`sku_id`,`store_id`)

所应用的数据库引擎为Innodb，隔离级别为RR[Repeatable Read]可反复读。

二、剖析思路

首先理解下Innodb引擎中有对于锁的内容

2.1 Innodb中的锁

2.1.1 行级锁

在Innodb引擎中，行级锁的实现形式有以下三种：

名称	形容
Record Lock	锁定单行记录，在隔离级别RC和RR下均反对。
Gap Lock	间隙锁，锁定索引记录间隙（不蕴含查问的记录），锁定区间为左开右开，仅在RR隔离级别下反对。
Next-Key Lock	临键锁，锁定查问记录所在行，同时锁定后面的区间，故区间为左开右闭，仅在RR隔离级别下反对。

同时，在Innodb中实现了规范的行锁，依照锁定类型又可分为两类：

名称	符号	形容
共享锁	S	容许事务读一行数据，阻止其余事务取得雷同的数据集的排他锁。
排他锁	X	容许事务删除或更新一行数据，阻止其余事务取得雷同数据集的共享锁和排他锁。

简言之，当某个事物获取了共享锁后，其余事物只能获取共享锁，若想获取排他锁，必须要期待共享锁开释；若某个事物获取了排他锁，则其余事物无论获取共享锁还是排他锁，都须要期待排他锁开释。如下表所示：

将获取的锁（下）\已获取的锁（右）	共享锁S	排他锁X
共享锁S	兼容	不兼容
排他锁X	不兼容	不兼容

2.1.2 RR隔离级别下加锁示例

如果当初有这样一张表user，上面将针对不同的查问申请逐个剖析加锁状况。user表定义如下：

CREATE TABLE `user` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',  `mobile_num` bigint(20) NOT NULL COMMENT '手机号',  PRIMARY KEY (`id`),  UNIQUE KEY `IDX_USER_ID` (`user_id`),  KEY `IDX_MOBILE_NUM` (`mobile_num`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表'

其中主键id与user_id为惟一索引，user_name为一般索引。

假如该表中现有数据如下所示：

id	user_id	mobile_num
1	1	3
5	5	6
8	8	7
9	9	9

上面将应用select ... for update 语句进行查问，别离针对惟一索引、一般索引来进行举例。

1、惟一索引等值查问

select * from userwhere id = 5 for update

select * from userwhere user_id = 5 for update

在这两条SQL中，Innodb执行查问过程时，会如何加锁呢？

咱们都晓得Innodb默认的索引数据结构为B+树，B+树的叶子结点蕴含指向下一个叶子结点的指针。在查问过程中，会依照B+树的搜寻形式来进行查找，其底层原理相似二分查找。故在加锁过程中会依照以下两条准则进行加锁：

1.只会对满足查问指标左近的区间加锁，并不是对搜寻门路中的所有区间都加锁。本例中对搜寻id=5或者user_id=5时，最终能够定位到满足该搜寻条件的区域(1,5]。

2.加锁时，会以Next key Lock为加锁单位。那依照1满足的区域进行加Next key Lock锁（左开右闭），同时因为id=5或者user_id=5存在，所以该Next key Lock会进化为Record Lock，故只对id=5或user_id=5这个索引行加锁。

如果查问的id不存在，例如：

select * from userwhere id = 6 for update

依照下面两条准则，首先依照满足查问指标条件左近区域加锁，所以最终会找到的区间为(5,8]。因为id=6这条记录并不存在，所以Next key Lock(5, 8]最终会进化为Gap Lock，即对索引(5,8)加间隙锁。

2、惟一索引范畴查问

select * from userwhere id >= 4 and id <8 for update

同理，在范畴查问中，会首先匹配左值id=4，此时会对区间(1,5]加Next key Lock，因为id=4不存在，所以锁进化为 Gap Lock(1,5)；接着会往后持续查找id=8的记录，直到找到第一个不满足的区间，即Next key Lock(8, 9]，因为8不在范畴内，所以锁进化为Gap Lock(8, 9)。故该范畴查问最终会锁的区域为(1, 9)

3、非惟一索引等值查问

对非惟一索引查问时，与上述的加锁形式稍有区别。除了要对蕴含查问值区间内加Next key Lock之外，还要对不满足查问条件的下一个区间加Gap Lock，也就是须要加两把锁。

select * from userwhere mobile_num = 6 for update

须要对索引(3, 6]加Next key Lock，因为此时是非惟一索引，那么也就有可能有多个6存在，所以此时不会进化为Record Lock；此外还要对不满足该查问条件的下一个区间加Gap Lock，也就是对索引(6,7)加锁。故总体来看，对索引加了(3,6]Next key Lock和(6, 7) Gap Lock。

若非惟一索引不命中时，如下：

select * from user where mobile_num = 8 for update

那么须要对索引(7, 9]加Next key Lock，又因为8不存在，所以锁进化为Gap Lock (7, 9)

4、非惟一索引范畴查问

select * from userwhere mobile_num >= 6 and mobile_num < 8for update

首先先匹配mobile_num=6，此时会对索引(3, 6]加Next Key Lock，尽管此时非惟一索引存在，然而不会进化为Record Lock；其次再看后半局部的查问mobile_num=8，须要对索引(7, 9]加Next key Lock，又因为8不存在，所以进化为Gap Lock (7, 9)。最终，须要对索引行加Next key Lock(3, 6] 和 Gap Lock(7, 9)。

2.1.3 意向锁（Intention Locks）

Innodb为了反对多粒度锁定，引入了意向锁。意向锁是一种表级锁，用于表明事务将要对某张表某行数据操作而进行的锁定。同样，意向锁也分为类：共享意向锁（IS）和排他意向锁（IX）。

名称	符号	形容
共享意向锁	IS	表明事务将要对表的个别行设置共享锁
排他意向锁	IX	表明事务将要对表的个别行设置排他锁

例如select ... lock in shared mode会设置共享意向锁IS；select ... for update会设置排他意向锁IX

设置意向锁时须要依照以下两条准则进行设置：

1.当事务须要申请行的共享锁S时，必须先对表申请共享动向IS锁或更强的锁

2.当事务须要申请行的排他锁X时，必须先对表申请排他动向IX锁

表级锁兼容性矩阵如下表：

将获取的锁（下）/已获取的锁（右）	X	IX	S	IS
X	抵触	抵触	抵触	抵触
IX	抵触	兼容	抵触	兼容
S	抵触	抵触	兼容	兼容
IS	抵触	兼容	兼容	兼容

如果申请锁的事务与现有锁兼容，则会将锁授予该事务，但如果与现有锁抵触，则不会授予该事务。事务期待，直到抵触的现有锁被开释。

意向锁的目标就是为了阐明事务正在对表的一行进行锁定，或将要对表的一行进行锁定。在意向锁概念中，除了对全表加锁会导致意向锁阻塞外，其余状况意向锁均不会阻塞任何申请！

2.1.4 插入意向锁

插入意向锁是一种非凡的意向锁，同时也是一种非凡的“Gap Lock”，是在Insert操作之前设置的Gap Lock。

如果此时有多个事务执行insert操作，恰好须要插入的地位都在同一个Gap Lock中，然而并不是在Gap Lock的同一个地位时，此时的插入意向锁彼此之间不会阻塞。

2.2 过程剖析

回到本文的问题上来，本文中有两个事务执行同样的动作，别离为先执行select ... for update获取排他锁，其次判断若为空，则执行insert动作，否则执行update动作。伪代码形容如下：

start transaction// 1、查问数据data = select for update(tenantId, storeId, skuId);if (data == null) {    // 插入数据    insert(tenantId, storeId, skuId);} else {    // 更新数据    update(tenantId, storeId, skuId);}end transaction

当初对这两个事务所执行的动作进行逐个剖析，如下表所示：

工夫点	事务A	事务B	潜在动作
1	开始事务	开始事务
2	执行select ... for update操作		事务A申请到IX 事务A申请到X，Gap Lock
3		执行select ... for update操作	事务B申请到IX，与事务A的IX不抵触。事务B申请到Gap Lock，Gap Lock可共存。
4	执行insert操作		事务A先申请插入意向锁IX，与事务B的Gap Lock抵触，期待事务B的Gap Lock开释。
5		执行insert操作	事务B先申请插入意向锁IX，与事务A的Gap Lock抵触，期待事务A的Gap Lock开释。
6			死锁检测器检测到死锁

详细分析：

•工夫点1，事务A与事务B开始执行事务

•工夫点2，事务A执行select ... for update操作，执行该操作时首先须要申请动向排他锁IX作用于表上，接着申请到了排他锁X作用于区间，因为查问的值不存在，故Next key Lock进化为Gap Lock。

•工夫点3，事务B执行select ... for update操作，首先申请动向排他锁IX，依据2.1.3节表级锁兼容矩阵能够看到，意向锁之间是互相兼容的，故申请IX胜利。因为查问值不存在，故能够申请X的Gap Lock，而Gap Lock之间是能够共存的，不论是共享还是排他。这一点能够参考Innodb对于Gap Lock的形容，要害形容本文粘贴至此：

Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

•工夫点4，事务A执行insert操作前，首先会申请插入意向锁，但此时事务B曾经领有了插入区间的排他锁，依据2.1.3节表级锁兼容矩阵可知，在已有X锁状况下，再次申请IX锁是抵触的，须要期待事务B对X Gap Lock开释。

•工夫点5，事务B执行insert操作前，也会首先申请插入意向锁，此时事务A也对插入区间领有X Gap Lock，因而须要期待事务A对X锁进行开释。

•工夫点6，事务A与事务B均在期待对方开释X锁，后被MySQL的死锁检测器检测到后，报Dead Lock谬误。

思考：如果select ... for update 查问的数据存在时，会是什么样的过程呢？过程如下表：

工夫点	事务A	事务B	潜在动作
1	开始事务	开始事务
2	执行select ... for update操作		事务A申请到IX 事务A申请到X行锁，因数据存在故锁进化为Record Lock。
3		执行select ... for update操作	事务B申请到IX，与事务A的IX不抵触。事务B想申请指标行的Record Lock，此时须要期待事务A开释该锁资源。
4	执行update操作		事务A先申请插入意向锁IX，此时事务B仅仅领有IX锁资源，兼容，不抵触。而后事务A领有X的Record Lock，故执行更新。
5	commit		事务A提交，开释IX与X锁资源。
6		执行select ... for update操作	事务B事务B此时获取到X Record Lock。
7		执行update操作	事务B领有X Record Lock执行更新
8		commit	事务B开释IX与X锁资源

也就是当查问数据存在时，不会呈现死锁问题。

三、解决办法

1、在事务开始之前，采纳CAS+分布式锁来管制并发写申请。分布式锁key能够设置为store_skuId_version

2、事务过程能够改写为：

start transaction// RR级别下，读视图data = select from table(tenantId, storeId, skuId)if (data == null) {    // 可能呈现写并发    insert} else {    data = select for update(tenantId, storeId, skuId)    update}end transaction

尽管解决了插入数据不存在时会呈现的死锁问题，然而可能存在并发写的问题，第一个事务取得锁会首先插入胜利，第二个事务期待第一个事务提交后，插入数据，因为数据存在了所以报错回滚。

3、调整事务隔离级别为RC，在RC下没有next key lock（留神，此处并不精确，RC会有少部分状况加Next key lock），故此时仅仅会有record lock，所以事务2进行select for update时须要期待事务1提交。

参考文献

[1] Innodb锁官网文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

[2] https://blog.csdn.net/qq_43684538/article/details/131450395

[3] https://www.jianshu.com/p/027afd6345d5

[4] https://www.cnblogs.com/micrari/p/8029710.html

若有谬误，还望批评指正

作者：京东批发刘哲
起源：京东云开发者社区转载请注明起源