串行化隔離級(jí)別怎么解決幻讀問題？
先說下幻讀的含義，幻讀就是在事務(wù)中按照同樣的條件前后兩次查詢的結(jié)果數(shù)據(jù)量不同。

解決串行化的幻讀問題用間隙鎖（gap lock），間隙鎖是給不存在的記錄加鎖，要正確理解間隙，知道間隙的范圍。條件無非就是兩類：范圍查詢和等值查詢。再說下范圍查詢和等值查詢都是怎么加間隙鎖的，分別從主鍵索引和輔助索引兩個(gè)場(chǎng)景來說。

一、間隙鎖的概念

我們把事務(wù)2 select的指定的條件分為2類：范圍查詢、等值查詢

record lock（記錄鎖，就是行鎖）
gap lock（間隙鎖）
next-key lock：record lock 和 gap lock

二、測(cè)試間隙鎖范圍加鎖

設(shè)置事務(wù)為手動(dòng)提交，隔離級(jí)別設(shè)置成串行化

查看表結(jié)構(gòu)，id、age和name都有索引

場(chǎng)景1：用不可重復(fù)的主鍵id測(cè)試間隙鎖

做范圍查詢

事務(wù)2的select操作只給三行數(shù)據(jù)加了排它鎖，為什么插入id=24的數(shù)據(jù)也不行？

這是因?yàn)樵诖谢綦x級(jí)別中，不僅僅是獲取了滿足條件的這3行的行鎖，而且把表數(shù)據(jù)后邊空洞的地方也上了間隙鎖。

圖中紅色線的地方都上了間隙鎖，上鎖范圍（左開右閉）為：( 11 , 12 ] ∪ ( 12 ， 22 ] ∪ ( 22 ， 23 ] ∪ ( 23 ， + ∞ ]

12,22,23是三個(gè)行記錄，因?yàn)檫^濾條件是用id帶有索引的，所以select獲取了12,22,23的共享行鎖（record-lock）， 還把間隙加了間隙鎖，其實(shí)就是給間隙加上共享鎖或者排他鎖，將間隙鎖和行鎖統(tǒng)稱next-key lock（record-lock和gap-lock），也就是說where id>11加了next-key lock。正是因?yàn)榻o空洞也加鎖了，所以事務(wù)1再想獲取間隙的排它鎖是不可以的，因?yàn)楣蚕礞i和排它鎖是不能共存的。

由于事務(wù)2是select，所以是給間隙加上了共享鎖，如果事務(wù)1做select id>11還是可以的，不能update、insert、delete id>11的數(shù)據(jù)。

場(chǎng)景2：用可重復(fù)的age（有索引）測(cè)試間隙鎖

測(cè)試輔助索引樹上，間隙鎖的范圍

我們先查看表結(jié)構(gòu)、表數(shù)據(jù)，然后回滾。

根據(jù)表的內(nèi)容建簡(jiǎn)單的輔助索引

開啟事務(wù)進(jìn)行測(cè)試

很明顯，由于age>20的區(qū)間都被事務(wù)1加上了間隙鎖（這里加的是共享鎖），所以事務(wù)2插入age=22和age=21都失敗了

幻讀就是同一事務(wù)兩次用相同的條件查詢數(shù)據(jù)，下一次查出的數(shù)據(jù)量和上一次的數(shù)據(jù)量不一樣，就算事務(wù)1把a(bǔ)ge=20的數(shù)據(jù)插入表，事務(wù)2再用age>20查詢，得到的數(shù)據(jù)量也不會(huì)改變。

那事務(wù)1插入age=20的數(shù)據(jù)能否成功呢？

依然不能成功，這是因?yàn)槲覀儾迦氲臄?shù)據(jù)id是自增的，所以這條數(shù)據(jù)為(age=20，id=24)，位于輔助索引樹中(age=20，id=12)的右邊，由于(age=20，id=12)右邊都被上了鎖，(age=20，id=24)自然無法插入。

輔助索引值相等的話。主鍵按升序排列。

很顯然，事務(wù)1插入的age=18和age=19都不在事務(wù)2上鎖的范圍，所以可以插入

場(chǎng)景3：實(shí)際情況需要具體分析用的到底是行鎖還是表鎖

回滾，重新開啟事務(wù)

開始測(cè)試

我們發(fā)現(xiàn)事務(wù)1無論是插入age>18范圍內(nèi)的數(shù)據(jù)，還是范圍外的數(shù)據(jù)，都無法成功

這時(shí)我們就要分析了，這應(yīng)該沒有用到索引，因?yàn)槲覀冇盟饕^濾出的數(shù)據(jù)占了整張表的一大半，MySQL server沒使用索引。

沒有加行鎖，只能加表鎖（這時(shí)加的是共享鎖），所以事務(wù)1無論插入什么數(shù)據(jù)都不行

果然，沒有用到索引

age>20用到了索引，所以可以用行鎖

三、測(cè)試間隙鎖等值加鎖

查看表結(jié)構(gòu)和表數(shù)據(jù)

設(shè)置手動(dòng)提交，設(shè)置串行化隔離級(jí)別，回滾然后啟動(dòng)事務(wù)

1. 測(cè)試不能重復(fù)的主鍵索引

此時(shí)事務(wù)2做select操作，由于是等值查詢，所以給這條數(shù)據(jù)加了共享鎖。

事務(wù)2的主鍵或者唯一鍵進(jìn)行等值查詢的時(shí)候，事務(wù)1插入一個(gè)新的數(shù)據(jù)是可以成功的，因?yàn)橹麈Iid不能重復(fù)，我們不能再插入主鍵id=9的數(shù)據(jù)。

在這種情況下，主鍵或者唯一鍵是不能重復(fù)的，事務(wù)2進(jìn)行等值查詢時(shí)，事務(wù)1插入一個(gè)新的數(shù)據(jù)，不用擔(dān)心這條數(shù)據(jù)和查詢條件是一樣的，所以肯定能成功

2. 測(cè)試能重復(fù)的輔助索引

回滾并重啟事務(wù)

事務(wù)2等值查詢，給age=18這行數(shù)據(jù)加上了共享鎖（record-lock）

這是一個(gè)等值查詢，而且用的是輔助索引age，那么在輔助索引age的輔助索引樹上葉子節(jié)點(diǎn)存的是age的輔助索引值和它所在行的主鍵值，

事務(wù)1插入age=18是不被允許的，否則事務(wù)2再查詢age=18就有兩條記錄了。

奇怪的是，我們插入age=17,16,15也被阻塞住了

這是因?yàn)椋瑸榱朔乐够米x，除了age=18這條數(shù)據(jù)加了共享鎖，其兩側(cè)也被加了間隙鎖。

如果插入(age=15，id=1)就可以成功，根據(jù)輔助索引值相同，按照主鍵值升序排列，(age=15，id=1)應(yīng)該放在(age=15,id=7)前面，不在間隙鎖范圍內(nèi)

插入age=14,13都可以成功，不在間隙鎖范圍內(nèi)。

間隙鎖是給不存在的數(shù)據(jù)記錄的范圍加鎖：

• 對(duì)于輔助索引，若值允許重復(fù)，在串行隔離級(jí)別中如果進(jìn)行等值查詢，InnoDB會(huì)給數(shù)據(jù)加上行鎖和間隙鎖（防止別的事務(wù)插入索引值重復(fù)的數(shù)據(jù)，造成幻讀）
• 對(duì)于主鍵索引，或者唯一鍵索引，值不允許重復(fù)，那只需要加行鎖就夠了（對(duì)于唯一鍵索引，不可能發(fā)生插入索引值重復(fù)的數(shù)據(jù)）

到此這篇關(guān)于MySQL串行化隔離級(jí)別(間隙鎖實(shí)現(xiàn))的文章就介紹到這了,更多相關(guān)MySQL 間隙鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論