【網易雲商】記一次實遇的 MySQL--index merge 死鎖歷程

網易智企發表於2023-03-28
MySqlIndex

在實際業務開發過程中，MySQL 會由於許多原因造成死鎖，本文就是基於網易雲商的一次實遇死鎖經驗，講述了一次由於 Index Merge 最佳化導致的死鎖案例，並分享處理本次死鎖所運用的方法。死鎖的本質原因還是由加鎖導致，因此建立索引時要結合實際業務場景分析。

背景

某一天中午，我們收到了報警資訊，出現關鍵詞 Deadlock。


nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

顯然出現了資料庫死鎖。一開始我們以為是資料庫操作順序不一致引發的，但是經分析相關業務和 SQL 後並未發現不一致的場景，場面一度陷入僵局，沒辦法只能尋根究底從 MySQL 的索引和加鎖機制入手了。

先來看下 MySQL 的加鎖機制。

MySQL 的加鎖機制

MySQL 索引分為主鍵索引（或聚簇索引）和二級索引（或非主鍵索引、非聚簇索引，包括各種主鍵索引外的其他索引）。不同儲存引擎對於資料的組織方式略有不同，本文以 InnoDB 為例展開，MySQL 版本 5.7。

主鍵索引和資料是存放在一起的，構成一棵 B+ 樹，主鍵位於非葉子節點，資料存放於葉子節點。示意圖如下：


圖1：圖源網路

二級索引列位於非葉子節點，主鍵值位於葉子節點，示意圖如下：


圖2：圖源網路

以 select * from table where name='ccc' 為例，MySQL 對 SQL 進行解析後發現 name 欄位有索引可用，於是先在二級索引（圖2）上根據 name='ccc' 找到主鍵 id=27，然後根據主鍵 27 到主鍵索引上（圖1）上找到需要的記錄。這個過程也被稱作回表。

接下來言歸正傳看下 MySQL 到底是如何給索引加鎖的（本例中隔離級別為 RC）。具體分以下 3 個場景分析：

* 根據主鍵進行更新

update table set name='cjy' where id=27；只需要將主鍵上 id=27 的記錄加上互斥鎖即可（加鎖後本事務可以讀和寫，其他事務讀和寫會被阻塞）。如下：

  • 根據唯一索引進行更新

update table set name='cjy' where name='ccc'，InnoDB 現在唯一索引 name 上找到 name='ccc' 的索引項（ccc,27）並加上加上互斥鎖，然後根據 id=27 再到主鍵索引上找到對應的葉子節點並加上互斥鎖。

一共兩把鎖，一把加在唯一索引上，一把加在主鍵索引上。這裡需要說明的是加鎖是一步步加的，這種分步加鎖的機制就是後文死鎖的導火索。示意圖如下：

  • 根據非唯一索引進行更新
    update table set name='cjy' where name='ccc'。如果 name 不唯一，和上面唯一索引加鎖相似，不同的是會給所有符合條件的索引加鎖。如下：

這裡有四把鎖，加鎖步驟如下：

  • 在非唯一索引（name）上找到（ccc,27）的索引項，加上互斥鎖；
  • 根據（ccc,27）找到主鍵索引的（27,ccc）記錄，加互斥鎖；
  • 在非唯一索引（name）上找到（ccc,29）的索引項，加上互斥鎖；
  • 根據（ccc,29）找到主鍵索引的（29,ccc）記錄，加互斥鎖；

從上面步驟可以看出，InnoDB 對於每個符合條件的記錄是分步逐條加鎖的。

再來看下 MySQL 的事務機制。

MySQL 事務機制

事務的四個特性 ACID

原子性（Atomicity）：指事務是一個不可分割的最小工作單位，事務中的操作只有都發生和都不發生兩種情況。

一致性（Consistency）：事務必須使資料庫從一個一致狀態變換到另外一個一致狀態，舉個例子，李二給王五轉賬 50 元，其事務就是讓李二賬戶上減去 50 元，王五賬戶上加上 50 元；一致性是指其他事務看到的情況是要麼李二還沒有給王五轉賬的狀態，要麼王五已經成功接收到李二的 50 元轉賬。而對於李二少了 50 元，王五還沒加上 50 元這個中間狀態是不可見的。

隔離性（Isolation）：一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的資料對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。

永續性（Durability）：一個事務一旦提交成功，它對資料庫中資料的改變將是永久性的，接下來的其他操作或故障不應對其有任何影響。

事務的隔離等級

MySQL 中的四種事務隔離級別分別如下：

  • read uncommitted（讀未提交資料）：允許事務讀取未被其他事務提交的變更。（髒讀、不可重複讀和幻讀的問題都會出現）。
  • read committed（讀已提交資料）：只允許事務讀取已經被其他事務提交的變更。（可以避免髒讀，但不可重複讀和幻讀的問題仍然可能出現）。
  • repeatable read（可重複讀）：確保事務可以多次從一個欄位中讀取相同的值，在這個事務持續期間，禁止其他事務對這個欄位進行更新（update）。（可以避免髒讀和不可重複讀，但幻讀仍然存在）。
  • serializable（序列化）：確保事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其他事務對該表執行插入、更新和刪除操作，所有併發問題都可避免，但效能十分低下。

其中 RR 實現可重複讀的原理是：MVC 多版本併發控制。其實就是版本號概念。讀取資料時大於當前事務版本號的記錄不管。

同時 RR 中會使用到間隙鎖。間隙鎖是採用在指定記錄的前面和後面以及中間的間隙上加間隙鎖的方式避免資料被插入，從而解決幻讀的問題。

死鎖原因分析

基於上面講過的知識，下面就來看看本次死鎖的具體原因是什麼。

本次死鎖發生的場景是給一批客戶手機號發簡訊後更新記錄時觸發的。廢話不多說，先上表結構：

（基於資料敏感和方便理解考慮，去除了和本次死鎖無關的欄位）。

CREATE TABLE `phone_send_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '傳送批次id',
  `phone` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手機號',
  `status` smallint(4) NOT NULL DEFAULT '0' COMMENT '傳送狀態 0未傳送 1傳送成功 2 傳送失敗',
  PRIMARY KEY (`id`),
  KEY `idx_taskId` (`task_id`) USING BTREE,
  KEY `idx_phone` (`phone`) USING BTREE
) ENGINE = InnoDB  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '簡訊傳送記錄表'

業務場景就是：建立一個簡訊傳送任務（task_id），一個任務裡包含了一批手機號，因此資料庫簡訊傳送記錄表中會有 n 行記錄：phone 不同，task_id 相同。更新 SQL 如下：

update phone_send_record set status = #{status} where phone = #{phone} and task_id = #{taskId}

心路歷程

我們業務上做了手機號去重，因此理論上同一個傳送任務裡是不會存在相同手機號的，同時發生死鎖的2行記錄顯示的也是不同的手機號（見下文 sql）。

按道理講，innoDB 觸發行鎖，沒有鎖表，不同行不應該影響才對，就算鎖多行那也是資源競爭等待不應該觸發死鎖。我們帶著疑問繼續往下看。

接著我們考慮了間隙鎖的可能，但是間隙鎖是在 RR 隔離級別下才存在，查了下我們資料庫的隔離級別是 RC，所以間隙鎖的可能也被排除了。

接著檢視了 SQL 執行計劃，發現使用了 index_merge。index_merge 是 MySQL 5.1 後引入的一項索引合併最佳化技術，它允許對同一個表同時使用多個索引進行查詢，並對多個索引的查詢結果進行合併後返回。

回到我們的 SQL，經過分析日誌找出發生死鎖的是如下 2 條：

update phone_send_record set  status = 0 where phone = '13555111111' and task_id = 123;
update phone_send_record set  status = 0 where phone = '13555222222' and task_id = 123;

如果沒有 index_merge，要麼走 idx_taskId 索引，要麼走 idx_phone 索引，不會出現兩個索引一起使用的情況。而在使用 index_merge 技術後，會同時執行兩個索引，分別查到結果後再進行合併。再結合上文對加鎖機制的理解，兩個索引的同時加鎖就可能導致死鎖。圖析如下：

上圖只是其中一種造成死鎖的可能路徑，事實上還有其他可能（大家可以自行發揮想象）。

下表是對上圖做的簡要解析：

 事務 1 等待事務 2 釋放鎖，事務 2 等待事務 1 釋放鎖，這樣就造成了死鎖。

解決方法

  1. 既然是 index_merge 導致的，那理所當然的想就是關閉 index_merge。的確這能解決上述的死鎖問題，但是一般情況下，資料量越大 index_merge 開啟的最佳化效果會越明顯，所以沒有特別要求不建議關閉（預設是開啟的）。因此此方法不推薦。
  2. 使用主鍵索引進行更新。先根據二級索引查詢出主鍵 id，再拿主鍵 id 進行 update。這樣更新的範圍是唯一的，自然不會觸發死鎖。
  3. 刪除多餘的獨立索引，建立聯合索引。本慄就是 KEY idx_t_p （task_id，phone）。這個就提醒我們在加索引欄位時（尤其是往舊錶加）需要仔細分析下業務場景，來決定是建立獨立索引還是聯合索引。

小結：方法 2 是從程式碼層面入手從根本上規避死鎖，但是會增加一定的程式碼量；方法 3 是巧用聯合索引實現 2 個獨立索引的功能又不會觸發 index_merge。推薦方法 2，但方法 3 更方便些，原則上方法 2 和 3 都有效。

總結

本文僅描述了由於 index_merge 最佳化導致的死鎖，講述了死鎖產生的原因以及解決辦法，並順便介紹了 MySQL 加鎖機制。事實上死鎖的原因還有很多，比如運算元據順序不一致、長事務等，就不一一展開了。

相關文章