站長資訊網
推薦（免費）：SQL教程
間隙鎖再加上行鎖，很容易在判斷是否會出現(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_1 | session_2 | session_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定位記錄時，索引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_1 | session_2 | session_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是范圍查詢
- 按原則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_1 | session_2 | session_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遍歷時先訪問第一個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_1 | session_2 |
|---|---|
| begin; 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)這一行之后，滿足條件的語句已經有兩條，循環(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)鎖等待的問題上犯錯。
因為間隙鎖在可重復讀隔離級別下才有效，本文默認可重復讀。
