2015年6月8日 星期一

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

沒有留言:

張貼留言