生产故障解决之Mysql数据库死锁

线上故障回放

前几天晚上12：00点打算睡觉了(这么养生估计不适合某宁电器)，突然收到了一条线上告警，登录了监控系统，提示错误如下

Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in

此后每分钟告警不断持续，当即拉群应急处理，开发这个功能的小姑娘都吓的花容失色了。

简单评估之后，马上关闭定时任务对应的消息订阅，止血。

问题定位

这个问题已经很明显了，Mysql数据库事务等待超时，进行了回滚。

Mysql事务处理死锁的方式为主动监测，发现死锁，就进行回滚。那么为什么会突出造成这么多事务等待呢？

复盘一下场景，省略很多业务逻辑，以下操作在一个事务中为前提

1. 有一个主表batch_operation，id为主键。首先根据id进行select...for update锁定

   select * from batch_operation where id = #Id# for update

2. 有一个子表batch_operation_detail，有2个字段type、bussiness_id，根据type、bussiness_id进行锁定

   select * from batch_operation_detail where type = #type# and bussiness_id = #bussinessId# for update

3. 处理业务逻辑

问题很快定位出来了，由于同一时刻有很多任务在执行，而这些任务都是同一批次的，也就是batch_operation_detail里面的N条记录的batch_id是一样，这样大家都在争抢同一条batch_operation记录的锁定。如果事务处理太久，等待的任务获取不到锁，最后不得不回滚防止锁表。但是理论上业务处理不会花多长时间，否则线下也会测试出这个问题，那问题具体出在哪里呢？

batch_operation_detail表用type+bussiness_id去锁定记录防止并发，但是这2个字段不是UK，导致一次操作锁定10W+条记录，耗时过久！

那如何解决这个呢？也很简单，评估发现type+bussiness_id的确是业务UK的，加上了这个索引就解决了。

而且为了防止并发过大，对定时任务捞取的任务数量进行了降低，减小并发的几率

业务模型说明

业务模型一定程度上进行了简化，无关的信息都省略了

er图

一共3张表，2张业务表：批量操作记录表batch_operation，id为主键，还有个批次名称；一个批量操作明细表batch_operation_detail，id为主键，batch_id为批次号，对应batch_operation的主键id，还有一个类型type，加一个业务字段bussiness_id。

还有一张任务表，id为主键，biz_id为业务主键，这里一条batch_operation_detail对应一个任务记录

业务流程

线上的机器是集群，并且每个任务涉及到分布式锁。这里就暂且认为单机多线程，问题就出在红框的地方，下面就来模拟一下

上代码

先把上面2张表建好

DROP TABLE IF EXISTS `batch_operation`;
CREATE TABLE `batch_operation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `batch_operation_detail`;
CREATE TABLE `batch_operation_detail` (
  `id` bigint(20) NOT NULL,
  `batch_index` bigint(20) NOT NULL,
  `type` smallint(6) NOT NULL,
  `business_id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;