2015年6月8日 星期一

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();


參考：

張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)