推薦（免費）：SQL教學
#間隙鎖再加上行鎖，很容易判斷是否會出現鎖等待的問題上犯錯。
因為間隙鎖定在可重複讀取隔離等級下才有效，本文預設可重複讀取。
加鎖規則
資料準備
表名：t
新增資料：(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25)
接下來的例子基本上都是配合圖片說明的，所以我建議你可以對照著文稿看，有些例子可能會“毀三觀”，也建議你讀完文章後親手實踐一下。
案例
等值查詢間隙鎖定
等值查詢的間隙鎖定
表t中無id=7，所以根據原則1，加鎖單位next-key lock，所以session A加鎖範圍(5,10]
同時根據最佳化2，等值查詢(id=7)，而id=10不滿足，next-key lock退化成間隙鎖，因此最終加鎖範圍(5,10)
所以，session B要往這個間隙裡面插入id=8的記錄會被鎖住，但是session C修改id=10這行是可以的。
非唯一索引等值鎖定
只加在非唯一索引上的鎖定
根據原則1，加鎖單位next-key lock，因此給(0,5]加next-key lock
c是
普通索引，因此只訪問c=5這條記錄不能馬上停下來，需要向右遍歷，查到c=10才放棄。根據原則2，訪問到的都要加鎖，因此要給(5,10]加next-key lock 同時符合優化2：等值判斷，向右遍歷，最後一個值不滿足c=5這個等值條件，因此退化成間隙鎖(5,10)
根據原則2 ，只有存取到的物件才會加鎖，這個查詢使用覆蓋索引，並不需要存取主鍵索引，所以主鍵索引上沒有加任何鎖，所以session B的update語句可以執行完成。
但session C要插入(7,7,7)，就會被session A的間隙鎖(5,10)鎖住。
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型，這兩個語句就是等價的吧？其實，它們並不完全等價。
現在我們就用前面提到的加鎖規則，來分析一下session A 會加什麼鎖呢？
再看看範圍查詢加鎖，你可以對照著案例三
非唯一索引範圍鎖定
| 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 設定 d =d 1 where c=15;(blocked) |
c=10定位記錄時，索引c加了(5,10] next-key lock(5,10] 和(10,15] next-key lock。
唯一索引範圍鎖定bug
前四案例用到兩個原則和兩個最佳化，再看加鎖規則bug案例。| session_2 | session_3 | |
|---|---|---|
| where id>10 and id<=15 for update; | ||
|
|
||
|
##update t | set d=d 1where id=20;(阻塞) |
| 看如下案例。 | 6 | delete加鎖邏輯類似 |
|---|---|---|
|
session_1 |
session_2 | session_3|
| begin; delete * 從 t | where c=10||
|
#session1遍歷時先存取第一個c=10： |
依原則1，這裡加是(c=5,id=5)到(c=10,id=10) next-key lock | 然後，session1 向右找，直到碰到(c=15,id=15)這行，循環結束。根據最佳化2，等值查詢，向右查找到不滿足條件的行，所以退化成(c=10,id=10) 到(c=15,id=15)的間隙鎖（開區間，(c=5,id=5)和(c=15,id=15)這兩行無鎖）。
|
7 limit 語句加上鎖定 |
session1 的delete語句加了 limit 2。你知道表t裡c=10的記錄其實只有兩條，因此加不加limit 2，刪除的效果都是一樣的，但是加鎖的效果卻不同。可以看到，session B的insert語句執行通過了，跟案例六的結果不同。
這是因為，案例七里的delete語句明確加了limit 2的限制，因此在遍歷到(c=10, id=30)這一行之後，滿足條件的語句已經有兩條，循環就結束了。
因此，索引c上的加鎖範圍就變成了從（c=5,id=5)到（c=10,id=30)這個前開後閉區間，如下圖所示：
帶有limit 2的加鎖效果
可以看到，(c=10,id=30）之後的這個間隙並沒有在加鎖範圍裡，因此insert語句插入c=12是可以執行成功的。
這個例子對我們實踐的指導意義就是，在刪除資料的時候盡量加上limit。這樣不僅可以控制刪除資料的條數，讓操作更安全，還可以減少加鎖的範圍。
一個死鎖的例子
前面的例子中，我們在分析的時候，是依照next-key lock的邏輯來分析的，因為這樣分析比較方便。最後我們再看一個案例，目的是說明：next-key lock其實是間隙鎖和行鎖加起來的結果。
你一定會疑惑，這個概念不是一開始就說了嗎？不要急，我們先來看下面這個例子：
案例八的操作序列
#session A 啟動事務後執行查詢語句加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的間隙鎖鎖住。由於出現了死鎖，InnoDB讓session B回滾。
你可能會問，session B的next-key lock不是還沒申請成功嗎？
#其實是這樣的，session B的「加next-key lock(5,10] 」操作，實際上分成了兩步，先是加(5,10)的間隙鎖，加鎖成功；然後加c=10的行鎖，這時候才被鎖住的。
#也就是說，我們在分析加鎖規則的時候可以用next-key lock來分析。但是要知道，具體執行的時候，是要分成間隙鎖和行鎖兩段來執行的。
總結
所有案例都是在可重複讀取下驗證，可重複讀取遵守兩階段鎖定協議，所有加鎖的資源，都是在事務提交或回滾的時候才釋放。
在最後的案例中，你可以清楚地知道next-key lock實際上是由間隙鎖加行鎖實現的。如果切換到讀取提交隔離級別(read-committed)的話，就好理解了，過程中去掉間隙鎖的部分，也就是只剩下行鎖的部分。
在讀取提交隔離等級下還有一個最佳化，即：語句執行過程中加上的行鎖，在語句執行完成後，就要把「不符合條件的行」上的行鎖直接釋放了，不需要等到交易提交。
讀取提交隔離等級下，鎖的範圍更小，鎖的時間更短，所以不少業務也預設使用讀取提交。
在業務需要使用可重複讀取時，解決幻讀問題同時，最大限度提升系統並行處理事務的能力。
間隙鎖再加上行鎖，很容易在判斷是否會出現鎖定等待的問題上犯錯。
因為間隙鎖定在可重複讀取隔離等級下才有效，本文預設可重複讀取。
更多相關知識敬請造訪SQL免費欄位~~
#以上是驚！一行SQL語句竟然這麼多鎖..的詳細內容。更多資訊請關注PHP中文網其他相關文章！
所以session2要更新id=20這行會被阻塞。
