清理MySQL死锁事务线程

MySQL死锁事务的处理方法:通过日志信息分析死锁事务的线程id,使用kill命令清理死锁事务。

方式一

查看事务ID:

mysql> show engine innodb status;

从状态日志TRANSACTIONS信息中找出事务id:

 ------------
 TRANSACTIONS
 ------------
 Trx id counter 383404
 Purge done for trx's n:o < 383334 undo n:o < 0 state: running but idle
 History list length 35
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 422076598101416, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598102288, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598099672, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598094440, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598104904, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598105776, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598104032, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598103160, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598097928, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598096184, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598095312, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598100544, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 422076598093568, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 383332, ACTIVE 1974 sec
 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
 MySQL thread id 83611, OS thread handle 140600539936512, query id 1453332 171.44.100.54 root
 Trx read view will not see trx with id >= 383332, sees < 383332

说明:倒数第二行的MySQL thread id即事务id。

使用mysql命令行结束事务:

mysql>kill 83611;

方式二

SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX;

通过更多列信息确认问题线程:

SELECT * FROM information_schema.INNODB_TRX;

使用mysql kill命令结束线程:

mysql>:kill xxxx;

注:非操作系统kill命令。