生产故障排查——mysql事务锁阻塞

故障现象

前端接口请求长时间无法响应，等待一段时候后返回错误；查看应用日志发现大量事务超时异常：

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

数据库有大量的执行语句阻塞，

问题分析

 应用层分析： 上述应用异常，表示当前事务操作的数据被另外的事务锁持有，处于等待状态，达到事务最大等待时间后，当前事务回滚，并抛出超时异常。
 分析对应执行的sql，发现内部只有一条update操作，而且update的where条件为主键。以主键作为update条件为行级锁，性能上不存在问题。
 数据库层分析： 上述分析被锁住的事务执行的sql性能正常，接着从数据库层面详细分析事务之间的等阻塞情况；
 查看事务锁等待情况：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

 发现有大量的事务等待锁的情况，追查找出最根源的事务：

requesting_trx_id: 等待中的事务id
requested_lock_id: 等待中的事务锁id
blocking_trx_id: 阻塞当前事务的事务id
blocking_lock_id: 阻塞当前事务的事务锁id

 根据blocking_trx_id向上查找，找到最顶层的事务id；查询对应事务正在执行的sql：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_id=xxx

  发下对应事务的执行sql 字段trx_query为空，代表当前事务已经没有执行语句；出现这种情况，有两种可能：

1、事务内部业务逻辑执行完后，没有关闭事务（没有执行commit，或rollback）；
2、应用程序在事务执行中间插入了其他耗时的任务；

查看事务开始时间，发现已经过去多个小时，事务中间插入耗时任务的可行性不大；同时，在应用层导出jstack分析线程堆栈也未发现异常阻塞的线程；所以，最大的可行性还是事务开启后未正常关闭；

trx_id: 事务id
trx_state: 事务状态
trx_started: 事务开始时间
trx_requested_lock_id: 阻塞当前事务的事务锁id
...
trx_mysql_thread_id: 当前事务的mysql线程id
...
trx_isolation_level: 事务锁级别（RR,RC..）
...

锁定问题后，接着需要排查应用逻辑。应用中的事务统一由spring事务管理，正常情况下不存在事务未关闭的情况；
在应用层未找到问题并解决前，可以从运维层先以治标的方式预防再次出现故障。下面的sql可以查询未正常关闭事务的线程（事务开启超过10分钟，且目前未执行语句，对应的mysql线程为sleep状态），定时kill掉：

SELECT 
  p.* 
FROM
  information_schema.INNODB_TRX it,
  information_schema.`PROCESSLIST` p,
  information_schema.`INNODB_LOCK_WAITS` ilw 
WHERE it.`trx_mysql_thread_id` = p.`ID` 
  AND it.`trx_query` IS NULL 
  AND p.`COMMAND` = 'Sleep' 
  AND p.`STATE` = '' 
  AND TIMESTAMPDIFF(SECOND, it.`trx_started`, NOW()) > 600 
  AND it.`trx_id` = ilw.`blocking_trx_id` ;

排查生产环境最近上线的程序变动，有一块逻辑采用了手动控制事务的方式；

DefaultTransactionDefinition transDefinition = new DefaultTransactionDefinition();
transDefinition.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRES_NEW);
TransactionStatus status = transactionManager.getTransaction(transDefinition);

代码层面在执行完毕时commit，异常捕获处rollback。逻辑上好像没有问题，事务也有结束。仔细分析事务里面的代码，中间有逻辑判断，判断不通过有return的操作。如果执行到return，后面的commit和异常里面的rollback都不会执行，于是添加finally事务处理：

if(null != status && !status.isCompleted()){
    transactionManager.rollback(status);
}

至此，问题处理完毕。对于事务的控制最好交给上层spring封装处理，如果确实需要手动控制事务，必须有finally事务判断处理逻辑。

总结

查询mysql事务列表：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX；

查询mysql事务锁等待情况：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS；

查看数据库innodb状态情况，可以看到事务占用的资源：

show engine innodb status;