MySQL死锁查询【原创】

死锁详情查询

SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;

 

navicat实时查询

show OPEN TABLES where In_use > 0;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

show processlist

将死锁语句导出

mysql -uroot -p  -e "show engine innodb status \G;" > /tmp/lock-`date +%F_%T`.txt

cat /tmp/lock.txt|grep -i -n lock
cat /tmp/lock.txt|grep -i -n "LATEST DETECTED DEADLOCK"

在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容

注意：如果死锁时间距离现在太远，并且Buffer pool hit rate正常就可以忽略
cat /tmp/lock.txt|grep -i -n "hit"
330:Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000