Mysql 幻讀&Next Key Lock詳解

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

創(chuàng)新互聯(lián)建站憑借在網(wǎng)站建設(shè)、網(wǎng)站推廣領(lǐng)域領(lǐng)先的技術(shù)能力和多年的行業(yè)經(jīng)驗(yàn)，為客戶提供超值的營(yíng)銷型網(wǎng)站建設(shè)服務(wù)，我們始終認(rèn)為：好的營(yíng)銷型網(wǎng)站就是好的業(yè)務(wù)員。我們已成功為企業(yè)單位、個(gè)人等客戶提供了成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)服務(wù)，以良好的商業(yè)信譽(yù)，完善的服務(wù)及深厚的技術(shù)力量處于同行領(lǐng)先地位。

比如： 表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í)別下解決幻讀問題就是通過Next Key Lock (間隙鎖+行鎖)來(lái)實(shí)現(xiàn)的。而且，很多地方也有提到，如果對(duì)于讀一致性要求不高的場(chǎng)景可以考慮使用RC隔離級(jí)別，允許幻讀的發(fā)生。

還是上邊說的那個(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)說，事務(wù)B

mysql如何解決幻讀

幻讀是指：在一個(gè)事務(wù)中，讀取到了其他已經(jīng)提交的事務(wù)插入的數(shù)據(jù)行。

MySQL在解決臟讀、不可重復(fù)的讀時(shí)候，使用了MVCC一致性視圖，同時(shí)配合行鎖來(lái)解決。

至于幻讀的解決方式，MySQL引入了臨鍵鎖，通過間隙鎖可以避免在兩個(gè)行之間插入數(shù)據(jù)，從而避免了一個(gè)事務(wù)在讀取的過程中，讀取到其他事務(wù)插入的數(shù)據(jù)行。

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

什么是幻讀？

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

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

幻讀有什么問題？

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

如何解決幻讀？

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

總結(jié)

正確理解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.

翻譯一下：

所謂的幻影行問題是指，在同一個(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)生幻影行問題，因?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操作（增刪改，通過聯(lián)接列表或子查詢）從MySQL授權(quán)表中讀取數(shù)據(jù)，但不對(duì)其進(jìn)行修改，無(wú)論隔離級(jí)別如何，都不會(huì)在MySQL授權(quán)表上獲得讀取鎖。

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

四、樂觀鎖與悲觀鎖

1、樂觀鎖

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

使用樂觀鎖時(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鎖，也可以通過SELECT ... LOCK IN SHARE MODE加上S鎖，來(lái)防止其他事務(wù)對(duì)該行的修改。

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

五、總結(jié)

1、RC級(jí)別沒有范圍鎖一定會(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í)別下，可以通過提交當(dāng)前事務(wù)并在此之后發(fā)出新查詢來(lái)為查詢獲取更新的快照。

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

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

MySQL 事務(wù)的默認(rèn)隔離級(jí)別是什么？可以解決幻讀問題么？

我們?cè)O(shè)想一個(gè)場(chǎng)景，這個(gè)場(chǎng)景中我們需要插入多條相關(guān)聯(lián)的數(shù)據(jù)到數(shù)據(jù)庫(kù)，不幸的是，這個(gè)過程可能會(huì)遇到下面這些問題：

上面的任何一個(gè)問題都可能會(huì)導(dǎo)致數(shù)據(jù)的不一致性。為了保證數(shù)據(jù)的一致性，系統(tǒng)必須能夠處理這些問題。事務(wù)就是我們抽象出來(lái)簡(jiǎn)化這些問題的首選機(jī)制。事務(wù)的概念起源于數(shù)據(jù)庫(kù)，目前，已經(jīng)成為一個(gè)比較廣泛的概念。

何為事務(wù)？ 一言蔽之， 事務(wù)是邏輯上的一組操作，要么都執(zhí)行，要么都不執(zhí)行。

事務(wù)最經(jīng)典也經(jīng)常被拿出來(lái)說例子就是轉(zhuǎn)賬了。假如小明要給小紅轉(zhuǎn)賬 1000 元，這個(gè)轉(zhuǎn)賬會(huì)涉及到兩個(gè)關(guān)鍵操作，這兩個(gè)操作必須都成功或者都失敗。

事務(wù)會(huì)把這兩個(gè)操作就可以看成邏輯上的一個(gè)整體，這個(gè)整體包含的操作要么都成功，要么都要失敗。這樣就不會(huì)出現(xiàn)小明余額減少而小紅的余額卻并沒有增加的情況。

大多數(shù)情況下，我們?cè)谡務(wù)撌聞?wù)的時(shí)候，如果沒有特指 分布式事務(wù) ，往往指的就是 數(shù)據(jù)庫(kù)事務(wù) 。

數(shù)據(jù)庫(kù)事務(wù)在我們?nèi)粘ｉ_發(fā)中接觸的最多了。如果你的項(xiàng)目屬于單體架構(gòu)的話，你接觸到的往往就是數(shù)據(jù)庫(kù)事務(wù)了。

那數(shù)據(jù)庫(kù)事務(wù)有什么作用呢？

簡(jiǎn)單來(lái)說，數(shù)據(jù)庫(kù)事務(wù)可以保證多個(gè)對(duì)數(shù)據(jù)庫(kù)的操作（也就是 SQL 語(yǔ)句）構(gòu)成一個(gè)邏輯上的整體。構(gòu)成這個(gè)邏輯上的整體的這些數(shù)據(jù)庫(kù)操作遵循： 要么全部執(zhí)行成功,要么全部不執(zhí)行 。

另外，關(guān)系型數(shù)據(jù)庫(kù)（例如： MySQL 、 SQL Server 、 Oracle 等）事務(wù)都有 ACID 特性：

ACID

這里要額外補(bǔ)充一點(diǎn)： 只有保證了事務(wù)的持久性、原子性、隔離性之后，一致性才能得到保障。也就是說 A、I、D 是手段，C 是目的！

在典型的應(yīng)用程序中，多個(gè)事務(wù)并發(fā)運(yùn)行，經(jīng)常會(huì)操作相同的數(shù)據(jù)來(lái)完成各自的任務(wù)（多個(gè)用戶對(duì)同一數(shù)據(jù)進(jìn)行操作）。并發(fā)雖然是必須的，但可能會(huì)導(dǎo)致以下的問題。

不可重復(fù)讀和幻讀區(qū)別 ：不可重復(fù)讀的重點(diǎn)是修改比如多次讀取一條記錄發(fā)現(xiàn)其中某些列的值被修改，幻讀的重點(diǎn)在于新增或者刪除比如多次查詢同一條查詢語(yǔ)句（DQL）時(shí)，記錄發(fā)現(xiàn)記錄增多或減少了。

SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別：

隔離級(jí)別臟讀不可重復(fù)讀幻讀 READ-UNCOMMITTED READ-COMMITTED REPEATABLE-READ SERIALIZABLE

MySQL 的隔離級(jí)別基于鎖和 MVCC 機(jī)制共同實(shí)現(xiàn)的。

SERIALIZABLE 隔離級(jí)別，是通過鎖來(lái)實(shí)現(xiàn)的。除了 SERIALIZABLE 隔離級(jí)別，其他的隔離級(jí)別都是基于 MVCC 實(shí)現(xiàn)。

不過， SERIALIZABLE 之外的其他隔離級(jí)別可能也需要用到鎖機(jī)制，就比如 REPEATABLE-READ 在當(dāng)前讀情況下需要使用加鎖讀來(lái)保證不會(huì)出現(xiàn)幻讀。

MySQL InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級(jí)別是 REPEATABLE-READ（可重讀） 。我們可以通過 SELECT @@tx_isolation; 命令來(lái)查看，MySQL 8.0 該命令改為 SELECT @@transaction_isolation;

從上面對(duì) SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別的介紹可以看出，標(biāo)準(zhǔn)的 SQL 隔離級(jí)別定義里，REPEATABLE-READ(可重復(fù)讀)是不可以防止幻讀的。

但是！InnoDB 實(shí)現(xiàn)的 REPEATABLE-READ 隔離級(jí)別其實(shí)是可以解決幻讀問題發(fā)生的，主要有下面兩種情況：

因?yàn)楦綦x級(jí)別越低，事務(wù)請(qǐng)求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫(kù)系統(tǒng)的隔離級(jí)別都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存儲(chǔ)引擎默認(rèn)使用 REPEATABLE-READ 并不會(huì)有任何性能損失。

InnoDB 存儲(chǔ)引擎在分布式事務(wù)的情況下一般會(huì)用到 SERIALIZABLE 隔離級(jí)別。

網(wǎng)站標(biāo)題：mysql怎么消除幻讀,mysql怎么避免幻讀
網(wǎng)站網(wǎng)址：http://www.rwnh.cn/article0/dsigpio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、軟件開發(fā)、網(wǎng)站建設(shè)、外貿(mào)建站、全網(wǎng)營(yíng)銷推廣、定制開發(fā)

廣告

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