关于数据库: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

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

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

二、剖析思路

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

2.1 Innodb 中的锁

2.1.1 行级锁

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

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

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

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 为一般索引。

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

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

1、惟一索引等值查问

select * from user
where id = 5 for update

select * from user
where 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 user
where id = 6 for update

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

2、惟一索引范畴查问

select * from user
where 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 user
where 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 user
where mobile_num >= 6 and mobile_num < 8
for 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）。

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

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

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

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

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

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

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

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

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

详细分析：

•工夫点 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 查问的数据存在时，会是什么样的过程呢？过程如下表：

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

三、解决办法

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

若有谬误，还望批评指正

作者：京东批发  刘哲

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