死锁详情查询

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`.txtcat /tmp/lock.txt|grep -i -n lockcat /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