共計 4534 個字符，預計需要花費 12 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

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

間隙鎖再加上行鎖，很容易在判斷是否會出現(xiàn)鎖等待的問題上犯錯。

因為間隙鎖在可重復讀隔離級別下才有效，本文默認可重復讀。

加鎖規(guī)則

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

原則 2
查找過程中訪問到的對象才會加鎖。

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

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

一個 bug
唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

數(shù)據(jù)準備

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

案例

等值查詢間隙鎖

等值查詢的間隙鎖

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

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

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

非唯一索引等值鎖

只加在非唯一索引上的鎖

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

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

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

3 主鍵索引范圍鎖

范圍查詢。

對于我們這個表 t，下面這兩條查詢語句，加鎖范圍相同嗎？

mysql select * from t where id=10 for update;
mysql select * from t where id =10 and id 11 for update;
你可能會想，id 定義為 int 類型，這兩個語句就是等價的吧？其實，它們并不完全等價。

在邏輯上，這兩條查語句肯定是等價的，但是它們的加鎖規(guī)則不太一樣。現(xiàn)在，我們就讓 session A 執(zhí)行第二個查詢語句，來看看加鎖效果。

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

開始執(zhí)行的時候，要找到第一個 id=10 的行，因此本該是 next-key lock(5,10]。根據(jù)優(yōu)化 1，主鍵 id 上的等值條件，退化成行鎖，只加了 id=10 這一行的行鎖。

范圍查找就往后繼續(xù)找，找到 id=15 這一行停下來，因此需要加 next-key lock(10,15]。

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

這里你需要注意一點，首次 session A 定位查找 id=10 的行的時候，是當做等值查詢來判斷的，而向右掃描到 id=15 的時候，用的是范圍查詢判斷。

再看看范圍查詢加鎖，你可以對照著案例三

非唯一索引范圍鎖

session_1session_2session_3begin;
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 定位記錄時，索引 c 加了(5,10] next-key lock

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

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

所以從結果上來看，sesson2 要插入（8,8,8)的這個 insert 語句時就被阻塞。

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

唯一索引范圍鎖 bug

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

session_1session_2session_3begin;
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 是范圍查詢

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

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

所以 session2 要更新 id=20 這行會被阻塞。
session3 要插入 id=16，也會被阻塞。

按理說鎖住 id=20 這行沒必要，因為唯一索引掃描到 id=15 即可確定不用繼續(xù)遍歷。但實現(xiàn)上還是這么做了，可能是個 bug。

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

為更好地說明“間隙”概念。
插入記錄 7

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

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

看如下案例。

6

delete 加鎖邏輯類似 select … for update，即也符合一開始的規(guī)則。

session_1session_2session_3begin;
delete * from t
where c=10

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

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

session1 遍歷時先訪問第一個 c =10：

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

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

7 limit 語句加鎖

session_1session_2begin;
delete * from t
where c=10 limit 2

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

session1 的 delete 語句加了 limit 2。你知道表 t 里 c =10 的記錄其實只有兩條，因此加不加 limit 2，刪除的效果都是一樣的，但是加鎖的效果卻不同。可以看到，session B 的 insert 語句執(zhí)行通過了，跟案例六的結果不同。

這是因為，案例七里的 delete 語句明確加了 limit 2 的限制，因此在遍歷到 (c=10, id=30) 這一行之后，滿足條件的語句已經(jīng)有兩條，循環(huán)就結束了。

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

帶 limit 2 的加鎖效果

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

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

一個死鎖的例子

前面的例子中，我們在分析的時候，是按照 next-key lock 的邏輯來分析的，因為這樣分析比較方便。最后我們再看一個案例，目的是說明：next-key lock 實際上是間隙鎖和行鎖加起來的結果。

你一定會疑惑，這個概念不是一開始就說了嗎？不要著急，我們先來看下面這個例子：

案例八的操作序列

session A 啟動事務后執(zhí)行查詢語句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和間隙鎖(10,15)；

session B 的 update 語句也要在索引 c 上加 next-key lock(5,10]，進入鎖等待；

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

你可能會問，session B 的 next-key lock 不是還沒申請成功嗎？

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

也就是說，我們在分析加鎖規(guī)則的時候可以用 next-key lock 來分析。但是要知道，具體執(zhí)行的時候，是要分成間隙鎖和行鎖兩段來執(zhí)行的。

總結

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

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

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

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

間隙鎖再加上行鎖，很容易在判斷是否會出現(xiàn)鎖等待的問題上犯錯。

因為間隙鎖在可重復讀隔離級別下才有效，本文默認可重復讀。

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