導(dǎo)讀:1建站知識(shí)【作者】 劉博：攜程技術(shù)保障中心數(shù)據(jù)庫(kù)高級(jí)經(jīng)理，主要關(guān)注Sql server和Mysql的運(yùn)維和故障處理。 【環(huán)境】 版本號(hào)：5.6.21 隔離級(jí)別：REPEA網(wǎng)站建設(shè)哪家好網(wǎng)seo優(yōu)化趨勢(shì)。

【作者】

劉博：攜程技術(shù)保障中心數(shù)據(jù)庫(kù)高級(jí)經(jīng)理，主要關(guān)注Sql server和Mysql的運(yùn)維和故障處理。

【環(huán)境】

版本號(hào)：5.6.21

隔離級(jí)別：REPEATABLE READ

【問(wèn)題描述】

接到監(jiān)控報(bào)警，有一個(gè)線上的應(yīng)用DeadLock報(bào)錯(cuò)，每15分鐘會(huì)準(zhǔn)時(shí)出現(xiàn)，報(bào)錯(cuò)統(tǒng)計(jì)如下圖：

登錄Mysql服務(wù)器查看日志：

mysql> show engine innodb status\G *** (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 UPDATE TestTable SET column1 = 1, Column2 = sysdate(), Column3= '026' Column4 = 0 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 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 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(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK' *** (2) HOLDS THE LOCK(S): 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;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 601 page no 89642 n bits 1000 index idx_column6 of table test.TestTable trx id 32231892482 lock_mode X locks rec but not gap waiting Record lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 3; hex 53454b; asc SEK;; 1: len 8; hex 80000000007eea14; asc ~ ;;

大致一看，更新同一索引的同一行，應(yīng)該是一個(gè)Block，報(bào)TimeOut的錯(cuò)才對(duì)，怎么會(huì)報(bào)DeadLock?

【初步分析】

先分析下(2) TRANSACTION，TRANSACTION 32231892482。

等待的鎖信息為：

0: len 3; hex 53454b; asc SEK;; 1: len 8; hex 80000000007eea14; asc

關(guān)鍵詞標(biāo)簽: SQL 故障 全過(guò)程