Mysql 幻讀&Next Key Lock詳解

幻讀的定義是指，一個(gè)事務(wù)開啟后，執(zhí)行前后兩次查詢，兩次查詢中出現(xiàn)了新的數(shù)據(jù)，幻讀僅針對(duì)數(shù)據(jù)的新增。

站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到灌陽(yáng)網(wǎng)站設(shè)計(jì)與灌陽(yáng)網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：網(wǎng)站建設(shè)、網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名申請(qǐng)、網(wǎng)絡(luò)空間、企業(yè)郵箱。業(yè)務(wù)覆蓋灌陽(yáng)地區(qū)。

比如： 表t中，id為主鍵，目前有數(shù)據(jù)1,5,10,20四條。

開始一個(gè)事務(wù)A，前后兩次執(zhí)行 select * from t where id 10 for update;

開啟一個(gè)事務(wù)B，在事務(wù)A第二次執(zhí)行查詢前，執(zhí)行insert into t values( 2,...); 并提交事務(wù)（請(qǐng)暫時(shí)忽略這里能否成功執(zhí)行?。?/p>

此時(shí)在RC、RR隔離級(jí)別下都會(huì)導(dǎo)致事務(wù)A第二次查詢能夠查詢到 事務(wù)B新增的數(shù)據(jù) id = 2。

RC級(jí)別下能夠看到不同結(jié)果就不做解釋了。

對(duì)于RR隔離級(jí)別下，有了MVCC版本控制為什么還能讀取到不同的結(jié)果呢？

這里要?dú)w功于 "for update"。

"for update" 會(huì)將快照讀變?yōu)楫?dāng)前讀，在當(dāng)前讀場(chǎng)景中，會(huì)自動(dòng)讀取最新的數(shù)據(jù)，而非快照數(shù)據(jù)。

分析一下，鎖與當(dāng)前讀關(guān)系。了解什么情況下會(huì)加鎖。了解 意向鎖、共享鎖、排它鎖區(qū)別及各自在什么情況下使用。

行鎖的概念都清楚，這里就不做補(bǔ)充了。

間隙鎖實(shí)際上是指一個(gè)區(qū)間。

我們都知道，InnoDB 在RR事務(wù)隔離級(jí)別下解決幻讀問(wèn)題就是通過(guò)Next Key Lock (間隙鎖+行鎖)來(lái)實(shí)現(xiàn)的。而且，很多地方也有提到，如果對(duì)于讀一致性要求不高的場(chǎng)景可以考慮使用RC隔離級(jí)別，允許幻讀的發(fā)生。

還是上邊說(shuō)的那個(gè)實(shí)例，略微改動(dòng)：

比如： 表t中，id為主鍵，目前有數(shù)據(jù)1,5,10,20四條。

開始一個(gè)事務(wù)A，前后三次分別執(zhí)行

開啟一個(gè)事務(wù)B，在事務(wù)A執(zhí)行update前，執(zhí)行insert into t values( 2,...); 并提交事務(wù)。

此時(shí)我們知道，事務(wù)A中第二次查詢能夠查到 事務(wù)B新增的數(shù)據(jù)，也就是產(chǎn)生了幻讀。那么，按照SQL執(zhí)行的順序來(lái)說(shuō)，事務(wù)B

關(guān)于MySQL的幻讀問(wèn)題，看這一篇就夠了

什么是幻讀？

幻讀指的是一個(gè)事務(wù)在前后兩次查詢同一個(gè)范圍的時(shí)候，后一次查詢看到了前一次查詢沒(méi)有看到的行。

首先快照讀是不存在幻讀的，只有當(dāng)前讀（實(shí)時(shí)讀）才存在幻讀的問(wèn)題。

幻讀有什么問(wèn)題？

select ...for update語(yǔ)句就是將相應(yīng)的數(shù)據(jù)行鎖住，但是如果存在幻讀，就把for update的語(yǔ)義破壞了。

如何解決幻讀？

產(chǎn)生幻讀的原因是，行鎖只能鎖住行，但是新插入記錄這個(gè)動(dòng)作，要更新的是記錄之間的“間隙”。因此，為了解決幻讀問(wèn)題，InnoDB只好引入新的鎖，也就是間隙鎖(Gap Lock)。間隙鎖和行鎖合稱 next-key lock ， 每個(gè)next-key lock是前開后閉區(qū)間 。

總結(jié)

mysql 解決可提交讀、可重復(fù)讀、幻讀

這張圖本人覺(jué)得總結(jié)得挺好的，在一般的互聯(lián)網(wǎng)項(xiàng)目中，基本上用的都是Innodb引擎，一般只涉及到的都是行級(jí)鎖，但是如果sql語(yǔ)句中不帶索引進(jìn)行操作，可能會(huì)導(dǎo)致鎖表，這是不推薦的，性能非常低，可能會(huì)導(dǎo)致全表掃描等，行鎖的具體實(shí)現(xiàn)算法有以下幾種mysql特有的鎖：

Record Lock(記錄鎖):單個(gè)行記錄的鎖,一般是唯一索引或者主鍵上的加鎖

Gap Lock(間隙鎖):鎖定一個(gè)區(qū)間，但是不包括自身，開區(qū)間的鎖，RR級(jí)別才會(huì)有間隙鎖，間隙鎖的唯一目的是防止區(qū)間數(shù)據(jù)的插入，所以間隙鎖與間隙鎖之間是不會(huì)相互阻塞的

Next-key Lock(臨鍵鎖):與間隙鎖的區(qū)別是包括自身，是左開右閉區(qū)間，RR級(jí)別才會(huì)有

加鎖規(guī)則里面，包含了兩個(gè)“原則”、兩個(gè)“優(yōu)化”和一個(gè)“bug”。

原則 1：加鎖的基本單位是 next-key lock，希望你還記得，next-key lock 是前開后閉區(qū)間。

原則 2：查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。

優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖。

優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖。

一個(gè) bug：唯一索引上的范圍查詢會(huì)訪問(wèn)到不滿足條件的第一個(gè)值為止。

舉例來(lái)說(shuō)明上述的原則：

建表

插入數(shù)據(jù)：

INSERT INTO t ( id , c , d ) VALUES (0, 0, 0);

INSERT INTO t ( id , c , d ) VALUES (5, 5, 10);

INSERT INTO t ( id , c , d ) VALUES (10, 10, 10);

INSERT INTO t ( id , c , d ) VALUES (15, 15, 15);

INSERT INTO t ( id , c , d ) VALUES (20, 20, 20);

INSERT INTO t ( id , c , d ) VALUES (25, 25, 25);

例子1：鎖表

因?yàn)閐字段上沒(méi)有建索引，所以涉及該字段的查詢加鎖會(huì)鎖住整個(gè)表

因?yàn)閐字段上面沒(méi)有建立索引，所以事務(wù)1執(zhí)行后會(huì)導(dǎo)致整個(gè)表被鎖，后面所有的操作都會(huì)在等待整個(gè)表鎖被釋放

例子2：主鍵/唯一索引 記錄鎖

id字段為主鍵，而且事務(wù)1查詢命中了唯一的記錄，默認(rèn)是加Next-key Lock，區(qū)間是（0，5]，但是根據(jù)優(yōu)化1，唯一索引/主鍵上的等值查詢，會(huì)退化為行鎖，所以只會(huì)鎖5這個(gè)記錄。

例子3：主鍵/唯一索引上的間隙鎖

由于表 t 中沒(méi)有 id=7 的記錄，所以用我們上面提到的加鎖規(guī)則判斷一下的話：根據(jù)原則 1，加鎖單位是 next-key lock，事務(wù)1加鎖范圍就是 (5,10]；同時(shí)根據(jù)優(yōu)化 2，這是一個(gè)等值查詢 (id=7)，而 id=10 不滿足查詢條件，next-key lock 退化成間隙鎖，因此最終加鎖的范圍是 (5,10)，所以事務(wù)2會(huì)阻塞，事務(wù)3執(zhí)行成功。

例子4：普通索引上的間隙鎖

c字段是普通索引，事務(wù)1執(zhí)行時(shí)默認(rèn)是對(duì)區(qū)間（0,5]加間隙鎖，根據(jù)優(yōu)化2，非唯一索引/主鍵會(huì)繼續(xù)向右遍歷，找到10，所以最終的加鎖為（0，5]的Next-Key鎖+(5，10)的間隙鎖，所以事務(wù)2阻塞，事務(wù)3成功。

例子5：間隙鎖與行鎖

事務(wù)1默認(rèn)的Next-Key鎖區(qū)間是(0，5],根據(jù)優(yōu)化2會(huì)向右遍歷，找到不滿足查詢條件的10，退化成間隙鎖，所以事務(wù)1的鎖是（0，5]的Next-Key鎖+(5，10)的間隙鎖，這兩個(gè)鎖與行鎖是沖突的，而事務(wù)2申請(qǐng)的Next-Key鎖是和事務(wù)1一樣，但是c=5的行鎖與事務(wù)1沖突，所以產(chǎn)生了阻塞，如果改為update t set d=1000 where c=6;因?yàn)榇藭r(shí)產(chǎn)生的間隙鎖為（5，10），而間隙鎖與間隙鎖是不沖突的，不會(huì)產(chǎn)生阻塞

例子6：lock in share mode鎖覆蓋索引

事務(wù)1存在覆蓋索引的情況，不會(huì)去回表，lock in share mode這種情況下只會(huì)鎖c字段索引，而事務(wù)2是對(duì)主鍵加行鎖，所以兩者不存在沖突。

例子7：主鍵/唯一索引上的范圍查詢

開始執(zhí)行的時(shí)候，要找到第一個(gè) id=10 的行，因此本該是 Next-Key Lock(5,10]，根據(jù)優(yōu)化 1， 主鍵 id 上的等值條件，退化成行鎖，只加了 id=10 這一行的行鎖。范圍查找就往后繼續(xù)找，找到 id=15 這一行停下來(lái)，因此需要加 Next-Key Lock(10,15]，所以事務(wù)3是沖突的。

例子8：普通索引上的范圍查詢

開始執(zhí)行時(shí)，找到第一個(gè)滿足條件的行10，加鎖Next-Key Lock(5,10]，因?yàn)椴皇俏ㄒ凰饕?，所以不?huì)退化，繼續(xù)向后面找，找到15這一行停下來(lái)，因此需要加 Next-Key Lock(10,15]，因?yàn)槭欠秶樵?，所以鎖不會(huì)退化。

快照讀： 通過(guò)MVCC實(shí)現(xiàn)，該技術(shù)不僅可以保證innodb的可重復(fù)讀，而且可以防止幻讀，但是他讀取的數(shù)據(jù)雖然是一致的，但是數(shù)據(jù)是歷史數(shù)據(jù)。

簡(jiǎn)單的select操作(不包括 select … lock in share mode, select … for update)

當(dāng)前讀： 要做到保證數(shù)據(jù)是一致的，同時(shí)讀取的數(shù)據(jù)是最新的數(shù)據(jù),innodb提供了next-key lock，即gap鎖與行鎖結(jié)合來(lái)實(shí)現(xiàn)。

select … lock in share mode

select … for update

insert

update

delete

自己理解：

簡(jiǎn)單的select是快照讀，快照讀實(shí)現(xiàn)可提交讀，可重復(fù)讀和幻讀是通過(guò)MVCC+ReadView實(shí)現(xiàn)的,而當(dāng)前讀實(shí)現(xiàn)這幾種是通過(guò)鎖來(lái)實(shí)現(xiàn)的,為了說(shuō)明具體原理,下面介紹下MVCC和ReadView概念，所以簡(jiǎn)單的select是通過(guò)樂(lè)觀鎖實(shí)現(xiàn)的，當(dāng)前讀是通過(guò)悲觀鎖實(shí)現(xiàn)的。

參考文章：

正確理解MYSQL的幻讀

一、定義

1、幻讀MYSQL官方叫法是Phantom Rows，意為鬼影行或者幻影行，請(qǐng)看官方定義：

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.

翻譯一下：

所謂的幻影行問(wèn)題是指，在同一個(gè)事務(wù)中，同樣的查詢語(yǔ)句執(zhí)行多次，得到了不同的行結(jié)果集。

例如，如果同一個(gè)SELECT語(yǔ)句執(zhí)行了兩次，第二次執(zhí)行的時(shí)候比第一次執(zhí)行時(shí)多出一行，則該行就是所謂的幻影行。

2、幻讀與不可重復(fù)讀的區(qū)別

從官方的定義來(lái)看，幻讀的定義側(cè)重于多條記錄，就是記錄條數(shù)的變化，而不可重復(fù)讀側(cè)重于單條記錄數(shù)據(jù)的變化，這樣區(qū)分原因在于解決幻讀需要范圍鎖，解決不可重復(fù)讀只需要單條記錄加鎖

二、InnoDB的REPEATABLE READ級(jí)別

InnoDB支持由SQL1992標(biāo)準(zhǔn)描述的所有四個(gè)事務(wù)隔離級(jí)別，默認(rèn)隔離級(jí)別是 REPEATABLE READ。

1、快照讀：

在RR模式下，第一次讀取會(huì)建立快照，后續(xù)查詢會(huì)讀取快照。

這意味著，如果在同一事務(wù)中發(fā)出多個(gè)普通[ SELECT ]（非鎖定）語(yǔ)句，則這些 [ SELECT ]語(yǔ)句的結(jié)果也是一致的。

2、[locking reads](鎖定讀取，又叫當(dāng)前讀）

[ SELECT ]語(yǔ)句中使用 FOR UPDATE 或 FOR SHARE

3、行鎖

在RR模式下，使用當(dāng)前讀以及 [ UPDATE ]和 [ DELETE ]語(yǔ)句會(huì)對(duì)數(shù)據(jù)記錄加行鎖，鎖定范圍取決于該語(yǔ)句使用的是具有唯一搜索條件的唯一索引還是范圍類型搜索條件。

三、InnoDB的READ COMMITTED級(jí)別

1、在RC模式下，每次讀取都會(huì)刷新快照，因此不能保證可重復(fù)讀

2、在RC模式下，使用當(dāng)前讀以及 [ UPDATE ]和 [ DELETE ]語(yǔ)句會(huì)對(duì)數(shù)據(jù)記錄加行鎖，但是不會(huì)加范圍鎖，間隙鎖定僅用于外鍵約束檢查和重復(fù)鍵檢查。

3、由于禁用了間隙鎖定，因此可能會(huì)產(chǎn)生幻影行問(wèn)題，因?yàn)槠渌麜?huì)話可以在間隙中插入新行。

4、 對(duì)于[ UPDATE ]或 [ DELETE ]語(yǔ)句， InnoDB 僅對(duì)其更新或刪除的行持有鎖。MySQL評(píng)估 WHERE 條件后，將釋放不匹配行的記錄鎖 。這大大降低了死鎖的可能性，但是仍然可以發(fā)生。

5、對(duì)于[ UPDATE ]語(yǔ)句，如果某行已被鎖定，則 InnoDB 執(zhí)行“半一致”讀取，將最新提交版本的數(shù)據(jù)返回給MySQL，以便MySQL可以確定該行是否符合 WHERE 條件。如果該行匹配（必須更新），則MySQL會(huì)再次讀取該行，這一次 InnoDB 會(huì)將其鎖定或等待獲取鎖。

6、注意

從MySQL 8.0.22開始，DML操作（增刪改，通過(guò)聯(lián)接列表或子查詢）從MySQL授權(quán)表中讀取數(shù)據(jù)，但不對(duì)其進(jìn)行修改，無(wú)論隔離級(jí)別如何，都不會(huì)在MySQL授權(quán)表上獲得讀取鎖。

有關(guān)更多信息，請(qǐng)參見 Grant Table Concurrency 。

四、樂(lè)觀鎖與悲觀鎖

1、樂(lè)觀鎖

在UPDATE的WHERE子句中加入版本信息來(lái)確定修改是否生效

使用樂(lè)觀鎖時(shí)仍然需要非常謹(jǐn)慎，因?yàn)镽R是可重復(fù)讀的，在UPDATE之前讀取版本號(hào)，應(yīng)該使用[當(dāng)前讀]，不能使用[快照讀]

2、悲觀鎖

在UPDATE執(zhí)行前，SELECT后面加上FOR UPDATE來(lái)給記錄加鎖，保證記錄在UPDATE前不被修改。SELECT ... FOR UPDATE是加上了X鎖，也可以通過(guò)SELECT ... LOCK IN SHARE MODE加上S鎖，來(lái)防止其他事務(wù)對(duì)該行的修改。

3、無(wú)論是樂(lè)觀鎖還是悲觀鎖，使用的思想都是一致的，那就是當(dāng)前讀。樂(lè)觀鎖利用當(dāng)前讀判斷是否是最新版本，悲觀鎖利用當(dāng)前讀鎖定行。

五、總結(jié)

1、RC級(jí)別沒(méi)有范圍鎖一定會(huì)導(dǎo)致不可重復(fù)讀和幻影行

2、RR級(jí)別安全性更高，實(shí)現(xiàn)可重復(fù)讀的方式為快照，如果需要最新數(shù)據(jù)可以選擇[當(dāng)前讀]，因此RR級(jí)別是首選

3、不論RR還是RC級(jí)別，增、刪、改的操作都會(huì)進(jìn)行一次[當(dāng)前讀]操作，以此獲取最新版本的數(shù)據(jù)，并檢測(cè)是否有重復(fù)的索引。

4、RR級(jí)別下，當(dāng)前事務(wù)如果未發(fā)生更新操作(增刪改)，快照版本會(huì)保持不變，多次查詢讀取的快照是同一個(gè)

5、RR級(jí)別下，當(dāng)前事務(wù)如果發(fā)生更新(增刪改)，會(huì)刷新快照，會(huì)導(dǎo)致不可重復(fù)讀和幻影行

6、RR級(jí)別下，使用當(dāng)前讀，會(huì)刷新快照，會(huì)導(dǎo)致不可重復(fù)讀和幻影行

7、RR級(jí)別下，可以通過(guò)提交當(dāng)前事務(wù)并在此之后發(fā)出新查詢來(lái)為查詢獲取更新的快照。

8、RR級(jí)別可以部分解決不可重復(fù)讀和幻讀問(wèn)題

9、其實(shí)問(wèn)題的關(guān)鍵是你的業(yè)務(wù)邏輯需要可重復(fù)讀還是最新數(shù)據(jù)