<返回更多

MySQL 事务死锁问题排查

2023-09-27  京东云开发者  
加入收藏

一、背景

 

在预发环境中,由消息驱动最终触发执行事务来写库存,但是导致 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" "}

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

MySQL 事务死锁问题排查

时间前后相差 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 为普通索引。

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

iduser_idmobile_num113556887999

下面将使用 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)。

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

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

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

1. 当事务需要申请共享锁 S 时,必须先对申请共享意向 IS 锁或更强的锁

2. 当事务需要申请排他锁 X 时,必须先对申请排他意向 IX 锁

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

将获取的锁(下)/ 已获取的锁(右)XIXSISX冲突冲突冲突冲突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 Lock3执行 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死锁检测器检测到死锁

详细分析:

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.

思考:假如 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,故执行更新。5commit事务 A 提交,释放 IX 与 X 锁资源。6执行 select ... for update 操作事务 B 事务 B 此时获取到 X Record Lock。7执行 update 操作事务 B 拥有 X Record Lock 执行更新8commit事务 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

若有错误,还望批评指正

 

作者:京东零售 刘哲
来源:京东云开发者社区 转载请注明来源
关键词:MySQL      点击(2)
声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多MySQL相关>>>