大家好，我是小林。

之前有位小伙伴美團(tuán)三面，一直被追求「幻讀是否被 MySQL 可重復(fù)度隔離級(jí)別徹底解決了？」

之前我也提到過(guò)，MySQL InnoDB 引擎的默認(rèn)隔離級(jí)別雖然是「可重復(fù)讀」，但是它很大程度上避免幻讀現(xiàn)象（并不是完全解決了），解決的方案有兩種：

針對(duì)快照讀（普通 select 語(yǔ)句），是通過(guò) MVCC 方式解決了幻讀，因?yàn)榭芍貜?fù)讀隔離級(jí)別下，事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)，一直跟這個(gè)事務(wù)啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的，即使中途有其他事務(wù)插入了一條數(shù)據(jù)，是查詢(xún)不出來(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)題。

這次，我會(huì)舉例兩個(gè)實(shí)驗(yàn)場(chǎng)景來(lái)說(shuō)明 MySQL InnoDB 引擎的可重復(fù)讀隔離級(jí)別發(fā)生幻讀的問(wèn)題。

好了，發(fā)車(chē)！

什么是幻讀？

首先來(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. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

翻譯：當(dāng)同一個(gè)查詢(xún)?cè)诓煌臅r(shí)間產(chǎn)生不同的結(jié)果集時(shí)，事務(wù)中就會(huì)出現(xiàn)所謂的幻象問(wèn)題。例如，如果 SELECT 執(zhí)行了兩次，但第二次返回了第一次沒(méi)有返回的行，則該行是“幻像”行。

舉個(gè)例子，假設(shè)一個(gè)事務(wù)在 T1 時(shí)刻和 T2 時(shí)刻分別執(zhí)行了下面查詢(xún)語(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)題。

隔離級(jí)別

當(dāng)多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)可能會(huì)遇到「臟讀、不可重復(fù)讀、幻讀」的現(xiàn)象，這些現(xiàn)象會(huì)對(duì)事務(wù)的一致性產(chǎn)生不同程序的影響。

臟讀：讀到其他事務(wù)未提交的數(shù)據(jù)；

不可重復(fù)讀：前后讀取的數(shù)據(jù)不一致；

幻讀：前后讀取的記錄數(shù)量不一致。

這三個(gè)現(xiàn)象的嚴(yán)重性排序如下：

圖片

SQL 標(biāo)準(zhǔn)提出了四種隔離級(jí)別來(lái)規(guī)避這些現(xiàn)象，隔離級(jí)別越高，性能效率就越低，這四個(gè)隔離級(jí)別如下：

讀未提交（read uncommitted），指一個(gè)事務(wù)還沒(méi)提交時(shí)，它做的變更就能被其他事務(wù)看到；

讀提交（read committed），指一個(gè)事務(wù)提交之后，它做的變更才能被其他事務(wù)看到；

可重復(fù)讀（repeatable read），指一個(gè)事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)，一直跟這個(gè)事務(wù)啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的，MySQL InnoDB 引擎的默認(rèn)隔離級(jí)別；

串行化（serializable ）；會(huì)對(duì)記錄加上讀寫(xiě)鎖，在多個(gè)事務(wù)對(duì)這條記錄進(jìn)行讀寫(xiě)操作時(shí)，如果發(fā)生了讀寫(xiě)沖突的時(shí)候，后訪問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行；

針對(duì)不同的隔離級(jí)別，并發(fā)事務(wù)時(shí)可能發(fā)生的現(xiàn)象也會(huì)不同。

也就是說(shuō)：

在「讀未提交」隔離級(jí)別下，可能發(fā)生臟讀、不可重復(fù)讀和幻讀現(xiàn)象；

在「讀提交」隔離級(jí)別下，可能發(fā)生不可重復(fù)讀和幻讀現(xiàn)象，但是不可能發(fā)生臟讀現(xiàn)象；

在「可重復(fù)讀」隔離級(jí)別下，可能發(fā)生幻讀現(xiàn)象，但是不可能臟讀和不可重復(fù)讀現(xiàn)象；

在「串行化」隔離級(jí)別下，臟讀、不可重復(fù)讀和幻讀現(xiàn)象都不可能會(huì)發(fā)生。

所以，要解決臟讀現(xiàn)象，就要升級(jí)到「讀提交」以上的隔離級(jí)別；要解決不可重復(fù)讀現(xiàn)象，就要升級(jí)到「可重復(fù)讀」的隔離級(jí)別，要解決幻讀現(xiàn)象不建議將隔離級(jí)別升級(jí)到「串行化」。

不同的數(shù)據(jù)庫(kù)廠商對(duì) SQL 標(biāo)準(zhǔn)中規(guī)定的 4 種隔離級(jí)別的支持不一樣，有的數(shù)據(jù)庫(kù)只實(shí)現(xiàn)了其中幾種隔離級(jí)別，我們討論的 MySQL 雖然支持 4 種隔離級(jí)別，但是與SQL 標(biāo)準(zhǔn)中規(guī)定的各級(jí)隔離級(jí)別允許發(fā)生的現(xiàn)象卻有些出入。

MySQL 在「可重復(fù)讀」隔離級(jí)別下，可以很大程度上避免幻讀現(xiàn)象的發(fā)生（注意是很大程度避免，并不是徹底避免），所以 MySQL 并不會(huì)使用「串行化」隔離級(jí)別來(lái)避免幻讀現(xiàn)象的發(fā)生，因?yàn)槭褂谩复谢垢綦x級(jí)別會(huì)影響性能。

MySQL InnoDB 引擎的默認(rèn)隔離級(jí)別雖然是「可重復(fù)讀」，但是它很大程度上避免幻讀現(xiàn)象（并不是完全解決了），解決的方案有兩種：

針對(duì)快照讀（普通 select 語(yǔ)句），是通過(guò) MVCC 方式解決了幻讀，因?yàn)榭芍貜?fù)讀隔離級(jí)別下，事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)，一直跟這個(gè)事務(wù)啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的，即使中途有其他事務(wù)插入了一條數(shù)據(jù)，是查詢(xún)不出來(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)題。

快照讀是如何避免幻讀的？

可重復(fù)讀隔離級(jí)是由 MVCC（多版本并發(fā)控制）實(shí)現(xiàn)的，實(shí)現(xiàn)的方式是啟動(dòng)事務(wù)后，在執(zhí)行第一個(gè)查詢(xún)語(yǔ)句后，會(huì)創(chuàng)建一個(gè) Read View，后續(xù)的查詢(xún)語(yǔ)句利用這個(gè) Read View，通過(guò)這個(gè) ?Read View 就可以在 undo log 版本鏈找到事務(wù)開(kāi)始時(shí)的數(shù)據(jù)，所以事務(wù)過(guò)程中每次查詢(xún)的數(shù)據(jù)都是一樣的，即使中途有其他事務(wù)插入了新紀(jì)錄，是查詢(xún)不出來(lái)這條數(shù)據(jù)的，所以就很好了避免幻讀問(wèn)題。

做個(gè)實(shí)驗(yàn)，數(shù)據(jù)庫(kù)表 t_stu 如下，其中 id 為主鍵。

然后在可重復(fù)讀隔離級(jí)別下，有兩個(gè)事務(wù)的執(zhí)行順序如下：

從這個(gè)實(shí)驗(yàn)結(jié)果可以看到，即使事務(wù) B 中途插入了一條記錄，事務(wù) A 前后兩次查詢(xún)的結(jié)果集都是一樣的，并沒(méi)有出現(xiàn)所謂的幻讀現(xiàn)象。

當(dāng)前讀是如何避免幻讀的？

MySQL 里除了普通查詢(xún)是快照讀，其他都是當(dāng)前讀，比如 update、insert、delete，這些語(yǔ)句執(zhí)行前都會(huì)查詢(xún)最新版本的數(shù)據(jù)，然后再做進(jìn)一步的操作。

這很好理解，假設(shè)你要 update 一個(gè)記錄，另一個(gè)事務(wù)已經(jīng) delete 這條記錄并且提交事務(wù)了，這樣不是會(huì)產(chǎn)生沖突嗎，所以 update 的時(shí)候肯定要知道最新的數(shù)據(jù)。

另外，select ... for update 這種查詢(xún)語(yǔ)句是當(dāng)前讀，每次執(zhí)行的時(shí)候都是讀取最新的數(shù)據(jù)。

接下來(lái)，我們假設(shè)select ... for update當(dāng)前讀是不會(huì)加鎖的（實(shí)際上是會(huì)加鎖的），在做一遍實(shí)驗(yàn)。

這時(shí)候，事務(wù) B 插入的記錄，就會(huì)被事務(wù) A 的第二條查詢(xún)語(yǔ)句查詢(xún)到（因?yàn)槭钱?dāng)前讀），這樣就會(huì)出現(xiàn)前后兩次查詢(xún)的結(jié)果集合不一樣，這就出現(xiàn)了幻讀。

所以，Innodb 引擎為了解決「可重復(fù)讀」隔離級(jí)別使用「當(dāng)前讀」而造成的幻讀問(wèn)題，就引出了間隙鎖。（額外提一句，讀提交隔離級(jí)別，是沒(méi)有間隙鎖的，只有記錄鎖）

假設(shè)，表中有一個(gè)范圍 id 為（3，5）間隙鎖，那么其他事務(wù)就無(wú)法插入 id = 4 這條記錄了，這樣就有效的防止幻讀現(xiàn)象的發(fā)生。

舉個(gè)具體例子，場(chǎng)景如下：

事務(wù) A 執(zhí)行了這條當(dāng)前讀語(yǔ)句后，就在對(duì)表中的記錄加上 id 范圍為 (2, +∞] 的 next-key lock（next-key lock 是間隙鎖+記錄鎖的組合）。

然后，事務(wù) B 在執(zhí)行插入語(yǔ)句的時(shí)候，判斷到插入的位置被事務(wù) A 加了 ?next-key lock，于是事物 B 會(huì)生成一個(gè)插入意向鎖，同時(shí)進(jìn)入等待狀態(tài)，直到事務(wù) A 提交了事務(wù)。這就避免了由于事務(wù) B 插入新記錄而導(dǎo)致事務(wù) A 發(fā)生幻讀的現(xiàn)象。

幻讀被徹底解決了嗎？

可重復(fù)讀隔離級(jí)別下雖然很大程度上避免了幻讀，但是還是沒(méi)有能完全解決幻讀。

我舉例兩個(gè)可重復(fù)讀隔離級(jí)別發(fā)生幻讀現(xiàn)象的場(chǎng)景。

第一個(gè)發(fā)生幻讀現(xiàn)象的場(chǎng)景

還是以這張表作為例子：

事務(wù) A 執(zhí)行查詢(xún) id = 5 的記錄，此時(shí)表中是沒(méi)有該記錄的，所以查詢(xún)不出來(lái)。

#?事務(wù)?A
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)

mysql>?select?*?from?t_stu?where?id?=?5;
Empty?set?(0.01?sec)

然后事務(wù) B 插入一條 id = 5 的記錄，并且提交了事務(wù)。

#?事務(wù)?B
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)

mysql>?insert?into?t_stu?values(5,?'小美',?18);
Query?OK,?1?row?affected?(0.00?sec)

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

此時(shí)，事務(wù) A ?更新 id = 5 這條記錄，對(duì)沒(méi)錯(cuò)，事務(wù) A 看不到 id = 5 這條記錄，但是他去更新了這條記錄，這場(chǎng)景確實(shí)很違和，然后再次查詢(xún) id = 5 的記錄，事務(wù) A 就能看到事務(wù) B 插入的紀(jì)錄了，幻讀就是發(fā)生在這種違和的場(chǎng)景。

#?事務(wù)?A
mysql>?update?t_stu?set?name?=?'小林coding'?where?id?=?5;
Query?OK,?1?row?affected?(0.01?sec)
Rows?matched:?1??Changed:?1??Warnings:?0

mysql>?select?*?from?t_stu?where?id?=?5;
+----+--------------+------+
|?id?|?name?????????|?age??|
+----+--------------+------+
|??5?|?小林coding???|???18?|
+----+--------------+------+
1?row?in?set?(0.00?sec)

?

?

整個(gè)發(fā)生幻讀的時(shí)序圖如下：

在可重復(fù)讀隔離級(jí)別下，事務(wù) A 第一次執(zhí)行普通的 select 語(yǔ)句時(shí)生成了一個(gè) ReadView，之后事務(wù) B 向表中新插入了一條 id = 5 的記錄并提交。接著，事務(wù) A 對(duì) id = 5 這條記錄進(jìn)行了更新操作，在這個(gè)時(shí)刻，這條新記錄的 trx_id 隱藏列的值就變成了事務(wù) A 的事務(wù) id，之后事務(wù) A ?再使用普通 select 語(yǔ)句去查詢(xún)這條記錄時(shí)就可以看到這條記錄了，于是就發(fā)生了幻讀。

因?yàn)檫@種特殊現(xiàn)象的存在，所以我們認(rèn)為 MySQL Innodb 中的 MVCC 并不能完全避免幻讀現(xiàn)象。

第二個(gè)發(fā)生幻讀現(xiàn)象的場(chǎng)景

除了上面這一種場(chǎng)景會(huì)發(fā)生幻讀現(xiàn)象之外，還有下面這個(gè)場(chǎng)景也會(huì)發(fā)生幻讀現(xiàn)象。

T1 時(shí)刻：事務(wù) A 先執(zhí)行「快照讀語(yǔ)句」：select * from t_test where id > 100 得到了 3 條記錄。

T2 時(shí)刻：事務(wù) B 往插入一個(gè) id= 200 的記錄并提交；

T3 時(shí)刻：事務(wù) A 再執(zhí)行「當(dāng)前讀語(yǔ)句」 select * from t_test where id > 100 for update 就會(huì)得到 4 條記錄，此時(shí)也發(fā)生了幻讀現(xiàn)象。

要避免這類(lèi)特殊場(chǎng)景下發(fā)生幻讀的現(xiàn)象的話(huà)，就是盡量在開(kāi)啟事務(wù)之后，馬上執(zhí)行 select ... for update 這類(lèi)當(dāng)前讀的語(yǔ)句，因?yàn)樗鼤?huì)對(duì)記錄加 next-key lock，從而避免其他事務(wù)插入一條新記錄。

小結(jié)

MySQL InnoDB 引擎的可重復(fù)讀隔離級(jí)別（默認(rèn)隔離級(jí)），根據(jù)不同的查詢(xún)方式，分別提出了避免幻讀的方案：

針對(duì)快照讀（普通 select 語(yǔ)句），是通過(guò) MVCC 方式解決了幻讀。

針對(duì)當(dāng)前讀（select ... for update 等語(yǔ)句），是通過(guò) next-key lock（記錄鎖+間隙鎖）方式解決了幻讀。

我舉例了兩個(gè)發(fā)生幻讀場(chǎng)景的例子。

第一個(gè)例子：對(duì)于快照讀， MVCC 并不能完全避免幻讀現(xiàn)象。因?yàn)楫?dāng)事務(wù) A 更新了一條事務(wù) B 插入的記錄，那么事務(wù) A 前后兩次查詢(xún)的記錄條目就不一樣了，所以就發(fā)生幻讀。

第二個(gè)例子：對(duì)于當(dāng)前讀，如果事務(wù)開(kāi)啟后，并沒(méi)有執(zhí)行當(dāng)前讀，而是先快照讀，然后這期間如果其他事務(wù)插入了一條記錄，那么事務(wù)后續(xù)使用當(dāng)前讀進(jìn)行查詢(xún)的時(shí)候，就會(huì)發(fā)現(xiàn)兩次查詢(xún)的記錄條目就不一樣了，所以就發(fā)生幻讀。

所以，MySQL 可重復(fù)讀隔離級(jí)別并沒(méi)有徹底解決幻讀，只是很大程度上避免了幻讀現(xiàn)象的發(fā)生。

要避免這類(lèi)特殊場(chǎng)景下發(fā)生幻讀的現(xiàn)象的話(huà)，就是盡量在開(kāi)啟事務(wù)之后，馬上執(zhí)行 select ... for update 這類(lèi)當(dāng)前讀的語(yǔ)句，因?yàn)樗鼤?huì)對(duì)記錄加 next-key lock，從而避免其他事務(wù)插入一條新記錄。

編輯：黃飛

?