mysql 命令 kill_MySQL之死锁检测

最近，笔者在查看线上服务日志时，发现spring大量异常，异常中都显示了同样的报错信息，信息如下。

Deadlock found when trying to get lock; try restarting transaction

调研之后发现是mysql发生了死锁，这也是笔者第一次遇到数据库死锁问题，详细研究后，将过程记录为文章，以便日后参考回顾。

1. 死锁

死锁指的是两个或两个以上的进程(线程)在执行的时候，因为争夺资源出现相互等待的一种现象。产生死锁需要同时满足以下四个条件

1. 互斥条件:一个资源每次只能一个进程使用
2. 不可抢占：进程1在获取资源，使用的过程中，进程2不能抢占进程1正在使用的资源
3. 占有且等待：进程在申请资源的时，不能释放已经持有的资源
4. 循环等待:进程1等待进程2资源，同时进程2等待进程1持有的资源，出现循环等待的情况

2. 死锁日志

为了查找造成死锁的sql语句，笔者通过show engine innodb status查看到最近的一次死锁日志，通过这个sql语句，我们就能确定造成死锁的事务

//事务一相关信息
*** (1) TRANSACTION:
TRANSACTION 50E, ACTIVE 66 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 7, OS thread handle 0x27448, query id 82 localhost 127.0.0.1 root Updating
//当前事务正在执行的sql语句
update tb1 set c1= 10 where id =5
//以下信息记录了锁等待信息
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
//正在申请主键索引行记录的x锁
RECORD LOCKS space id 0 page no 3328 n bits 72 index `PRIMARY` of table `test`.`tb1` trx id 50E lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

//事务2相关信息
*** (2) TRANSACTION:
TRANSACTION 50F, ACTIVE 47 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 8, OS thread handle 0x277e4, query id 83 localhost 127.0.0.1 root Updating
update tb1 set c1= 10 where id =5
//正在持有的锁：主键索引为5的行记录级别的S锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3328 n bits 72 index `PRIMARY` of table `test`.`tb1` trx id 50F lock mode S locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000050d; asc       ;;
 2: len 7; hex 8b00000d080110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3328 n bits 72 index `PRIMARY` of table `test`.`tb1` trx id 50F lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000050d; asc       ;;
 2: len 7; hex 8b00000d080110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

WE ROLL BACK TRANSACTION (2)

笔者发现死锁日志最后一行出现了WE ROLL BACK TRANSACTION (2)信息。这是Mysql innodb引擎自动检测死锁机制，MySQL选择打断其中一个事务破坏死锁条件来消除死锁。Mysql官方文档上显示，mysql会选择杀死小的事务，这里的小指的是执行的insert,update,detected语句数目小的事务。需要注意的是mysql innodb死锁检测只能针对innodb引擎级别死锁，innodb死锁检测不能检测到应用层级别死锁

3. 死锁复现

为了避免泄露公司的业务和数据，笔者在开发环境复现了这个死锁

表结构如下所示

事务执行顺序如下

为什么这个sql语句会造成死锁呢？原因如下

4. 解决方案

通过上文的死锁复现，笔者发现事务2和事务3的作用一样的，他们都操作了相同的资源，执行事务2和事务3可能是同一进程内的线程执行，也可能是位于不同进程的线程执行。针对这种情况，笔者在生产环境使用分布式锁解决这种死锁场景，笔者以操作的行记录id作为分布式资源id，关于分布式锁，可以参考笔者先前写的文章

5. 发现死锁

上述场景，Mysql发现了死锁，并选择回滚其中一个事务解决了死锁问题，但是老版本的mysql没有死锁检测机制，如果出现死锁，连接可能都会处于等待状态，直到50S的锁等待超时，这会长时间占据数据库连接，导致数据库连接池连接耗尽，tomcat无法获取数据库连接，一直处于等待状态，随后tomcat队列排满后，整个服务就会处于僵死状态，在生产环境中是非常大的事故。如果发现mysql执行SQL语句长时间不响应。我们可以通过show full processlist 命令查看当前所有数据库连接状态，如果连接在等待锁资源，在State状态会显示waiting for table metadata lock信息，同时可以通过info信息查看数据库连接执行了哪一条sql语句，如果所有的等待锁的连接执行的sql语句都涉及到了同一张表，那么就能断定哪站表发生了死锁

6. 查找连接

知道哪张表被锁定仍然无法解决我们的问题，我们需要知道哪个数据库连接对表加了锁，才能kill连接，从mysql5.5开始，information_schema增加了三个关于锁的表，通过这三张表，我们能够找到连接id

6.1 innodb_locks

这张表提供了各个事务请求的数据库锁但是仍然没有获取的数据库锁。这张表提供的最重要的信息是请求锁的事务id

6.2 innodb_trx

通过这张表我们能查到当前innodb引擎执行的所有事务id以及当前执行事务的数据库连接id，于是便能通过kill命令杀死数据库连接，更关键的是我们能查找到事务正在执行的sql语句

6.3 innodb_lock_waits

这张表中requesting_trx_id代表了申请锁资源的事务ID，requesting_lock_id代表申请的锁id，blocking_trx_id代表了阻塞事务70E的事务id，blocking_lock_id代表了阻塞事务70E的锁的ID

出现死锁后，我们可以通过innodb_lock_waits获取相互等待的事务id，通过事务id从innodb_trx查找到数据库连接id，然后使用kill杀死连接

7. 应用层死锁

innodb_locks，innodb_trx，innodb_lock_waits三张表只能查找到innodb引擎层死锁的数据库连接id，对于server层死锁就无能为力了。比如说下述命令加的锁

lock tables tb1 write;
flush tables with read lock;

针对这种死锁，无法通过杀死数据库连接id达到释放锁的目的，针对这种情况笔者尚未找到解决方案，在生产环境下，笔者也遇到过这种情况造成的死锁，笔者只能寻求DBA重启数据库可以暂时解决死锁问题，但是最好的解决方案是尽量不要使用手动加锁命令。