大家好，我是小林。

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

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

答案是可以的。

接下來(lái)，通過(guò)幾個(gè)小實(shí)驗(yàn)來(lái)證明這個(gè)結(jié)論吧，順便再幫大家復(fù)習(xí)一下記錄鎖+間隙鎖。

什么是幻讀？

首先來(lái)看看 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)同一個(gè)查詢?cè)诓煌臅r(shí)間產(chǎn)生不同的結(jié)果集時(shí)，事務(wù)中就會(huì)出現(xiàn)所謂的幻象問(wèn)題。

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

SELECT * FROM t_test WHERE id > 100;

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

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

MySQL  是怎么解決幻讀的？

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

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

實(shí)驗(yàn)驗(yàn)證

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

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

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

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

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

此時(shí)，B 事務(wù)的刪除語(yǔ)句就陷入了等待狀態(tài)，說(shuō)明是無(wú)法進(jìn)行刪除的。

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

加鎖分析

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

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

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

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

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

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

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

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

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

因此，此時(shí)事務(wù) A 在主鍵索引（INDEX_NAME : PRIMARY）上加了 10 個(gè) 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)于把整個(gè)表給鎖住了，其他事務(wù)在對(duì)該表進(jìn)行增、刪、改操作的時(shí)候都會(huì)被阻塞。

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

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

這是因?yàn)槭聞?wù) A 的這條查詢語(yǔ)句是全表掃描，鎖是在遍歷索引的時(shí)候加上的，并不是針對(duì)輸出的結(jié)果加鎖。

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

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

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

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

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

因?yàn)楸碇杏袃蓚€(gè)索引，分別是主鍵索引和 age 索引，所以會(huì)分別對(duì)這兩個(gè)索引加鎖。

主鍵索引會(huì)加如下的鎖：

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

分析 age 索引加鎖的范圍時(shí)，要先對(duì) age 字段進(jìn)行排序。

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, +∞] 的記錄；

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

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

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

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

總結(jié)

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

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

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