MySQL InnoDB 查看 deadlock 死結記錄

執行下列指令，可查到最後一次發生 deadlock 的記錄

SHOW ENGINE INNODB STATUS\G;

輸出的資料很多，找到 LATEST DETECTED DEADLOCK 這一段。
可發現有 (1) TRANSACTION、(2) TRANSACTION，兩個交易發生 deadlock，
最後面系統選擇 ROLL BACK TRANSACTION (1)

LATEST DETECTED DEADLOCK
------------------------
2015-06-06 21:31:08 7f3c0b104700
*** (1) TRANSACTION:
TRANSACTION 29442129, ACTIVE 15 sec inserting
(...略...)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
(...略...)

*** (2) TRANSACTION:
TRANSACTION 29442027, ACTIVE 16 sec inserting
(...略...)
*** (2) HOLDS THE LOCK(S):
(...略...)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
(...略...)

*** WE ROLL BACK TRANSACTION (1)

若是 MySQL 5.6 之後的版本，增加了  innodb_print_all_deadlocks 設定。
開啟後，會將所有 deadlock 過程記錄在 error_log 檔。

innodb_print_all_deadlocks=ON

運行中開啟 innodb_print_all_deadlocks

mysql> SET GLOBAL innodb_print_all_deadlocks=ON;
mysql> SELECT @@global.innodb_print_all_deadlocks;

參考：
MySQL :: MySQL 5.6 Reference Manual :: 14.11 InnoDB Startup Options and System Variables
MySQL :: MySQL 5.6 Reference Manual :: 14.2.2.9 How to Cope with Deadlocks
mysql transaction deadlock
Trying to understand MySQL deadlock on InnoDB table
How to deal with MySQL deadlocks