MySQL中怎么排查死鎖

這篇文章將為大家詳細講解有關(guān)MySQL中怎么排查死鎖，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

死鎖起因

先介紹一下數(shù)據(jù)庫和表情況，因為涉及到公司內(nèi)部真是的數(shù)據(jù)，所以以下都做了模擬，不會影響具體的分析。

我們采用的是 5.5 版本的 mysql 數(shù)據(jù)庫，事務(wù)隔離級別是默認的 RR（Repeatable-Read），采用 innodb 引擎。假設(shè)存在 test 表：

CREATE TABLE `test` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `a` int(11) unsigned DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

表的結(jié)構(gòu)很簡單，一個主鍵 id，另一個***索引 a。表里的數(shù)據(jù)如下：

mysql> select * from test;  +----+------+  | id | a    |  +----+------+  |  1 |    1 |  |  2 |    2 |  |  4 |    4 |  +----+------+  3 rows in set (0.00 sec)

出現(xiàn)死鎖的操作如下：

然后我們可以通過 SHOW ENGINE INNODB STATUS; 來查看死鎖日志：

------------------------  LATEST DETECTED DEADLOCK  ------------------------  170219 13:31:31  *** (1) TRANSACTION:  TRANSACTION 2A8BD, ACTIVE 11 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 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating  delete from test where a = 2  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32  0: len 4; hex 00000002; asc     ;;  1: len 4; hex 00000002; asc     ;;  *** (2) TRANSACTION:  TRANSACTION 2A8BC, ACTIVE 18 sec inserting  mysql tables in use 1, locked 1  4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2  MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update  insert into test (id,a) values (10,2)  *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32  0: len 4; hex 00000002; asc     ;;  1: len 4; hex 00000002; asc     ;;  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32  0: len 4; hex 00000002; asc     ;;  1: len 4; hex 00000002; asc     ;;  *** WE ROLL BACK TRANSACTION (1)

分析

閱讀死鎖日志

遇到死鎖，***步就是閱讀死鎖日志。死鎖日志通常分為兩部分，上半部分說明了事務(wù) 1 在等待什么鎖：

170219 13:31:31  *** (1) TRANSACTION:  TRANSACTION 2A8BD, ACTIVE 11 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 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating  delete from test where a = 2  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32  0: len 4; hex 00000002; asc     ;;  1: len 4; hex 00000002; asc     ;;

從日志里我們可以看到事務(wù) 1 當前正在執(zhí)行 delete from test where a = 2，該條語句正在申請索引 a 的 X 鎖，所以提示 lock_mode X waiting。

然后日志的下半部分說明了事務(wù) 2 當前持有的鎖以及等待的鎖： 

*** (2) TRANSACTION:  TRANSACTION 2A8BC, ACTIVE 18 sec inserting  mysql tables in use 1, locked 1  4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2  MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update  insert into test (id,a) values (10,2)  *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32  0: len 4; hex 00000002; asc     ;;  1: len 4; hex 00000002; asc     ;;  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32  0: len 4; hex 00000002; asc     ;;  1: len 4; hex 00000002; asc     ;;

從日志的 HOLDS THE LOCKS(S) 塊中我們可以看到事務(wù) 2 持有索引 a 的 X 鎖，并且是記錄鎖（Record Lock）。該鎖是通過事務(wù) 2 在步驟 2 執(zhí)行的 delete 語句申請的。由于是 RR 隔離模式下的基于***索引的等值查詢（Where a = 2），所以會申請一個記錄鎖，而非 next-key 鎖。

從日志的 WAITING FOR THIS LOCK TO BE GRANTED 塊中我們可以看到事務(wù) 2 正在申請 S 鎖，也就是共享鎖。該鎖是 insert into test (id,a) values (10,2) 語句申請的。insert 語句在普通情況下是會申請排他鎖，也就是 X 鎖，但是這里出現(xiàn)了 S 鎖。這是因為 a 字段是一個***索引，所以 insert 語句會在插入前進行一次 duplicate key 的檢查，為了使這次檢查成功，需要申請 S 鎖防止其他事務(wù)對 a 字段進行修改。

那么為什么該 S 鎖會失敗呢？這是對同一個字段的鎖的申請是需要排隊的。S 鎖前面還有一個未申請成功的 X 鎖，所以 S 鎖必須等待，所以形成了循環(huán)等待，死鎖出現(xiàn)了。

通過閱讀死鎖日志，我們可以清楚地知道兩個事務(wù)形成了怎樣的循環(huán)等待，再加以分析，就可以逆向推斷出循環(huán)等待的成因，也就是死鎖形成的原因。

死鎖形成流程圖

為了讓大家更好地理解死鎖形成的原因，我們再通過表格的形式闡述死鎖形成的流程：

拓展

在排查死鎖的過程中，有個同事還發(fā)現(xiàn)了上述場景會產(chǎn)生另一種死鎖，該場景無法通過手工復(fù)現(xiàn)，只有高并發(fā)場景下才有可能復(fù)現(xiàn)。

該死鎖對應(yīng)的日志這里就不貼出了，與上一個死鎖的核心差別是事務(wù) 2 等待的鎖從 S 鎖換成了 X 鎖，也就是 lock_mode X locks gap before rec insert intention waiting。我們還是通過表格來詳細說明該死鎖產(chǎn)生的流程：

關(guān)于MySQL中怎么排查死鎖就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。