大家好，我是小林。

昨天有位讀者在美團二面的時候，被問到關于幻讀的問題：

面試官反問的大概意思是，MySQL 記錄鎖+間隙鎖可以防止刪除操作而導致的幻讀嗎？

答案是可以的。

接下來，通過幾個小實驗來證明這個結論吧，順便再幫大家復習一下記錄鎖+間隙鎖。

什么是幻讀？

首先來看看 MySQL 文檔是怎么定義幻讀（Phantom Read）的:

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.

翻譯：當同一個查詢在不同的時間產生不同的結果集時，事務中就會出現所謂的幻象問題。

舉個例子，假設一個事務在 T1 時刻和 T2 時刻分別執行了下面查詢語句，途中沒有執行其他任何語句：

SELECT * FROM t_test WHERE id > 100;

只要 T1 和 T2 時刻執行產生的結果集是不相同的，那就發生了幻讀的問題，比如：

• T1 時間執行的結果是有 5 條行記錄，而 T2 時間執行的結果是有 6 條行記錄，那就發生了幻讀的問題。
• T1 時間執行的結果是有 5 條行記錄，而 T2 時間執行的結果是有 4 條行記錄，也是發生了幻讀的問題。

MySQL  是怎么解決幻讀的？

MySQL 可重復讀隔離級別是解決幻讀問題，查詢數據的操作有兩種方式，所以解決的方式是不同的：

• 針對快照讀（普通 select 語句），是通過 MVCC 方式解決了幻讀，因為可重復讀隔離級別下，事務執行過程中看到的數據，一直跟這個事務啟動時看到的數據是一致的，即使中途有其他事務插入了一條數據，是查詢不出來這條數據的，所以就很好了避免幻讀問題。
• 針對當前讀（select ... for update 等語句），是通過 next-key lock（記錄鎖+間隙鎖）方式解決了幻讀，因為當執行 select ... for update 語句的時候，會加上 next-key lock，如果有其他事務在 next-key lock 鎖范圍內插入了一條記錄，那么這個插入語句就會被阻塞，無法成功插入，所以就很好了避免幻讀問題。

實驗驗證

接下來，來驗證「 MySQL 記錄鎖+間隙鎖可以防止刪除操作而導致的幻讀問題」的結論。

實驗環境：MySQL 8.0 版本，可重復讀隔離級。

現在有一張用戶表（t_user），表里只有一個主鍵索引，表里有以下行數據：

現在有一個 A 事務執行了一條查詢語句，查詢到年齡大于 20 歲的用戶共有 6 條行記錄。

然后， B 事務執行了一條刪除 id = 2 的語句：

此時，B 事務的刪除語句就陷入了等待狀態，說明是無法進行刪除的。

因此，MySQL 記錄鎖+間隙鎖可以防止刪除操作而導致的幻讀問題。

加鎖分析

問題來了，A 事務在執行 select ... for update 語句時，具體加了什么鎖呢？

我們可以通過 select * from performance_schema.data_locks\G; 這條語句，查看事務執行 SQL 過程中加了什么鎖。

輸出的內容很多，共有 11 行信息，我刪減了一些不重要的信息：

從上面輸出的信息可以看到，共加了兩種不同粒度的鎖，分別是：

• 表鎖（LOCK_TYPE: TABLE）：X 類型的意向鎖；
• 行鎖（LOCK_TYPE: RECORD）：X 類型的 next-key 鎖；

這里我們重點關注「行鎖」，圖中 LOCK_TYPE? 中的 RECORD 表示行級鎖，而不是記錄鎖的意思：

• 如果 LOCK_MODE 為X，說明是 next-key 鎖；
• 如果 LOCK_MODE 為X, REC_NOT_GAP，說明是記錄鎖；
• 如果 LOCK_MODE 為X, GAP，說明是間隙鎖；

然后通過 LOCK_DATA 信息，可以確認 next-key 鎖的范圍，具體怎么確定呢？

• 根據我的經驗，如果 LOCK_MODE 是 next-key 鎖或者間隙鎖，那么LOCK_DATA 就表示鎖的范圍最右值，而鎖范圍的最左值為 LOCK_DATA 的上一條記錄的值。

因此，此時事務 A 在主鍵索引（INDEX_NAME : PRIMARY）上加了 10 個 next-key 鎖，如下：

• X 型的 next-key 鎖，范圍：(-∞, 1]
• X 型的 next-key 鎖，范圍：(1, 2]
• X 型的 next-key 鎖，范圍：(2, 3]
• X 型的 next-key 鎖，范圍：(3, 4]
• X 型的 next-key 鎖，范圍：(4, 5]
• X 型的 next-key 鎖，范圍：(5, 6]
• X 型的 next-key 鎖，范圍：(6, 7]
• X 型的 next-key 鎖，范圍：(7, 8]
• X 型的 next-key 鎖，范圍：(8, 9]
• X 型的 next-key 鎖，范圍：(9, +∞]

這相當于把整個表給鎖住了，其他事務在對該表進行增、刪、改操作的時候都會被阻塞。

只有在事務 A 提交了事務，事務 A 執行過程中產生的鎖才會被釋放。

為什么只是查詢年齡 20 歲以上行記錄，而把整個表給鎖住了呢？

這是因為事務 A 的這條查詢語句是全表掃描，鎖是在遍歷索引的時候加上的，并不是針對輸出的結果加鎖。

因此，在線上在執行 update、delete、select ... for update 等具有加鎖性質的語句，一定要檢查語句是否走了索引，如果是全表掃描的話，會對每一個索引加 next-key 鎖，相當于把整個表鎖住了，這是挺嚴重的問題。

如果對 age 建立索引，事務 A 這條查詢會加什么鎖呢？

接下來，我對 age 字段建立索引，然后再執行這條查詢語句：

接下來，繼續通過 select * from performance_schema.data_locks\G; 這條語句，查看事務執行 SQL 過程中加了什么鎖。

具體的信息，我就不打印了，我直接說結論吧。

因為表中有兩個索引，分別是主鍵索引和 age 索引，所以會分別對這兩個索引加鎖。

主鍵索引會加如下的鎖：

• X 型的記錄鎖，鎖住 id = 2 的記錄；
• X 型的記錄鎖，鎖住 id = 3 的記錄；
• X 型的記錄鎖，鎖住 id = 5 的記錄；
• X 型的記錄鎖，鎖住 id = 6 的記錄；
• X 型的記錄鎖，鎖住 id = 7 的記錄；
• X 型的記錄鎖，鎖住 id = 8 的記錄；

分析 age 索引加鎖的范圍時，要先對 age 字段進行排序。

age 索引加的鎖：

• X 型的 next-key lock，鎖住 age 范圍 (19, 21] 的記錄；
• X 型的 next-key lock，鎖住 age 范圍 (21, 21] 的記錄；
• X 型的 next-key lock，鎖住 age 范圍 (21, 23] 的記錄；
• X 型的 next-key lock，鎖住 age 范圍 (23, 23] 的記錄；
• X 型的 next-key lock，鎖住 age 范圍 (23, 39] 的記錄；
• X 型的 next-key lock，鎖住 age 范圍 (39, 43] 的記錄；
• X 型的 next-key lock，鎖住 age 范圍 (43, +∞] 的記錄；

化簡一下，age 索引  next-key 鎖的范圍是 (19, +∞]。

可以看到，對 age 字段建立了索引后，查詢語句是索引查詢，并不會全表掃描，因此不會把整張表給鎖住。

總結一下，在對 age 字段建立索引后，事務 A 在執行下面這條查詢語句后，主鍵索引和 age 索引會加下圖中的鎖。

事務 A 加上鎖后，事務 B、C、D、E 在執行以下語句都會被阻塞。

總結

在 MySQL 的可重復讀隔離級別下，針對「當前讀」的查詢語句會對索引加記錄鎖+間隙鎖，這樣可以避免其他事務執行「增、刪、改」時導致幻讀的現象。

有一點要注意的是，在執行 update、delete、select ... for update 等具有加鎖性質的語句，一定要檢查語句是否走了索引，如果是全表掃描的話，會對每一個索引加 next-key 鎖，相當于把整個表鎖住了，這是挺嚴重的問題。

這次教了大家如何分析事務具體加了什么鎖，以后大家可以多做實驗，然后自己嘗試分析分析，掌握分析的方法，遠比記住加鎖規則強！?