MySQL 幻讀的詳解、實(shí)例及解決辦法

昨天的文章，有一個(gè)很有意思的評(píng)論，今天我們?cè)谶@里討論一下。

那么問(wèn)題來(lái)了，MySQL 的 RR 隔離級(jí)別下，到底有沒(méi)有徹底解決幻讀問(wèn)題？

?

臟讀/不可重復(fù)讀的概念都比較容易理解和掌握，這里不在討論。

?

事務(wù)隔離級(jí)別（tx_isolation）

mysql 有四級(jí)事務(wù)隔離級(jí)別 每個(gè)級(jí)別都有字符或數(shù)字編號(hào)

我們可以通過(guò)以下命令 查看/設(shè)置 全局/會(huì)話 的事務(wù)隔離級(jí)別

mysql>?SELECT?@@global.tx_isolation,?@@tx_isolation;
+-----------------------+------------------+
|?@@global.tx_isolation?|?@@tx_isolation???|
+-----------------------+------------------+
|?REPEATABLE-READ???????|?READ-UNCOMMITTED?|
+-----------------------+------------------+
1?row?in?set?(0.00?sec)

#?設(shè)定全局的隔離級(jí)別?設(shè)定會(huì)話?global?替換為?session?即可?把set語(yǔ)法溫習(xí)一下
#?SET?[GLOABL]?config_name?=?'foobar';
#?SET?@@[session.|global.]config_name?=?'foobar';
#?SELECT?@@[global.]config_name;

SET?@@gloabl.tx_isolation?=?0;
SET?@@gloabl.tx_isolation?=?'READ-UNCOMMITTED';

SET?@@gloabl.tx_isolation?=?1;
SET?@@gloabl.tx_isolation?=?'READ-COMMITTED';

SET?@@gloabl.tx_isolation?=?2;
SET?@@gloabl.tx_isolation?=?'REPEATABLE-READ';

SET?@@gloabl.tx_isolation?=?3;
SET?@@gloabl.tx_isolation?=?'SERIALIZABLE';

幻讀

首先我們要搞明白何謂幻讀，目前網(wǎng)上的眾多解釋幻讀的博文個(gè)人感覺(jué)仔細(xì)設(shè)想一下就能找出推翻的例子，就像博文把 非阻塞IO 等同為 異步IO，然后好多文章都紛紛借用，其實(shí)這倆貨是完全不同，非阻塞IO 是 同步IO 中的一種模式，并非 異步IO。錯(cuò)誤的觀點(diǎn)都被大眾認(rèn)同的 "正確化" 了，扯遠(yuǎn)了，回歸主題。

幻讀會(huì)在 RU / RC / RR 級(jí)別下出現(xiàn)，SERIALIZABLE 則杜絕了 幻讀，但 RU / RC 下還會(huì)存在臟讀、不可重復(fù)讀，故我們就以 RR 級(jí)別來(lái)研究 幻讀，排除其他干擾。

注意：RR 級(jí)別下存在幻讀的可能，但也是可以使用對(duì)記錄手動(dòng)加 X鎖 的方法消除幻讀。SERIALIZABLE 正是對(duì)所有事務(wù)都加 X鎖 才杜絕了 幻讀，但很多場(chǎng)景下我們的業(yè)務(wù) sql 并不會(huì)存在 幻讀 的風(fēng)險(xiǎn)。SERIALIZABLE 的一刀切雖然事務(wù)絕對(duì)安全，但性能會(huì)有很多不必要的損失。故可以在 RR 下根據(jù)業(yè)務(wù)需求決定是否加鎖，存在幻讀風(fēng)險(xiǎn)我們加鎖，不存在就不加鎖，事務(wù)安全與性能兼?zhèn)?，這也是 RR 作為 mysql 默認(rèn)隔是個(gè)事務(wù)離級(jí)別的原因，所以需要正確的理解 幻讀。

?

幻讀錯(cuò)誤的理解：說(shuō)幻讀是 事務(wù)A 執(zhí)行兩次 select 操作得到不同的數(shù)據(jù)集，即 select 1 得到 10 條記錄，select 2 得到 11 條記錄。這其實(shí)并不是幻讀，這是不可重復(fù)讀的一種，只會(huì)在 R-U R-C 級(jí)別下出現(xiàn)，而在 mysql 默認(rèn)的 RR 隔離級(jí)別是不會(huì)出現(xiàn)的。

?

這里給出我對(duì)幻讀的比較白話的理解：

?

幻讀，并不是說(shuō)兩次讀取獲取的結(jié)果集不同，幻讀側(cè)重的方面是某一次的 select 操作得到的結(jié)果所表征的數(shù)據(jù)狀態(tài)無(wú)法支撐后續(xù)的業(yè)務(wù)操作。更為具體一些：select 某記錄是否存在，不存在，準(zhǔn)備插入此記錄，但執(zhí)行 insert 時(shí)發(fā)現(xiàn)此記錄已存在，無(wú)法插入，此時(shí)就發(fā)生了幻讀。

?

這里給出 mysql 幻讀的比較形象的場(chǎng)景（借用我在知乎上的回答）：

table users: id primary key

事務(wù)T1

事務(wù)T2

?

step1 T1: SELECT * FROM users WHERE id = 1;
step2 T2: INSERT INTO users VALUES (1, 'big cat');step3 T1: INSERT INTO users VALUES (1, 'big cat');step4 T1: SELECT * FROM users WHERE id = 1;

?

T1 ：主事務(wù)，檢測(cè)表中是否有 id 為 1 的記錄，沒(méi)有則插入，這是我們期望的正常業(yè)務(wù)邏輯。

T2 ：干擾事務(wù)，目的在于擾亂 T1 的正常的事務(wù)執(zhí)行。

在 RR 隔離級(jí)別下，step1、step2 是會(huì)正常執(zhí)行的，step3 則會(huì)報(bào)錯(cuò)主鍵沖突，對(duì)于 T1 的業(yè)務(wù)來(lái)說(shuō)是執(zhí)行失敗的，這里 T1 就是發(fā)生了「幻讀」，因?yàn)?T1 在 step1 中讀取的數(shù)據(jù)狀態(tài)并不能支撐后續(xù)的業(yè)務(wù)操作，T1：“見(jiàn)鬼了，我剛才讀到的結(jié)果應(yīng)該可以支持我這樣操作才對(duì)啊，為什么現(xiàn)在不可以”。T1 不敢相信的又執(zhí)行了 step4，發(fā)現(xiàn)和 setp1 讀取的結(jié)果是一樣的（RR下的 MMVC機(jī)制）。此時(shí)，幻讀無(wú)疑已經(jīng)發(fā)生，T1 無(wú)論讀取多少次，都查不到 id = 1 的記錄，但它的確無(wú)法插入這條他通過(guò)讀取來(lái)認(rèn)定不存在的記錄（此數(shù)據(jù)已被T2插入），對(duì)于 T1 來(lái)說(shuō)，它幻讀了。

其實(shí) RR 也是可以避免幻讀的，通過(guò)對(duì) select 操作手動(dòng)加 行X鎖（SELECT ... FOR UPDATE 這也正是 SERIALIZABLE 隔離級(jí)別下會(huì)隱式為你做的事情），同時(shí)還需要知道，即便當(dāng)前記錄不存在，比如 id=1 是不存在的，當(dāng)前事務(wù)也會(huì)獲得一把記錄鎖（因?yàn)镮nnoDB的行鎖鎖定的是索引，故記錄實(shí)體存在與否沒(méi)關(guān)系，存在就加 行X鎖，不存在就加 next-key lock間隙X鎖），其他事務(wù)則無(wú)法插入此索引的記錄，故杜絕了幻讀。

在 SERIALIZABLE 隔離級(jí)別下，step1 執(zhí)行時(shí)是會(huì)隱式的添加 行(X)鎖 / gap(X)鎖的，從而 step2 會(huì)被阻塞，step3 會(huì)正常執(zhí)行，待 T1 提交后，T2 才能繼續(xù)執(zhí)行（主鍵沖突執(zhí)行失?。瑢?duì)于 T1 來(lái)說(shuō)業(yè)務(wù)是正確的，成功的阻塞扼殺了擾亂業(yè)務(wù)的T2，對(duì)于T1來(lái)說(shuō)他前期讀取的結(jié)果是可以支撐其后續(xù)業(yè)務(wù)的。

所以 mysql 的幻讀并非什么讀取兩次返回結(jié)果集不同，而是事務(wù)在插入事先檢測(cè)不存在的記錄時(shí)，驚奇的發(fā)現(xiàn)這些數(shù)據(jù)已經(jīng)存在了，之前的檢測(cè)讀獲取到的數(shù)據(jù)如同鬼影一般。

這里要靈活的理解讀取的意思，第一次select是讀取，第二次的 insert 其實(shí)也屬于隱式的讀取，只不過(guò)是在 mysql 的機(jī)制中讀取的，插入數(shù)據(jù)也是要先讀取一下有沒(méi)有主鍵沖突才能決定是否執(zhí)行插入。

不可重復(fù)讀側(cè)重表達(dá) 讀-讀，幻讀則是說(shuō) 讀-寫(xiě)，用寫(xiě)來(lái)證實(shí)讀的是鬼影。

RR級(jí)別下防止幻讀

RR級(jí)別下只要對(duì) SELECT 操作也手動(dòng)加行（X）鎖即可類(lèi)似 SERIALIZABLE 級(jí)別（它會(huì)對(duì) SELECT 隱式加鎖），即大家熟知的：

#?這里需要用?X鎖，?用?LOCK?IN?SHARE?MODE?拿到?S鎖?后我們沒(méi)辦法做?寫(xiě)操作
SELECT?`id`?FROM?`users`?WHERE?`id`?=?1?FOR?UPDATE;

如果 id = 1 的記錄存在則會(huì)被加行（X）鎖，如果不存在，則會(huì)加 next-lock key / gap 鎖（范圍行鎖），即記錄存在與否，mysql 都會(huì)對(duì)記錄應(yīng)該對(duì)應(yīng)的索引加鎖，其他事務(wù)是無(wú)法再獲得做操作的。

這里我們就展示下 id = 1 的記錄不存在的場(chǎng)景，F(xiàn)OR UPDATE 也會(huì)對(duì)此 “記錄” 加鎖，要明白，InnoDB 的行鎖（gap鎖是范圍行鎖，一樣的）鎖定的是記錄所對(duì)應(yīng)的索引，且聚簇索引同記錄是直接關(guān)系在一起的。

?

id = 1 的記錄不存在，開(kāi)始執(zhí)行事務(wù)：
step1: T1 查詢(xún) id = 1 的記錄并對(duì)其加 X鎖
step2: T2 插入 id = 1 的記錄，被阻塞
step3: T1 插入 id = 1 的記錄，成功執(zhí)行（T2 依然被阻塞中），T1 提交（T2 喚醒但主鍵沖突執(zhí)行錯(cuò)誤）

?

?

T1事務(wù)符合業(yè)務(wù)需求成功執(zhí)行，T2干擾T1失敗。

?

SERIALIZABLE級(jí)別杜絕幻讀

在此級(jí)別下，我們便不需要對(duì) SELECT 操作顯式加鎖，InnoDB會(huì)自動(dòng)加鎖，事務(wù)安全，但性能很低

?

step1: T1 查詢(xún) id = 2 的記錄，InnoDB 會(huì)隱式的對(duì)齊加 X鎖
step2: T2 插入 id = 2 的記錄，被阻塞
step3: T1 插入 id = 2 的記錄，成功執(zhí)行（T2 依然被阻塞中）
step4: T1 成功提交（T2 此時(shí)喚醒但主鍵沖突執(zhí)行錯(cuò)誤）

?

?

T1事務(wù)符合業(yè)務(wù)需求成功執(zhí)行，T2干擾T1失敗。

?

總結(jié)

RR 級(jí)別作為 mysql 事務(wù)默認(rèn)隔離級(jí)別，是事務(wù)安全與性能的折中，可能也符合二八定律（20%的事務(wù)存在幻讀的可能，80%的事務(wù)沒(méi)有幻讀的風(fēng)險(xiǎn)），我們?cè)谡_認(rèn)識(shí)幻讀后，便可以根據(jù)場(chǎng)景靈活的防止幻讀的發(fā)生。

SERIALIZABLE 級(jí)別則是悲觀的認(rèn)為幻讀時(shí)刻都會(huì)發(fā)生，故會(huì)自動(dòng)的隱式的對(duì)事務(wù)所需資源加排它鎖，其他事務(wù)訪問(wèn)此資源會(huì)被阻塞等待，故事務(wù)是安全的，但需要認(rèn)真考慮性能。

InnoDB 的行鎖鎖定的是索引，而不是記錄本身，這一點(diǎn)也需要有清晰的認(rèn)識(shí)，故某索引相同的記錄都會(huì)被加鎖，會(huì)造成索引競(jìng)爭(zhēng)，這就需要我們嚴(yán)格設(shè)計(jì)業(yè)務(wù) sql，盡可能的使用主鍵或唯一索引對(duì)記錄加鎖。索引映射的記錄如果存在，加行鎖，如果不存在，則會(huì)加 next-key lock / gap 鎖 / 間隙鎖，故 InnoDB 可以實(shí)現(xiàn)事務(wù)對(duì)某記錄的預(yù)先占用，如果記錄存在，它就是本事務(wù)的，如果記錄不存在，那它也將是本是無(wú)的，只要本是無(wú)還在，其他事務(wù)就別想占有它。

面試官：undo log 是如何實(shí)現(xiàn) MVCC 的？