一个参数救活被hang住的数据库！

作者介绍

贺春旸，惠普金融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提交后，连接被强行断开。

设置这个参数后只针对新的连接有效，正在执行的连接无效，固我们让运维又依次重启前端应用后，数据库终于复活成功！

注：官方MySQL5.5/5.6是没有这个参数的。

参考文献：

• https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_kill_idle_transaction

• https://www.percona.com/doc/percona-server/5.7/management/innodb_kill_idle_trx.html

精选专题（官网：dbaplus.cn）

◆  近期热文  ◆  

◆  MVP专栏  ◆  

杨志洪丨杨建荣丨邹德裕丨韩锋丨欧阳辰

网易丨腾讯云丨百度丨朱祥磊丨卢钧轶