今天使用SQLyog给同事执行一个SQL等待了非常长的时间都没有完毕,由于赶着处理其他东西,同事让我终止掉之前的SQL后重新提交了一些经过优化的SQL,可是执行过程中发现出现“ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction”的错误提示,意思是说锁超时。一开始想着是语句太多,可是精简之后还是出现这样的提示,可以判断跟语句无关了,使用show full processlist也不能方便的定位出是什么语句导致被锁,后来查资料发现可以通过information_schema库中的三个表来查找被锁住的语句:
innodb_trx表可以查询当前运行的所有事务
innodb_locks表可以查询当前出现的锁
innodb_lock_waits表可以查询锁等待的对应关系
知道如何查询就好办了,再次执行正常的SQL语句,然后查询表中内容
SELECT * FROM information_schema.innodb_trx \G #查看正在锁的事务
select * from information_schema.innodb_lock_waits; #查看等待锁的事务,可以找到锁的源头
果然看到了状态是Locking,而导致锁的原因就是最开始那条被中断的语句没有正常结束,这个时候找到事务的ID号(trx_mysql_thread_id)准备杀掉。
在MySQL命令行中将其kill即可,需要注意的是kill的是线程trx_mysql_thread_id而不是trx_id
mysql > kill 386014769
再次执行语句,恢复正常