mysql死锁案例之唯一索引

本文通过一个线上环境的真实案例,详细分析了由唯一索引引起的MySQL死锁现象。当商户并发领取优惠券时,由于并发的insert和update操作,导致两个事务在尝试获取S锁和X锁时形成循环等待,从而发生死锁。解决方案是将insert和update操作分开,确保不会同时出现S锁和X锁,以避免死锁情况。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、背景

1.1 业务背景

        本案例为线上环境真实案例,业务背景是商户领取优惠券,商户领取了优惠券后会往mysql表中插入一条领用记录,假设这张优惠券可以被商户领取多张,第一次领取时,记录是insert一条已领取量=1的记录,当再次领取这张优惠券时,则是update这条记录已领取量=2的操作。

1.2 简化后的表结构

字段含义
id主键ID
coupon_id优惠券ID
user_id商户ID
received_num已领数量

1.3 表索引

        除了主键索引外,还额外设置了user_id和coupon_id的联合唯一索引;

1.4 代码逻辑

        当时设置唯一索引的目的是为了防止同一个user_id并发领取同一个coupon_id时insert成多条记录。有了唯一索引后,并发领取时,先执行的insert会成功;后执行的insert会抛唯一键冲突,然后再update。

try {
    //插入
    insert into tableX (user_id,coupon_id,received_num) values (?,?,?);
} catch (DuplicateKeyException e) {
    //唯一键冲突后,更新
    update tableX set received_num = received_num + 1 where user_id = ? 
and coupon_id = ?;
}

1.5 正常领取流程

         商户1第一次领取优惠券1,表记录如下:

                id=1、coupon_id=1、user_id=1、received_num=1

         商户1再次领取优惠券1,表记录如下:

                id=1、coupon_id=1、user_id=1、received_num=2

2、死锁现象

2.1 服务日志

e:Error updating datebase.Couse:com.mysql.cj.jdbc.execeptions.MySQLTransactionRollbackException:
Deadlock found when trying to get lock;try restarting transaction
### SQL:update tableX set received_num = received_num + 1 
where userId = ? and coupon_id = ?;

2.2 mysql日志

查看mysql最近一次的死锁日志 : show engine innodb status

3、死锁分析

        根据mysql死锁日志看,两个事物中都是同一条update语句造成的,参数变量值是一样的,说明应该是同一个用户的并发操作造成的,第一个事物中,持有着S(共享)锁,想要加X(排他锁);另一个事物中亦是如此。

        我很纳闷这个S锁是如何产生的,我的代码主要就是insert、update两条语句,按理都是加X锁的,最终我在mysql官方文档中找到这么一句话:

 mysql文档地址:MySQL :: MySQL 8.0 Reference Manual :: 15.7.3 Locks Set by Different SQL Statements in InnoDBicon-default.png?t=L892https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html如果你对于S和X锁的含义不是很清晰的话,我贴了段官方的解释:

大白话就是,如果一个事物想对记录加共享S锁时,这个记录上不能存在X锁;如果想对记录加排他X锁时,这个记录上不能存在S或X锁。

4、案发现场还原 

        假设商户1连击3次领取优惠券1时,事物1最先执行,其次事物2,再事物3执行;则:

事物1:

        insert 成功,received_num=1;

事物2:

        insert 失败,唯一键冲突,(并发时刻)走update;

事物3:

        insert 失败,唯一键冲突,(并发时刻)走update;

事物2和事物3在并发时刻时,由于insert唯一键冲突后,对同一条索引记录都加了各自的S锁,执行到update时,都需要加X锁,于是,事物2想加X锁时,需要等待事物3的S锁释放;事物3想加X锁时,需要等待事物2的S锁释放;形成了循环等待锁释放,发生了死锁现象。

5、解决

        知道了问题的本质后,解决方案有很多。可以改造不用唯一键的方式、提前insert好记录,领取时只走update等。

        由于改造不用唯一键方式改动点、影响点比较大,而提前插入由于业务的复杂度需要定时任务等引入;我这边最后的解决方案是:代码层面的insert和update分离,抽成两个方法,第一个方法预插入,第二个方法内只update更新即可;也就是执行完第一个预插入方法后,记录一定存在,将insert和update不放在同一个事物内,避免S锁和X锁同时存在的可能性。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值