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

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锁，两个业务产生循环等候，形成死锁。两个业务都是加的RecordLock记载锁，前面也说到了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/…