Mysql 幻讀&Next Key Lock詳解

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

為藤縣等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及藤縣網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都做網(wǎng)站、網(wǎng)站制作、藤縣網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

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

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

開啟一個事務(wù)B，在事務(wù)A第二次執(zhí)行查詢前，執(zhí)行insert into t values( 2,...); 并提交事務(wù)（請暫時忽略這里能否成功執(zhí)行！）。

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

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

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

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

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

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

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

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

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

還是上邊說的那個實(shí)例，略微改動：

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

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

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

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

正確理解MYSQL的幻讀

一、定義

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

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ù)中，同樣的查詢語句執(zhí)行多次，得到了不同的行結(jié)果集。

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

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

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

二、InnoDB的REPEATABLE READ級別

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

1、快照讀：

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

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

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

[ SELECT ]語句中使用 FOR UPDATE 或 FOR SHARE

3、行鎖

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

三、InnoDB的READ COMMITTED級別

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

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

3、由于禁用了間隙鎖定，因此可能會產(chǎn)生幻影行問題，因?yàn)槠渌麜捒梢栽陂g隙中插入新行。

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

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

6、注意

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

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

四、樂觀鎖與悲觀鎖

1、樂觀鎖

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

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

2、悲觀鎖

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

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

五、總結(jié)

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

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

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

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

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

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

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

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

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

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

什么是幻讀？

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

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

幻讀有什么問題？

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

如何解決幻讀？

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

總結(jié)

MySQL的RR隔離級別與幻讀問題

最近在網(wǎng)上看了不少mysql鎖的文章，不少文章都提到InnoDB的RR隔離級別（Repeatable Read）無法解決幻讀的問題。對此問題作者親自做了一些實(shí)驗(yàn)，將實(shí)驗(yàn)結(jié)論記錄在此。

本次實(shí)驗(yàn)的mysql版本為5.7.22 。

此篇文章的重點(diǎn)在于通過實(shí)驗(yàn)的形式解釋清楚InnoDB的RR隔離級別是否解決了幻讀問題。所以文中將不會對一些相關(guān)的概念進(jìn)行解釋，默認(rèn)讀者已經(jīng)具備相關(guān)知識。如果讀者對于以下的知識點(diǎn)不甚清楚，最好自行查閱相關(guān)資料，理解清楚之后再閱讀接下來的實(shí)驗(yàn)內(nèi)容，以免造成困惑。

進(jìn)行此次實(shí)驗(yàn)需要具備的知識點(diǎn)（包括但不限于）：

創(chuàng)建表結(jié)構(gòu)：

創(chuàng)建兩條數(shù)據(jù)：

最終的表數(shù)據(jù)如下：

打開兩個終端，連上mysql，分別啟動事務(wù)a和事務(wù)b。

在事務(wù)a和事務(wù)b上面分別執(zhí)行如下命令：

查詢出來的結(jié)果如下：

事務(wù)a：

事務(wù)b:

很明顯事務(wù)b沒有查詢到事務(wù)a未提交的新插入數(shù)據(jù)。原因也很簡單，因?yàn)?普通的select語句是快照讀，而事務(wù)b啟動時，它的快照數(shù)據(jù)就已經(jīng)被版本鎖定了 。

那么我們在事務(wù)b里面執(zhí)行如下命令來看看執(zhí)行結(jié)果：

執(zhí)行完成之后我們發(fā)現(xiàn)事務(wù)b此時會block住，原因是 事務(wù)a的insert語句排它鎖住了id為3的新插入數(shù)據(jù)，而事務(wù)b想請求所有行的共享鎖，肯定是需要等待的。

那么此時事務(wù)b當(dāng)前讀id為1或2的數(shù)據(jù)（非事務(wù)a新插入數(shù)據(jù)）是否可行呢？

結(jié)論是可行的，因?yàn)?tmp_table 存在唯一鍵，且事務(wù)a的insert語句只是鎖住了id為3的行。所以其他事務(wù)獲取其他行的共享鎖是可行的 。讀者可以自行測試，這里就不做演示了。

事務(wù)a和事務(wù)b執(zhí)行如下命令：

事務(wù)b打印的結(jié)果：

還是一樣， 因?yàn)槠胀╯elect是快照讀，事務(wù)b還是讀取到的是快照數(shù)據(jù)，所以不包含事務(wù)a提交之后的新數(shù)據(jù) 。

讓我們在事務(wù)b下面使用共享鎖查看當(dāng)前版本數(shù)據(jù)：

結(jié)果如下：

可以查詢到事務(wù)a已提交的新數(shù)據(jù)，所以此時使用當(dāng)前讀就產(chǎn)生了幻讀 。

還有另一種情況也會產(chǎn)生幻讀，并且只需要執(zhí)行普通的select語句。下面請看演示。

在事務(wù)b下面執(zhí)行如下兩條語句：

第一條命令使用update更新了事務(wù)a已提交的新數(shù)據(jù)，第二條命令通過普通的select語句查看快照數(shù)據(jù)。

打印結(jié)果如下：

可以看到事務(wù)a已提交的新數(shù)據(jù)被事務(wù)b使用update語句更新了，并且通過普通的select語句給查詢出來了，很顯然，出現(xiàn)了幻讀 。

所以說InnoDB的RR隔離級別沒有或者解決了幻讀問題都不太準(zhǔn)確。應(yīng)該說它并沒有完全解決幻讀的問題。

如果在同一個事務(wù)里面，只是總是執(zhí)行普通的select快照讀，是不會產(chǎn)生幻讀的。

但是如果在這個事務(wù)里面通過當(dāng)前讀或者先更新然后快照讀的形式來讀取數(shù)據(jù)，就會產(chǎn)生幻讀。

Phantom Rows

Innodb 中 RR 隔離級別能否防止幻讀？

分享名稱：mysql幻讀怎么解決,mysql是如何解決幻讀
標(biāo)題路徑：http://chinadenli.net/article10/dsepggo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、網(wǎng)站設(shè)計、企業(yè)建站、網(wǎng)站導(dǎo)航、品牌網(wǎng)站建設(shè)、服務(wù)器托管

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)