大家好，我是小林。

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

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

答案是可以的。

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

什么是幻讀？

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

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

翻譯：當(dāng)同一個查詢在不同的時間產(chǎn)生不同的結(jié)果集時，事務(wù)中就會出現(xiàn)所謂的幻象問題。

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

SELECT * FROM t_test WHERE id > 100;

只要 T1 和 T2 時刻執(zhí)行產(chǎn)生的結(jié)果集是不相同的，那就發(fā)生了幻讀的問題，比如：

• T1 時間執(zhí)行的結(jié)果是有 5 條行記錄，而 T2 時間執(zhí)行的結(jié)果是有 6 條行記錄，那就發(fā)生了幻讀的問題。
• T1 時間執(zhí)行的結(jié)果是有 5 條行記錄，而 T2 時間執(zhí)行的結(jié)果是有 4 條行記錄，也是發(fā)生了幻讀的問題。

MySQL  是怎么解決幻讀的？

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

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

實驗驗證

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

實驗環(huán)境：MySQL 8.0 版本，可重復(fù)讀隔離級。

現(xiàn)在有一張用戶表（t_user），表里只有一個主鍵索引，表里有以下行數(shù)據(jù)：

現(xiàn)在有一個 A 事務(wù)執(zhí)行了一條查詢語句，查詢到年齡大于 20 歲的用戶共有 6 條行記錄。

然后， B 事務(wù)執(zhí)行了一條刪除 id = 2 的語句：

此時，B 事務(wù)的刪除語句就陷入了等待狀態(tài)，說明是無法進行刪除的。

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

加鎖分析

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

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

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

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

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

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

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

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

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

因此，此時事務(wù) 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, +∞]

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

只有在事務(wù) A 提交了事務(wù)，事務(wù) A 執(zhí)行過程中產(chǎn)生的鎖才會被釋放。

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

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

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

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

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

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

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

因為表中有兩個索引，分別是主鍵索引和 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 字段建立了索引后，查詢語句是索引查詢，并不會全表掃描，因此不會把整張表給鎖住。

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

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

總結(jié)

在 MySQL 的可重復(fù)讀隔離級別下，針對「當(dāng)前讀」的查詢語句會對索引加記錄鎖+間隙鎖，這樣可以避免其他事務(wù)執(zhí)行「增、刪、改」時導(dǎo)致幻讀的現(xiàn)象。

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

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