MySQL InnoDB 查看 deadlock 死結(死鎖)記錄

[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;

[Lock wait timeout exceeded]
某些 deadlock 的情況，MySQL 無法判斷。

就會發生等到超過 innodb_lock_wait_timeout 設定值的時間，

然後出現錯誤訊息：

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

出錯後，再使用「SHOW ENGINE INNODB STATUS」查看，看不出有 DEADLOCK 關鍵字。


此時，可先看 innodb_lock_wait_timeout 設定，看逾時時間多久。(預設是50秒)

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

可暫時設一個方便後面debug觀察的時間，避免太短來不急觀察，太長試一次等太久。

例如20秒：

SET GLOBAL innodb_lock_wait_timeout=20;

然後，趁被卡住時，查看 PROCESSLIST，應該可以看到狀態(State)執行中被卡住的process。

SHOW PROCESSLIST;

查看「Info」中的 SQL 語句，可知道哪個部分卡住了。
「Id 」則為此連線的 thread ID

或被卡住時，查看 INFORMATION_SCHEMA.INNODB_TRX

可得知目前正在執行的每個交易的訊息，包括交易是否正在等待鎖定、交易何時開始以及交易正在執行的 SQL 語句。

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

這邊應該可以看到「trx_state」欄位資料為 「LOCK WAIT」的交易，
然後看「trx_query」欄位，可得知哪個 SQL 語句被卡住了。
「trx_mysql_thread_id」為此連線的 thread ID

如果被卡住時，查看

SHOW ENGINE INNODB STATUS

也可發現有一些 lock 訊息

---TRANSACTION 2D1261B, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 113368, OS thread handle 0x34ec, query id 638222 ..... Sending data
被卡住的 SQL 語句
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12650 page no 138 n bits 424 ..... trx id 2D1261B lock_mode X waiting
Record lock, heap no 122 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc     ;;
1: len 1; hex 80; asc  ;;
2: len 1; hex 80; asc  ;;
3: len 1; hex 01; asc  ;;
4: len 13; hex 31363237313332313832303633; asc 1627132182063;;

------------------
---TRANSACTION 2D12619, ACTIVE 6 sec
18 lock struct(s), heap size 3112, 104 row lock(s), undo log entries 5
MySQL thread id 113366, OS thread handle 0x4d98, query id 638215
Trx read view will not see trx with id >= 2D1261B, sees < 2D1261B

查目前連線的 thread ID ：

SELECT CONNECTION_ID();

參考：

• https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks MySQL :: MySQL 5.6 Reference Manual :: 14.14 InnoDB Startup Options and System Variables
• https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks.html
  MySQL :: MySQL 5.6 Reference Manual :: 14.7.5 Deadlocks in InnoDB
• https://stackoverflow.com/questions/5353877/mysql-transaction-deadlock
  mysql transaction deadlock - Stack Overflow
• https://stackoverflow.com/questions/11331029/trying-to-understand-mysql-deadlock-on-innodb-table
  transactions - Trying to understand MySQL deadlock on InnoDB table - Stack Overflow
• https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/
  How to deal with MySQL deadlocks
• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
  MySQL :: MySQL 5.7 Reference Manual :: 14.15 InnoDB Startup Options and System Variables
• https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-trx-table.html
  MySQL :: MySQL 5.7 Reference Manual :: 24.4.28 The INFORMATION_SCHEMA INNODB_TRX Table
• https://ningyu1.github.io/site/post/75-mysql-lock-wait-timeout-exceeded/
  MySql Lock wait timeout exceeded该如何处理？ - 凝雨 - Yun