数据测试 MySQL 事务死锁问题排查 | 京东云技术团队

京东云开发者 · 2023年09月26日 · 最后由 shadow 回复于 2023年10月08日 · 5188 次阅读

一、背景

在预发环境中，由消息驱动最终触发执行事务来写库存，但是导致 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 中实现了标准的行锁，按照锁定类型又可分为两类：

| 名称 | 符号 | 描述 | | 共享锁 | 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 user
where id = 5 for update

select * from user
where user_id = 5 for update

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

我们都知道 Innodb 默认的索引数据结构为 B+ 树，B+ 树的叶子结点包含指向下一个叶子结点的指针。在查询过程中，会按照 B+ 树的搜索方式来进行查找，其底层原理类似二分查找。故在加锁过程中会按照以下两条原则进行加锁：

只会对满足查询目标附近的区间加锁，并不是对搜索路径中的所有区间都加锁。本例中对搜索 id=5 或者 user_id=5 时，最终可以定位到满足该搜索条件的区域 (1,5]。
加锁时，会以 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）。

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

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

设置意向锁时需要按照以下两条原则进行设置：

当事务需要申请行的共享锁 S时，必须先对表申请共享意向 IS 锁或更强的锁
当事务需要申请行的排他锁 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 查询的数据存在时，会是什么样的过程呢？过程如下表：

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

三、解决方法

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

若有错误，还望批评指正

作者：京东零售刘哲

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

1 个赞

共收到 1 条回复时间点赞

shadow · #1 · 2023年10月08日

仅楼主可见

需要登录后方可回复, 如果你还没有账号请点击这里注册。