一次生产环境数据库死锁问题排查与解决

1 背景

最近线上清算服务RocketMQ消费频繁报SQL死锁异常，虽然最终可以基于事务回滚和消息消费重试保证最终正确性，但频繁回滚和重试是会降低消费端吞吐量的。个人通过分析线上MySQL死锁日志、阅读相关逻辑代码，找到了真正的问题所在，并给出了解决办法。特在此整理出来，互相学习提升，如果文中有错误的地方欢迎指正。

2 环境

生产环境使用的数据库为Mysql 8.0.13版本，InnoDB引擎，事务隔离级别READ-COMMITED

发生死锁的表结构及索引情况（只保留了表关键字段）

create table clearing_settle_org_cost_order
(
    id                    int auto_increment
        primary key,
    org_cost_uid          varchar(64)                                 not null comment '成本唯一id',
    org_id                int                                         not null comment '机构id',
    product_id            int                                         not null comment '产品id',
    back_article          decimal(12, 3) default 0.000                not null comment '回款金额',
    repay_date            varchar(32)                                 not null comment '还款日期:年-月',
    create_time           datetime(3)    default CURRENT_TIMESTAMP(3) not null comment '创建时间',
    update_time           datetime(3)    default CURRENT_TIMESTAMP(3) not null on update CURRENT_TIMESTAMP(3) comment '更新时间',
    is_delete             int            default 0                    null comment '删除状态 1删除  0未删除',
    constraint org_cost_uid
        unique (org_cost_uid)
)
    comment '清算-机构结算成本单';
​
create index org_id_index
    on clearing_settle_org_cost_order (org_id);
​
create index product_id_index
    on clearing_settle_org_cost_order (product_id);
​

3 知识储备

什么是数据库死锁：通常是因为两个及以上事务发生了死循环锁依赖，此时不得不回滚来释放锁。

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在实际业务中，大部分死锁都是行级锁导致的，InnoDB引擎行级锁有三大类：

• Record Lock：记录锁，也就是仅仅把一条记录锁上；
• Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

这里我们线上数据库使用的隔离级别为RC，只有Record Lock记录锁。

行级锁锁的是什么？

锁的是索引，索引分为聚簇索引（主键索引）、二级索引（辅助索引），如果查询直接走聚簇索引，则锁聚簇索引，如果走二级索引的话，先锁二级索引，再锁聚簇索引（重点）。

关于行级锁在RR、RC隔离级别的详细加锁规则大家可以自行了解,这里就不过多赘述。

4 排查过程

当数据库发生死锁时，执行下面的命令可查看数据库最近的一次死锁日志数据

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-08-09 16:03:24 0x7f6dc21cf700
*** (1) TRANSACTION:
TRANSACTION 4679832935, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 15 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 302348430, OS thread handle 140109382948608, query id 4556452767 172.16.18.77 assets Sending data
SELECT  id,org_cost_uid,settle_order_num,org_id,org_name,product_id,product_name,back_article,service_fee_rate,bonus,fine,service_fee,settle_order_url,generate_settle_order,repay_date,voucher_state,operation_user_id,operation_user_name,is_delete,create_time,update_time  FROM clearing_settle_org_cost_order 
​
 WHERE (org_id = 1561 AND product_id = 45 AND repay_date = '2023-08' AND is_delete = 0) for update
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 7680 page no 374 n bits 152 index PRIMARY of table `assets`.`clearing_settle_org_cost_order` trx id 4679832935 lock_mode X locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
​
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7680 page no 227 n bits 1112 index product_id_index of table `assets`.`clearing_settle_org_cost_order` trx id 4679832935 lock_mode X locks rec but not gap waiting
Record lock, heap no 117 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000002d; asc    -;;
 1: len 4; hex 80001c70; asc    p;;
​
*** (2) TRANSACTION:
TRANSACTION 4679832933, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
178 lock struct(s), heap size 24784, 33 row lock(s)
MySQL thread id 570785034, OS thread handle 140109384840960, query id 4556452765 172.16.18.77 assets Sending data
SELECT  id,org_cost_uid,settle_order_num,org_id,org_name,product_id,product_name,back_article,service_fee_rate,bonus,fine,service_fee,settle_order_url,generate_settle_order,repay_date,voucher_state,operation_user_id,operation_user_name,is_delete,create_time,update_time  FROM clearing_settle_org_cost_order 
​
 WHERE (org_id = 598 AND product_id = 45 AND repay_date = '2023-08' AND is_delete = 0) for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7680 page no 227 n bits 1112 index product_id_index of table `assets`.`clearing_settle_org_cost_order` trx id 4679832933 lock_mode X locks rec but not gap
Record lock, heap no 117 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
​
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7680 page no 374 n bits 152 index PRIMARY of table `assets`.`clearing_settle_org_cost_order` trx id 4679832933 lock_mode X locks rec but not gap waiting
Record lock, heap no 25 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
​
*** WE ROLL BACK TRANSACTION (1)

从上面的死锁日志中我们可以知道，造成死锁的两条sql分别为:

事务一
SELECT  id,org_cost_uid,settle_order_num,org_id,org_name,product_id,product_name,back_article,service_fee_rate,bonus,fine,service_fee,settle_order_url,generate_settle_order,repay_date,voucher_state,operation_user_id,operation_user_name,is_delete,create_time,update_time  FROM clearing_settle_org_cost_order 
 WHERE (org_id = 1561 AND product_id = 45 AND repay_date = '2023-08' AND is_delete = 0) for update
事务二 
 SELECT  id,org_cost_uid,settle_order_num,org_id,org_name,product_id,product_name,back_article,service_fee_rate,bonus,fine,service_fee,settle_order_url,generate_settle_order,repay_date,voucher_state,operation_user_id,operation_user_name,is_delete,create_time,update_time  FROM clearing_settle_org_cost_order 
 WHERE (org_id = 598 AND product_id = 45 AND repay_date = '2023-08' AND is_delete = 0) for update

事务1持有PRIMARY锁，等待获取product_id_index锁，事务2持有product_id_index锁，等待获取PRIMARY锁，两个事务发生循环等待，造成死锁。两个事务都是加的Record Lock记录锁，前面也说到了RC隔离级别只有记录锁。

通过sql定位到对应代码，代码的逻辑是在进行mq消费，一个事务里执行了多次for update当前读，这里涉及到公司机密，就不贴源代码了。

这里我们先分析一下，这两条sql只有查询条件org_id不同，表中有org_id和product_id两条二级索引。

如果MySql优化器选择先走org_id索引：

1.定位到若干条数据后会给二级索引org_id=xx加记录锁，再给这些记录的主键索引加上记录锁；
2.再走product_id索引，会给二级索引product_id=45加上记录锁，前面加的主键记录锁不符合product_id=45的会释放掉。

这里步骤1是不存在资源竞争的，因为org_id并不一样，步骤2中，如果A事务先执行，那么A会拿到product_id=45的记录锁，B事务执行sql的时候会等待，然后A再执行这条sql的时候，并不会发生死锁。

如果MySql优化器选择先走product_id索引：

1.定位到若干条数据后会给二级索引product_id=45加记录锁，再给这些记录的主键索引加上记录锁；
2.再走org_id索引，会给二级索引org_id=xx加上记录锁，前面加的主键记录锁不符合org_id=xx的会释放掉。

这里步骤1会发生资源竞争，如果A事务先执行，拿到product_id=45的记录锁后，B事务执行sql的时候会等待，然后A再次执行这条sql的时候，同样也不会发生死锁。

通过上述分析我们知道，这两种情况都不会发生死锁。难道他们走的索引顺序不一样吗？我们查看一下这两条sql的执行计划。

事务一：

事务二：

执行计划并不一样，我们根据它们各自的执行计划来分析一下加锁过程。

1. 事务二先走product_id索引，定位到若干条数据后会给二级索引product_id=45加上记录锁，再给这些记录的主键索引加上记录锁，再走org_id索引，会给二级索引org_id=598加上记录锁，前面锁定的主键索引不符合org_id=598的会释放掉。最终加锁情况如下图：

   ​

2. 然后事务一开始执行，先走org_id索引，定位到若干条数据后会给二级索引org_id=1561加上记录锁，再给这些记录的主键索引加上记录锁，接着走product_id索引，发现product_id=45已被事务二加上了记录锁，事务一进入等待状态，最终加锁情况如下图：

   

   注意这三条主键上的记录锁PRIMARY（12573、12634、12701）

   我们看一下这三条记录对应的product_id字段值为多少：

   

3. 接着事务一再次执行上次的sql，和步骤一的逻辑一样，先走product_id索引，定位到若干条数据后会给二级索引product_id=45加上记录锁，再给这些记录的主键索引加上记录锁，这个时候死锁就出现了，事务一可以对product_id=45加记录锁这没问题，因为步骤一已经拿到了这个锁，当给这些记录的主键索引加记录锁的时候，有一条数据PRIMARY=12573被事务二加了记录锁（“事物一进入等待”）。

   事务一持有二级索引product_id=45的记录锁，等待获取主键索引PRIMARY=12573的记录锁；

   事务二持有主键索引PRIMARY=12573的记录锁，等待获取二级索引product_id=45的记录锁（死锁）。

5 解决办法

通过上面的排查分析，这次死锁发生的原因已经很清晰了，那么该如何解决呢？

因为清算服务这部分Mq消费的代码逻辑不是我写的，而且代码逻辑比较复杂，直接改代码不太现实，最终决定在这张表上添加联合索引(org_id,product_id,repay_date)。

如何有效的避免死锁的发生：

1. 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

2. 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

3. 修改数据库隔离级别为RC，MySql默认级别为RR，RC没有间隙锁Gap Lock和组合锁Next-Key Lock，能一定程度的避免死锁的发生。

4. 尽量少使用当前读for update，数据更新时尽量使用主键。

参考资料：

• 《MySQL 是怎样运行的？》

• 《MySQL45讲》

• 《高性能MySQL》

• mysql.taobao.org/monthly/202…

• xiaolincoding.com/mysql/lock/…