开发者社区 > 博文 > MySQL事务死锁问题排查
分享
  • 打开微信扫码分享

  • 点击前往QQ分享

  • 点击前往微博分享

  • 点击复制链接

MySQL事务死锁问题排查

  • 魔幻****
  • 2023-09-20
  • IP归属:北京
  • 6520浏览

    一、背景

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


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

    将获取的锁(下)/已获取的锁(右)
    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


    若有错误,还望批评指正

    文章数
    1
    阅读量
    163

    作者其他文章