手把手教你分析解決MySQL死鎖問題

雨點的名字發表於2022-01-16
MySql
手把手教你分析解決MySQL死鎖問題

在生產環境中如果出現MySQL死鎖問題該如何排查和解決呢，本文將模擬真實死鎖場景進行排查，最後總結下實際開發中如何儘量避免死鎖發生。

一、準備好相關資料和環境

當前自己的資料版本是8.0.22

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

資料庫隔離級別(預設隔離級別)

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

自動提交關閉

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (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)

表結構

這個age為 非唯一索引，這點對下面整個案例非常重要。

-- id是自增主鍵，age是非唯一索引，name普通欄位
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `age` int DEFAULT NULL COMMENT '年齡',
  `name` varchar(255)  DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='使用者資訊表';

表中暫時先插入兩條資料

手把手教你分析解決MySQL死鎖問題

二、模擬出真實死鎖案例

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

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

mysql> update  user  set name = 'wangwu' where age= 20;
Query OK, 1 row affected (0.00 sec)
  1. 事務B執行更新操作，更新成功
mysql> update  user  set name = 'zhaoliu' where age= 10;
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into user values (null, 15, "tianqi");
手把手教你分析解決MySQL死鎖問題

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

insert into user values (null, 30, "wangba");
Query OK, 1 row affected (0.00 sec)

事務A的插入操作變成報錯。

手把手教你分析解決MySQL死鎖問題

上面四步操作後，我們分別對事務A和事務B進行commit操作。

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

我們再來看資料庫中表的資料。

手把手教你分析解決MySQL死鎖問題

我們發現，事務B的所有操作最終都成功了，而事務A的操作因為報錯都回滾了。所以事務A的操作都失敗。

那既然是死鎖，為什麼回滾事務A,而不是事務B,是隨機的還是有機制在裡面？

我們可以理解死鎖是資料庫對事務的保護機制,一旦發生死鎖,MySQL會選擇相對小的事務(undo較少的)進行回滾。


三、檢視分析死鎖日誌

可以用 show engine innodb status，檢視最近一次死鎖日誌哈，執行後，死鎖日誌如下(只展示部分日誌)：

LATEST DETECTED DEADLOCK
------------------------
2021-12-24 06:02:52 0x7ff7074f8700
*** (1) TRANSACTION:
TRANSACTION 2554368, ACTIVE 22 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

INSERT INTO user VALUES (NULL, 15, "tianqi")

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 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;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2554369, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
INSERT INTO user VALUES (NULL, 30, "wangba")

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 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、事務A相關日誌

1）找到關鍵詞TRANSACTION，事務2554368

手把手教你分析解決MySQL死鎖問題

2）檢視事務1正在執行的sql

insert into user values (null, 15, "tianqi")
  1. 檢視當前事務已佔有的鎖和等待其它事務釋放的鎖
手把手教你分析解決MySQL死鎖問題

2、事務B相關日誌

1）找到關鍵詞TRANSACTION，事務2554369

手把手教你分析解決MySQL死鎖問題

2）檢視事務2正在執行的sql

insert into user values (null, 30, "wangba")
  1. 檢視當前事務已佔有的鎖和等待其它事務釋放的鎖
手把手教你分析解決MySQL死鎖問題

3、總結

這裡把一些關鍵的日誌截圖了下

手把手教你分析解決MySQL死鎖問題

我們把這張圖換一種方式畫下

手把手教你分析解決MySQL死鎖問題

1）從圖中可以很明顯的看出,事務1和事務2都在等對方的鎖釋放，所以導致了死鎖問題。而且最終是事務1進行了回滾。

2）這個日誌提供比較重要的資訊就是我們可以看出的是哪兩條sql在互相一直等待其它事務的鎖釋放而產生了死鎖，也知道是哪個索引導致產生的死鎖，同時也知道最終哪個事務

被回滾了。

3）如果上面的資訊還不能幫你定位解決問題，那可以問資料庫DB要詳細的binlog日誌來分析這段時間這兩個事務具體執行的所有sql。


四、總結分析案例中產生死鎖的原因

這個分析就需要對MySQL中的各種鎖機制有所瞭解，還不清楚的話可以看我之前寫的兩篇文章，看完你就清楚我下面所寫的了。

1、事務A的SQL產生了哪些鎖

1) 事務A的update語句產生哪些鎖

我們先來看

update  user  set name = 'wangwu' where age= 20;

記錄鎖

因為是等值查詢，所以這裡會在滿足age=20的所有資料請求一個記錄鎖。

間隙鎖

因為這裡是非唯一索引的等值查詢,所以一樣會產生間隙鎖(如果是唯一索引的等值查詢那就不會產生間隙鎖，只會有記錄鎖),因為這裡只有2條記錄

所以左邊為(10,20),右邊因為沒有記錄了，所以請求間隙鎖的範圍就是(20,+∞),加一起就是(10,20) +（20,+∞)。

Next-Key鎖

Next-Key鎖=記錄鎖+間隙鎖，所以該Update語句就有了（10,+∞)的 Next-Key鎖

事務A的install語句產生哪些鎖

INSERT INTO user VALUES (NULL, 15, "tianqi");

間隙鎖

因為age 15(在10和20之間)，所以需要請求加(10,20)的間隙鎖

插入意向鎖（Insert Intention）

插入意向鎖是在插入一行記錄操作之前設定的一種間隙鎖，這個鎖釋放了一種插入方式的訊號，即事務A需要插入意向鎖(10,20),這個插入鎖的作用就是提高插入效率的，在分析

死鎖的時候我們可以不用關心它，只關心上面間隙鎖就好了。

2、事務B的SQL產生了哪些鎖

事務B的update語句產生哪些鎖

我們先來看

update  user  set name = 'zhaoliu' where age= 10

記錄鎖

因為是等值查詢，所以這裡會在滿足age=10的所有資料請求一個記錄鎖。

間隙鎖

因為左邊沒有記錄,右邊有一個age=20的記錄，所以間隙鎖的範圍是(-∞,10),右邊為(10,20),一起就是(-∞,10)+(10,20)。

Next-Key鎖

Next-Key鎖=記錄鎖+間隙鎖，所以該Update語句就有了（-∞,20)的 Next-Key鎖

事務A的install語句產生哪些鎖

INSERT INTO user VALUES (NULL, 30, "wangba")

間隙鎖

  • 因為age 30(左邊是20，右邊沒有值)，所以需要請求加(20,+∞)的間隙鎖

插入意向鎖（Insert Intention）

  • (20,+∞)

鎖都分析清楚了，接下來再來看下是什麼地方導致死鎖的呢？

手把手教你分析解決MySQL死鎖問題

這樣以來產生整個死鎖的原因也就清楚了，不過這裡再補充兩點

1）MySQL的間隙鎖雖然有左開右閉的原則,但是其實這個並不完全正確,因為它有可能是左閉右開,也可能是左開右開，它會跟你插入主鍵值位置有關，具體的可以看我之前寫的

一篇文章裡面有完整示例MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示。所以這裡間隙鎖寫的都是左開右開的範圍，可能臨界點有點模糊，但不影響分析這個案例的死鎖問題。

2）通過事務A和事務B的update語句，我們可以發現其實它們都持有間隙鎖(10,20)的這段範圍,說明間隙鎖範圍是可以相互相容的，意思就是隻要你的10不在我（10,+∞)的間隙鎖

範圍內，那就可以產生部分重合的間隙鎖，也就是這裡的(10,20)。


五、實際開發中如何儘量避免死鎖發生

一般來講在實際開發中，很少會發生死鎖的情況，尤其是在業務併發量不是很大的情況下。在併發很大的情況下可能會存在偶爾產生死鎖。

不過呢，在自己實際開發中，有遇到過請求一個介面出現100%概率死鎖的情況。

當時的場景其實很簡單。一段業務程式碼中，有去走Dubbo調其它介面服務,這就存在了兩個事務,結果各自事務提交的時候，都需要等待對方的鎖釋放，就導致每次都發生死鎖超時。

這其實是一種程式碼不規範而導致死鎖的發生。這裡也總結下如何儘量避免死鎖發生。

1）不同的應用訪問同一組表時，應儘量約定以相同的順序訪問各表。對一個表而言，應儘量以固定的順序存取表中的行。這點真的很重要,它可以明顯的減少死鎖的發生。

舉例：好比有a,b兩張表，如果事務1先a後b,事務2先b後a,那就可能存在相互等待產生死鎖。那如果事務1和事務2都先a後b，那事務1先拿到a的鎖，事務2再去拿a的鎖，如果

鎖衝突那就會等待事務1釋放鎖，那自然事務2就不會拿到b的鎖，那就不會堵塞事務1拿到b的鎖，這樣就避免死鎖了。

2）在主鍵等值更新的時候，儘量先查詢看資料庫中有沒有滿足條件的資料，如果不存在就不用更新，存在才更新。為什麼要這麼做呢，因為如果去更新一條資料庫不存在的資料，

一樣會產生間隙鎖。

舉例：如果表中只有id=1和id=5的資料，那麼如果你更新id=3的sql，因為這條記錄表中不存在，那就會產生一個(1,5)的間隙鎖，但其實這個鎖就是多餘的，因為你去更新一個

資料都不存在的資料沒有任何意義。

3）儘量使用主鍵更新資料,因為主鍵是唯一索引，在等值查詢能查到資料的情況下只會產生行鎖，不會產生間隙鎖，這樣產生死鎖的概率就減少了。當然如果是範圍查詢，

一樣會產生間隙鎖。

4）避免長事務，小事務傳送鎖衝突的機率也小。這點應該很好理解。

5）在允許幻讀和不可重複度的情況下，儘量使用RC的隔離級別，避免gap lock造成的死鎖，因為產生死鎖經常都跟間隙鎖有關，間隙鎖的存在本身也是在RR隔離級別來

解決幻讀的一種措施。


感謝

這篇文章給自己提供了很好的思路，這篇文章也基本上按照這個思路往下寫的

手把手教你分析MySQL死鎖問題


宣告: 公眾號如需轉載該篇文章,發表文章的頭部一定要 告知是轉至公眾號: 後端元宇宙。同時也可以問本人要markdown原稿和原圖片。其它情況一律禁止轉載！

相關文章