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
沒有留言:
張貼留言