作者介绍
贺春旸，普惠金融MySQL专家，《MySQL管理之道》第一版、第二版作者。曾任职于中国移动飞信、机锋安卓市场，拥有丰富的数据库管理经验。目前致力于MySQL、Linux等开源技术的研究。
现象
开年头一天上班，开发说程序连接不上数据库了，程序伴随着有大量的update锁超时，试着引导他们用SQLYOG客户端连接均无问题，然后查看监控图发现有大量的锁，如下图：
排查
-
数据库版本为：10.0.28-MariaDB-enterprise - MariaDB Enterprise Certified Binary
-
DELL R730XD 128G内存（BP 70G）/14块SAS 15000转RAID10
-
Update操作/秒30-50左右 innodb_lock_wait_timeout锁等待超时设置为10秒
在MySQL中information_schema库下有三个经典的数据字典表：INNODB_LOCK_WAITS、PROCESSLIST、INNODB_TRX，三者可以结合起来，就能够查到相对比较完整的阻塞信息和事务的情况。
1、通过以下SQL语句查看
SELECT
a.trx_id,
trx_state,
trx_started,
b.id AS thread_id,
b.info,
b.user,
b.host,
b.db,
b.command,
b.state
FROM
information_schema.`INNODB_TRX` a,
information_schema.`PROCESSLIST` b
WHERE a.trx_mysql_thread_id = b.id
ORDER BY a.trx_started;
查询结果如下：
请注意红色标识的，trx_state事务状态是RUNNING，但command那里查不到正在执行的SQL，显示的是Sleep状态。
2、通过以下SQL语句查看
SHOW ENGINE INNODB STATUS\G
查询结果如下：
请注意红色标识，事务ID和线程ID的状态为ACTIVE且运行了563秒，凭着以往处理故障的经验，这是N多条未提交事务的SQL引起的。
分析
当时慢查询日志里并没有记录慢SQL，线上设置的为1秒，询问开发是哪个SQL被锁了，也不清楚，说是通过框架生成的SQL语句，不好排查。
然后我们开启了general_log抓包，得到了很多简单的update，每次更新为1条记录，例如update t1 set name='aa' where id=XX，通过explain查看执行计划，where后面的字段都用到了索引，正常情况下执行这种SQL只需零点几毫秒的时间，但由于会话A对该记录更改未提交，会话B又对该记录进行更改，此时就会出现锁等待，直到超过了innodb_lock_wait_timeout参数设置的阈值。
在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量的连接数资源，造成严重的性能问题，甚至拖跨数据库。最终我们断定为开发的代码里应忘加了commit提交事务的操作，导致这一惨案的发生，可参考下面的重现操作。
前端应用JAVA Mybatis连接池一直不释放，积压过多的请求无法被处理，最终呈现给开发的现象是数据库又挂了。通俗来讲相当于在银行里办理业务，一个人办理不完，就得排队等待，越排越多，最终造成银行里人流混乱。
重现
MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> update t5 set name = 'aa' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
记住！千万别COMMIT！等待一会（抽一根烟的时间），然后重复执行上述命令，即可查看到跟我这里的截图完全一致。
解决方案
让开发再排查代码，已经不太现实了，时间不等人，业务不等人，不能再增加用户的投诉量了，DBA首先要保证的是数据库别跑挂了，先恢复！
下面介绍给大家一个参数innodb_kill_idle_transaction，意思为当一个事务长时间未提交，那么这个连接就不能关闭，内存就不释放，并发一大，导致DB连接数增多，就会对性能产生影响。
默认是0秒，你可以根据自己的情况设定阈值。超过这个阈值，服务端自动杀死未提交的空闲事务。
下面的截图展示了这一过程：
从图中结果上可以看出，当未提交的事务超过空闲时间30秒时，被后台进程自动KILL掉，执行COMMIT提交后，连接被强行断开。
设置这个参数后只针对新的连接有效，正在执行的连接无效，固我们让运维又依次重启前端应用后，数据库终于复活成功！
原文发布时间为：2017-02-08
本文来自云栖社区合作伙伴DBAplus
