溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

數(shù)據(jù)庫(kù)中加鎖規(guī)則有哪些

發(fā)布時(shí)間:2021-02-03 09:28:09 來(lái)源:億速云 閱讀:343 作者:小新 欄目:數(shù)據(jù)庫(kù)

這篇文章主要介紹數(shù)據(jù)庫(kù)中加鎖規(guī)則有哪些,文中介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們一定要看完!

間隙鎖再加上行鎖,很容易在判斷是否會(huì)出現(xiàn)鎖等待的問(wèn)題上犯錯(cuò)。

因?yàn)殚g隙鎖在可重復(fù)讀隔離級(jí)別下才有效,本文默認(rèn)可重復(fù)讀。

加鎖規(guī)則

  • 原則1
    加鎖的基本單位是next-key lock,前開(kāi)后閉區(qū)間。

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

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

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

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

數(shù)據(jù)準(zhǔn)備

表名:t
新增數(shù)據(jù):(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25)
接下來(lái)的例子基本都是配合著圖片說(shuō)明的,所以我建議你可以對(duì)照著文稿看,有些例子可能會(huì)“毀三觀”,也建議你讀完文章后親手實(shí)踐一下。

案例

等值查詢(xún)間隙鎖

  • 等值查詢(xún)的間隙鎖數(shù)據(jù)庫(kù)中加鎖規(guī)則有哪些

  • 表t中無(wú)id=7,所以根據(jù)原則1,加鎖單位next-key lock,所以session A加鎖范圍(5,10]

  • 同時(shí)根據(jù)優(yōu)化2,等值查詢(xún)(id=7),而id=10不滿(mǎn)足,next-key lock退化成間隙鎖,因此最終加鎖范圍(5,10)

所以,session B要往這個(gè)間隙里面插入id=8的記錄會(huì)被鎖住,但是session C修改id=10這行是可以的。

非唯一索引等值鎖

  • 只加在非唯一索引上的鎖
    數(shù)據(jù)庫(kù)中加鎖規(guī)則有哪些

  • session A要給索引c的c=5這行加讀鎖
    根據(jù)原則1,加鎖單位next-key lock,因此給(0,5]加next-key lock
    c是普通索引,因此僅訪問(wèn)c=5這條記錄不能馬上停下,需要向右遍歷,查到c=10才放棄。根據(jù)原則2,訪問(wèn)到的都要加鎖,因此要給(5,10]加next-key lock
    同時(shí)符合優(yōu)化2:等值判斷,向右遍歷,最后一個(gè)值不滿(mǎn)足c=5這個(gè)等值條件,因此退化成間隙鎖(5,10)
    根據(jù)原則2 ,只有訪問(wèn)到的對(duì)象才會(huì)加鎖,這個(gè)查詢(xún)使用覆蓋索引,并不需要訪問(wèn)主鍵索引,所以主鍵索引上沒(méi)有加任何鎖,所以session B的update語(yǔ)句可以執(zhí)行完成。
    但session C要插入(7,7,7),就會(huì)被session A的間隙鎖(5,10)鎖住。

這個(gè)例子中,lock in share mode只鎖覆蓋索引,但如果是for update就不一樣了。 執(zhí)行 for update時(shí),系統(tǒng)會(huì)認(rèn)為你接下來(lái)要更新數(shù)據(jù),因此會(huì)順便給主鍵索引上滿(mǎn)足條件的行加上行鎖。

這例說(shuō)明,鎖是加在索引上的;同時(shí),它給我們的指導(dǎo)是,如果你要用lock in share mode來(lái)給行加讀鎖避免數(shù)據(jù)被更新的話,就必須得繞過(guò)覆蓋索引的優(yōu)化,在查詢(xún)字段中加入索引中不存在的字段。比如,將session A的查詢(xún)語(yǔ)句改成select d from t where c=5 lock in share mode。你可以自己驗(yàn)證一下效果。

3 主鍵索引范圍鎖

范圍查詢(xún)。

對(duì)于我們這個(gè)表t,下面這兩條查詢(xún)語(yǔ)句,加鎖范圍相同嗎?

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
你可能會(huì)想,id定義為int類(lèi)型,這兩個(gè)語(yǔ)句就是等價(jià)的吧?其實(shí),它們并不完全等價(jià)。

在邏輯上,這兩條查語(yǔ)句肯定是等價(jià)的,但是它們的加鎖規(guī)則不太一樣?,F(xiàn)在,我們就讓session A執(zhí)行第二個(gè)查詢(xún)語(yǔ)句,來(lái)看看加鎖效果。

圖3 主鍵索引上范圍查詢(xún)的鎖
現(xiàn)在我們就用前面提到的加鎖規(guī)則,來(lái)分析一下session A 會(huì)加什么鎖呢?

開(kāi)始執(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]。

所以,session A這時(shí)候鎖的范圍就是主鍵索引上,行鎖id=10和next-key lock(10,15]。這樣,session B和session C的結(jié)果你就能理解了。

這里你需要注意一點(diǎn),首次session A定位查找id=10的行的時(shí)候,是當(dāng)做等值查詢(xún)來(lái)判斷的,而向右掃描到id=15的時(shí)候,用的是范圍查詢(xún)判斷。

再看看范圍查詢(xún)加鎖,你可以對(duì)照著案例三

非唯一索引范圍鎖

session_1session_2session_3
begin;
select * from t where c>=10 and c<11 for update;



insert into t values(8,8,8);(blocked)


update t set d=d+1 where c=15;(blocked)
  • session1在第一次用c=10定位記錄時(shí),索引c加了(5,10] next-key lock

  • c是非唯一索引,無(wú)優(yōu)化規(guī)則,即不會(huì)退變?yōu)樾墟i

  • 因此最終sesion1加鎖為c的(5,10](10,15] next-key lock。

所以從結(jié)果上來(lái)看,sesson2要插入(8,8,8)的這個(gè)insert語(yǔ)句時(shí)就被阻塞。

非唯一索引要掃到c=15,才知道無(wú)需繼續(xù)往后遍歷。

唯一索引范圍鎖bug

前四案例用到兩個(gè)原則和兩個(gè)優(yōu)化,再看加鎖規(guī)則bug案例。

session_1session_2session_3
begin;
select * from t
where id>10 and id<=15 for update;



update t
set d=d+1
where id=20;(阻塞)



insert into t values(16,16,16);(阻塞)

session1是范圍查詢(xún)

  • 按原則1,索引id只加(10,15] next-key lock,因?yàn)閕d是唯一鍵,所以循環(huán)判斷到id=15這行就該停止遍歷。

但實(shí)現(xiàn)上,InnoDB會(huì)繼續(xù)掃描到第一個(gè)不滿(mǎn)足條件的行,即id=20,且由于這是范圍掃描,因此id上的(15,20] next-key lock也會(huì)被鎖。

所以session2要更新id=20這行會(huì)被阻塞。
session3要插入id=16,也會(huì)被阻塞。

按理說(shuō)鎖住id=20這行沒(méi)必要,因?yàn)槲ㄒ凰饕龗呙璧絠d=15即可確定不用繼續(xù)遍歷。但實(shí)現(xiàn)上還是這么做了,可能是個(gè)bug。

非唯一索引上存在"等值"的例子

為更好地說(shuō)明“間隙”概念。
插入記錄7
數(shù)據(jù)庫(kù)中加鎖規(guī)則有哪些

新插入的這一行c=10,即現(xiàn)在表里有兩個(gè)c=10。那么,這時(shí)索引c上的間隙是什么狀態(tài)了呢?
由于非唯一索引上包含主鍵的值,所以不可能存在“相同”兩行。

但現(xiàn)在雖然有兩個(gè)c=10,它們的主鍵值id卻不同,因此這兩個(gè)c=10記錄之間也有間隙。

看如下案例。

6

delete加鎖邏輯類(lèi)似select ... for update ,即也符合一開(kāi)始的規(guī)則。

session_1session_2session_3
begin;
delete * from t
where c=10



insert into t
values(13,13,13);(阻塞)



update t set d=d+1 where c=15;

session1遍歷時(shí)先訪問(wèn)第一個(gè)c=10:

  • 根據(jù)原則1,這里加是(c=5,id=5)到(c=10,id=10) next-key lock

  • 然后,session1 向右查找,直到碰到(c=15,id=15)這行,循環(huán)結(jié)束。根據(jù)優(yōu)化2,等值查詢(xún),向右查找到不滿(mǎn)足條件的行,所以退化成(c=10,id=10) 到 (c=15,id=15)的間隙鎖(開(kāi)區(qū)間,(c=5,id=5)和(c=15,id=15)這兩行無(wú)鎖)。

7 limit 語(yǔ)句加鎖

session_1session_2
begin;
delete * from t
where c=10 limit 2


insert into t
values(13,13,13);(阻塞)

session1 的delete語(yǔ)句加了 limit 2。你知道表t里c=10的記錄其實(shí)只有兩條,因此加不加limit 2,刪除的效果都是一樣的,但是加鎖的效果卻不同??梢钥吹?,session B的insert語(yǔ)句執(zhí)行通過(guò)了,跟案例六的結(jié)果不同。

這是因?yàn)椋咐呃锏膁elete語(yǔ)句明確加了limit 2的限制,因此在遍歷到(c=10, id=30)這一行之后,滿(mǎn)足條件的語(yǔ)句已經(jīng)有兩條,循環(huán)就結(jié)束了。

因此,索引c上的加鎖范圍就變成了從(c=5,id=5)到(c=10,id=30)這個(gè)前開(kāi)后閉區(qū)間,如下圖所示:

帶limit 2的加鎖效果

可以看到,(c=10,id=30)之后的這個(gè)間隙并沒(méi)有在加鎖范圍里,因此insert語(yǔ)句插入c=12是可以執(zhí)行成功的。

這個(gè)例子對(duì)我們實(shí)踐的指導(dǎo)意義就是,在刪除數(shù)據(jù)的時(shí)候盡量加limit。這樣不僅可以控制刪除數(shù)據(jù)的條數(shù),讓操作更安全,還可以減小加鎖的范圍。

一個(gè)死鎖的例子

前面的例子中,我們?cè)诜治龅臅r(shí)候,是按照next-key lock的邏輯來(lái)分析的,因?yàn)檫@樣分析比較方便。最后我們?cè)倏匆粋€(gè)案例,目的是說(shuō)明:next-key lock實(shí)際上是間隙鎖和行鎖加起來(lái)的結(jié)果。

你一定會(huì)疑惑,這個(gè)概念不是一開(kāi)始就說(shuō)了嗎?不要著急,我們先來(lái)看下面這個(gè)例子:

案例八的操作序列

session A 啟動(dòng)事務(wù)后執(zhí)行查詢(xún)語(yǔ)句加lock in share mode,在索引c上加了next-key lock(5,10] 和間隙鎖(10,15);

session B 的update語(yǔ)句也要在索引c上加next-key lock(5,10] ,進(jìn)入鎖等待;

然后session A要再插入(8,8,8)這一行,被session B的間隙鎖鎖住。由于出現(xiàn)了死鎖,InnoDB讓session B回滾。

你可能會(huì)問(wèn),session B的next-key lock不是還沒(méi)申請(qǐng)成功嗎?

其實(shí)是這樣的,session B的“加next-key lock(5,10] ”操作,實(shí)際上分成了兩步,先是加(5,10)的間隙鎖,加鎖成功;然后加c=10的行鎖,這時(shí)候才被鎖住的。

也就是說(shuō),我們?cè)诜治黾渔i規(guī)則的時(shí)候可以用next-key lock來(lái)分析。但是要知道,具體執(zhí)行的時(shí)候,是要分成間隙鎖和行鎖兩段來(lái)執(zhí)行的。

總結(jié)

所有案例都是在可重復(fù)讀下驗(yàn)證,可重復(fù)讀遵守兩階段鎖協(xié)議,所有加鎖的資源,都是在事務(wù)提交或者回滾的時(shí)候才釋放。

在最后的案例中,你可以清楚地知道next-key lock實(shí)際上是由間隙鎖加行鎖實(shí)現(xiàn)的。如果切換到讀提交隔離級(jí)別(read-committed)的話,就好理解了,過(guò)程中去掉間隙鎖的部分,也就是只剩下行鎖的部分。

在讀提交隔離級(jí)別下還有一個(gè)優(yōu)化,即:語(yǔ)句執(zhí)行過(guò)程中加上的行鎖,在語(yǔ)句執(zhí)行完成后,就要把“不滿(mǎn)足條件的行”上的行鎖直接釋放了,不需要等到事務(wù)提交。
讀提交隔離級(jí)別下,鎖的范圍更小,鎖的時(shí)間更短,所以不少業(yè)務(wù)也默認(rèn)使用讀提交。

在業(yè)務(wù)需要使用可重復(fù)讀時(shí),解決幻讀問(wèn)題同時(shí),最大限度提升系統(tǒng)并行處理事務(wù)的能力。

間隙鎖再加上行鎖,很容易在判斷是否會(huì)出現(xiàn)鎖等待的問(wèn)題上犯錯(cuò)。

因?yàn)殚g隙鎖在可重復(fù)讀隔離級(jí)別下才有效,本文默認(rèn)可重復(fù)讀。

以上是“數(shù)據(jù)庫(kù)中加鎖規(guī)則有哪些”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對(duì)大家有幫助,更多相關(guān)知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI