計算機故障排查思路，mysql deadlock 記錄_MySQL DeadLock故障排查全過程記錄

【作者】

劉博：攜程技術保障中心數據庫高級經理，主要關注Sql server和Mysql的運維和故障處理。

【環境】

版本號：5.6.21

計算機故障排查思路？隔離級別：REPEATABLE READ

【問題描述】

接到監控報警，有一個線上的應用DeadLock報錯，每15分鐘會準時出現，報錯統計如下圖：

登錄Mysql服務器查看日志：

mysql> show engine innodb statusG

故障排查、*** (1) TRANSACTION:

TRANSACTION 102973, ACTIVE 11 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 6, OS thread handle 140024996574976, query id 83 localhost us updating

mysql數據庫連接池無法釋放？UPDATE TestTable

SET column1 = 1,

Column2 = sysdate(),

Column3= '026'

Column4 = 0

mysql數據庫連接池設置多少、AND column5 = 485

AND column6 = 'SEK'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_column6 of table test.TestTable trx id 102973 lock_mode X waiting

Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

mysql慢查詢優化排查，0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007e1452; asc ~ R;;

*** (2) TRANSACTION:

TRANSACTION 102972, ACTIVE 26 sec starting index read

mysql tables in use 3, locked 3

mysql問題排查、219 lock struct(s), heap size 24784, 2906 row lock(s), undo log entries 7

MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updating

UPDATE TestTable

SET Column1 = 1,

Column2 = sysdate(),

MySQL shell、Column3 = '026'

Column4 = 0

AND Column5 = 485

AND Column6 = 'SEK'

*** (2) HOLDS THE LOCK(S):

mysql、RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_Column6 of table test.TestTable trx id 102972 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

故障，1: len 8; hex 80000000007e1452; asc ~ R;;