一个项目中mysql数据库经常死锁的问题解决记录

1、问题描述

此项目为一个物流系统，需要使用PDA对货物进行入库、备货、出货等操作，在系统开发测试过程中，经常发现死锁问题。

有这样一种业务场景：仓库对备货单上货进行扫码备货后，点击”完成”以确定完成了该备货单，才能进行下一步的发车动作，也即是说，如果不对单进行”完成”动作，就无法进行发车。仓库使用人员经常反馈已经点击了完成，但是不生效。此问题很诡异，有时很正常，有时频繁发生，联系开发人员检查代码，检查网络，很久都没有解决，很是头疼。仓库使用较大意见，项目推进遇到很大阻力。

最后经过摸索，问题得到解决。因为问题重现比较难，自己测试的时候都无问题（还怀疑过使用人员是否误报），此问题的解决刚开始要等出现的时候观察，慢慢有点头绪，再通过脚本方式进行巡检，再分析，最终才得到解决。解决方法记录如下

2、问题分析过程

2.1、编写事务监控与锁冲突监控脚本

普及下如下三个表的作用

innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

在出现问题的时候，通过观察innodb_trx发现，经常有一些事务执行很久，一直在执行，也不会关闭，同时观察innodb_locks表，会出现死锁现象

于是写了两个脚本分别对innodb_trx、innodb_locks表进行监控，脚本1每5秒钟记录innodb_trx的情况，脚本2每1分钟监控innodb_locks的情况，如果innodb_locks有数据代表有出现死锁

脚本1监控使用如下命令：此命令查询正在执行的事务，需要说明的是有事务不一定是有死锁，只是代表有一个事务在执行还未commit，本脚本不发告警邮件，只做记录

SELECT * FROM information_schema.INNODB_TRXG;

脚本2监控使用如下命令：如果有锁冲突，此表会有数据，如果未锁表，此表为空。此脚本发现有死锁就发邮件通知，以便问题的及时核查

SELECT * FROM information_schema.INNODB_locks;

2.2、开启mysql的general日志

开启general log会将所有到达MySQL Server的SQL语句记录下来。一般不会开启此功能，因为log的量会非常庞大。
但个别情况下可能会临时的开一会儿general log以供排障使用。

相关参数一共有3：general_log、log_output、general_log_file

• general_log：全局动态变量，默认关闭
• log_output ：全局动态变量，可取FILE、TABLE、NONE。其中TABLE存储方式比较方便按条件检索。若指定为NONE，则即使general_log开启了也不会记录log。若log_output指定为TABLE，则会在mysql数据库下边创建一个general_log表。需要注意的是该参数不仅仅影响general的存储方式还影响slow的存储方式，这一点需要特别注意。
• general_log_file：全局动态变量，日志文件名，不指定的话默认为hostname.log，位于数据目录下。

开启命令：

set global general_log=on

注：此命令是临时开启，如果mysql重启后回默认关闭

关闭命令：

set global general_log=off

2.3、核查分析

在出现死锁的时候，分别对general日志与脚本记录的innodb_trx数据进行分析，如下：

查看监控innodb_trx的日志情况，两个时间对了下（如上下图时间），确定了就是beihuovr存储过程执行才出现的这个一直执行的事务

分析：

同一个会话分别执行beihu和beihuovr存储过程出现问题，单独执行beihuovr没有问题，进一步分析beihu和beihuovr存储过程的内容

• beihuovr存储过程没有设置autocommit=0，既会自动提交
• beihu存储过程设置了autocommit=0，关闭了自动提交

按道理beihuovr这个存储过程的事务不会一直执行才对，那么就有一种可能，因为是同一个线程执行两个存储过程，可能是beihuovr存储过程继承了beihu存储过程的autocommit属性，存储过程里面也没有commit操作，导致这个存储过程的事务一直在执行。

3、解决办法

在每个存储过程的开始设置autocommit=1，问题解决。