mysql死锁案例之唯一索引

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 InnoDBhttps://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锁同时存在的可能性。