MySQL死鎖如何解決

MySQL死鎖如何解決，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

環境準備

數據庫隔離級別：

  mysql> select @@tx_isolation;   +-----------------+   | @@tx_isolation  |   +-----------------+   | REPEATABLE-READ |   +-----------------+   1 row in set, 1 warning (0.00 sec)

自動提交關閉：

  mysql> set autocommit=0;   Query OK, 0 rows affected (0.00 sec)      mysql> select @@autocommit;   +--------------+   | @@autocommit |   +--------------+   |            0 |   +--------------+   1 row in set (0.00 sec)

表結構:

  //id是自增主鍵，name是非唯一索引，balance普通字段   CREATE TABLE `account` (    `id` int(11) NOT NULL AUTO_INCREMENT,     `name` varchar(255) DEFAULT NULL,     `balance` int(11) DEFAULT NULL,     PRIMARY KEY (`id`),     KEY `idx_name` (`name`) USING BTREE   ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表中的數據：

模擬并發

開啟兩個終端模擬事務并發情況，執行順序以及實驗現象如下：

1)事務A執行更新操作，更新成功

mysql> update account set balance =1000 where name ='Wei'; Query OK, 1 row affected (0.01 sec)

2)事務B執行更新操作，更新成功

mysql> update account set balance =1000 where name ='Eason';  Query OK, 1 row affected (0.01 sec)

3)事務A執行插入操作，陷入阻塞~

mysql> insert into account values(null,'Jay',100);

這時候可以用

select*frominformation_schema.innodb_locks;

查看鎖情況：

4)事務B執行插入操作，插入成功，同時事務A的插入由阻塞變為死鎖error。

mysql> insert into account values(null,'Yan',100);  Query OK, 1 row affected (0.01 sec)

鎖介紹

在分析死鎖日志前，先做一下鎖介紹，哈哈~

主要介紹一下兼容性以及鎖模式類型的鎖：

共享鎖與排他鎖

InnoDB 實現了標準的行級鎖，包括兩種：共享鎖(簡稱 s 鎖)、排它鎖(簡稱 x 鎖)。

• 共享鎖(S鎖)：允許持鎖事務讀取一行。

• 排他鎖(X鎖)：允許持鎖事務更新或者刪除一行。

如果事務 T1 持有行 r 的 s 鎖，那么另一個事務 T2 請求 r 的鎖時，會做如下處理：

• T2 請求 s 鎖立即被允許，結果 T1 T2 都持有 r 行的 s 鎖

• T2 請求 x 鎖不能被立即允許

如果 T1 持有 r 的 x 鎖，那么 T2 請求 r 的 x、s 鎖都不能被立即允許，T2 必須等待T1釋放 x  鎖才可以，因為X鎖與任何的鎖都不兼容。

意向鎖

• 意向共享鎖( IS 鎖)：事務想要獲得一張表中某幾行的共享鎖

• 意向排他鎖( IX 鎖)：事務想要獲得一張表中某幾行的排他鎖

比如：事務1在表1上加了S鎖后，事務2想要更改某行記錄，需要添加IX鎖，由于不兼容，所以需要等待S鎖釋放;如果事務1在表1上加了IS鎖，事務2添加的IX鎖與IS鎖兼容，就可以操作，這就實現了更細粒度的加鎖。

InnoDB存儲引擎中鎖的兼容性如下表：

記錄鎖(Record Locks)

• 記錄鎖是最簡單的行鎖，僅僅鎖住一行。如： SELECT c1 FROM t WHERE c1=10FOR  UPDATE

• 記錄鎖永遠都是加在索引上的，即使一個表沒有索引，InnoDB也會隱式的創建一個索引，并使用這個索引實施記錄鎖。

• 會阻塞其他事務對其插入、更新、刪除

記錄鎖的事務數據(關鍵詞：lock_mode X locks rec butnotgap)，記錄如下：

  RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`    trx id 10078 lock_mode X locks rec but not gap   Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0    0: len 4; hex 8000000a; asc     ;;    1: len 6; hex 00000000274f; asc     'O;;    2: len 7; hex b60000019d0110; asc        ;;

間隙鎖(Gap Locks)

• 間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最后一個索引之后的間隙。

• 使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條數據。

• 間隙鎖只阻止其他事務插入到間隙中，他們不阻止其他事務在同一個間隙上獲得間隙鎖，所以 gap x lock 和 gap s lock 有相同的作用。

間隙鎖的事務數據(關鍵詞：gap before rec)，記錄如下：

  RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`    trx id 38049 lock_mode X locks gap before rec   Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0    0: len 3; hex 576569; asc Wei;;    1: len 4; hex 80000002; asc     ;;

Next-Key Locks

• Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。

插入意向鎖(Insert Intention)

• 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，亦即多個事務在相同的索引間隙插入時如果不是插入間隙中相同的位置就不需要互相等待。

• 假設有索引值4、7，幾個不同的事務準備插入5、6，每個鎖都在獲得插入行的獨占鎖之前用插入意向鎖各自鎖住了4、7之間的間隙，但是不阻塞對方因為插入行不沖突。

事務數據類似于下面：

  RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`   trx id 8731 lock_mode X locks gap before rec insert intention waiting   Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0    0: len 4; hex 80000066; asc    f;;    1: len 6; hex 000000002215; asc     " ;;    2: len 7; hex 9000000172011c; asc     r  ;;...

鎖模式兼容矩陣(橫向是已持有鎖，縱向是正在請求的鎖)：

如何讀懂死鎖日志?

show engine innodb status

可以用 show engine innodb status，查看最近一次死鎖日志哈~，執行后，死鎖日志如下：

  0x243c   *** (1) TRANSACTION:   TRANSACTION 38048, ACTIVE 92 sec inserting   mysql tables in use 1, locked 1   LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2   MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update   insert into account values(null,'Jay',100)   *** (1) WAITING FOR THIS LOCK TO BE GRANTED:   RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`   trx id 38048 lock_mode X locks gap before rec insert intention waiting  Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0   0: len 3; hex 576569; asc Wei;;   1: len 4; hex 80000002; asc     ;;    *** (2) TRANSACTION:  TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000  mysql tables in use 1, locked 1  5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2  MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update  insert into account  values(null,'Yan',100)  *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`   trx id 38049 lock_mode X locks gap before rec  Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0   0: len 3; hex 576569; asc Wei;;   1: len 4; hex 80000002; asc     ;;    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account`   trx id 38049 lock_mode X insert intention waiting  Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0   0: len 8; hex 73757072656d756d; asc supremum;;    *** WE ROLL BACK TRANSACTION (1)

我們如何分析以上死鎖日志呢?

第一部分

1)找到關鍵詞TRANSACTION，事務38048

2)查看正在執行的SQL

insert into account values(null,'Jay',100)

3)正在等待鎖釋放(WAITING FOR THIS LOCK TO BE GRANTED)，插入意向排他鎖(lockmode X locks gap  before rec insert intention waiting)，普通索引(idxname)，物理記錄(PHYSICAL  RECORD)，間隙區間(未知，Wei);

第二部分

1)找到關鍵詞TRANSACTION，事務38049

2)查看正在執行的SQL

insert into account values(null,'Yan',100)

3)持有鎖(HOLDS THE LOCK)，間隙鎖(lockmode X locks gap before rec)，普通索引(index  idxname)，物理記錄(physical record)，區間(未知，Wei);

4)正在等待鎖釋放(waiting for this lock to be granted)，插入意向鎖(lockmode X insert  intention waiting)，普通索引上(index idxname)，物理記錄(physical record)，間隙區間(未知，+∞);

5)事務1回滾(we roll back transaction 1);

查看日志結果

查看日志可得：

• 事務A正在等待的插入意向排他鎖(事務A即日志的事務1，根據insert語句來對號入座的哈)，正在事務B的懷里~

• 事務B持有間隙鎖，正在等待插入意向排它鎖

這里面，有些朋友可能有疑惑，

• 事務A持有什么鎖呢?日志根本看不出來。它又想拿什么樣的插入意向排他鎖呢?

• 事務B拿了具體什么的間隙鎖呢?它為什么也要拿插入意向鎖?

• 死鎖的死循環是怎么形成的?目前日志看不出死循環構成呢?

我們接下來一小節詳細分析一波，一個一個問題來~

死鎖分析

死鎖死循環四要素

• 互斥條件：指進程對所分配到的資源進行排它性使用，即在一段時間內某資源只由一個進程占用。如果此時還有其它進程請求資源，則請求者只能等待，直至占有資源的進程用畢釋放。

• 請求和保持條件：指進程已經保持至少一個資源，但又提出了新的資源請求，而該資源已被其它進程占有，此時請求進程阻塞，但又對自己已獲得的其它資源保持不放。

• 不剝奪條件：指進程已獲得的資源，在未使用完之前，不能被剝奪，只能在使用完時由自己釋放。

• 環路等待條件：指在發生死鎖時，必然存在一個進程——資源的環形鏈，即進程集合{P0，P1，P2，···，Pn}中的P0正在等待一個P1占用的資源;P1正在等待P2占用的資源，……，Pn正在等待已被P0占用的資源。

事務A持有什么鎖呢?它又想拿什么樣的插入意向排他鎖呢?

為了方便記錄，例子用W表示Wei，J表示Jay，E表示Eason哈~

我們先來分析事務A中update語句的加鎖情況~

update account set balance =1000 where name ='Wei';

間隙鎖：

• Update語句會在非唯一索引的name加上左區間的間隙鎖，右區間的間隙鎖(因為目前表中只有name='Wei'的一條記錄，所以沒有中間的間隙鎖~)，即(E,W)  和(W，+∞)

• 為什么存在間隙鎖?因為這是RR的數據庫隔離級別，用來解決幻讀問題用的~

記錄鎖

• 因為name是索引，所以該update語句肯定會加上W的記錄鎖

Next-Key鎖

• Next-Key鎖=記錄鎖+間隙鎖，所以該update語句就有了(E，W]的 Next-Key鎖

綜上所述，事務A執行完update更新語句，會持有鎖：

• Next-key Lock：(E，W]

• Gap Lock ：(W，+∞)

我們再來分析一波事務A中insert語句的加鎖情況

insert into account values(null,'Jay',100);

間隙鎖：

• 因為Jay(J在E和W之間)，所以需要請求加(E,W)的間隙鎖

插入意向鎖(Insert Intention)

• 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，即事務A需要插入意向鎖(E,W)

因此，事務A的update語句和insert語句執行完，它是持有了 (E，W]的 Next-Key鎖，(W，+∞)的Gap鎖，想拿到  (E,W)的插入意向排它鎖，等待的鎖跟死鎖日志是對上的，哈哈~

事務B擁有了什么間隙鎖?它為什么也要拿插入意向鎖?

同理，我們再來分析一波事務B，update語句的加鎖分析：

update account set balance =1000 where name ='Eason';

間隙鎖：

• Update語句會在非唯一索引的name加上左區間的間隙鎖，右區間的間隙鎖(因為目前表中只有name='Eason'的一條記錄，所以沒有中間的間隙鎖~)，即(-∞，E)和(E，W)

記錄鎖

• 因為name是索引，所以該update語句肯定會加上E的記錄鎖

Next-Key鎖

• Next-Key鎖=記錄鎖+間隙鎖，所以該Update語句就有了(-∞，E]的 Next-Key鎖

綜上所述，事務B執行完update更新語句，會持有鎖：

• Next-key Lock：(-∞，E]

• Gap Lock ：(E，W)

我們再來分析一波B中insert語句的加鎖情況

insert into account values(null,'Yan',100);

間隙鎖：

• 因為Yan(Y在W之后)，所以需要請求加(W,+∞)的間隙鎖

插入意向鎖(Insert Intention)

• 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，即事務A需要插入意向鎖(W,+∞)

所以，事務B的update語句和insert語句執行完，它是持有了 (-∞，E]的 Next-Key鎖，(E，W)的Gap鎖，想拿到  (W,+∞)的間隙鎖，即插入意向排它鎖，加鎖情況跟死鎖日志也是對上的~

死鎖真相還原

接下來呢，讓我們一起還原死鎖真相吧~哈哈~

• 事務A執行完Update Wei的語句，持有(E，W]的Next-key Lock，(W，+∞)的Gap Lock ，插入成功~

• 事務B執行完Update Eason語句，持有(-∞，E]的 Next-Key Lock，(E，W)的Gap Lock，插入成功~

• 事務A執行Insert Jay的語句時，因為需要(E，W)的插入意向鎖，但是(E，W)在事務B懷里，所以它陷入心塞~

• 事務B執行Insert Yan的語句時，因為需要(W,+∞) 的插入意向鎖，但是(W,+∞) 在事務A懷里，所以它也陷入心塞。

• 事務A持有(W，+∞)的Gap Lock，在等待(E，W)的插入意向鎖，事務B持有(E，W)的Gap鎖，在等待(W,+∞)  的插入意向鎖，所以形成了死鎖的閉環~(Gap鎖與插入意向鎖會沖突的，可以看回鎖介紹的鎖模式兼容矩陣哈~)

• 事務A,B形成了死鎖閉環后，因為Innodb的底層機制，它會讓其中一個事務讓出資源，另外的事務執行成功，這就是為什么你最后看到事務B插入成功了，但是事務A的插入顯示了Deadlock  found ~

關于MySQL死鎖如何解決問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。