驚！一行SQL語句竟然這麼多鎖..-SQL-PHP中文網

驚！一行SQL語句竟然這麼多鎖..

推薦（免費）：SQL教學

#間隙鎖再加上行鎖，很容易判斷是否會出現鎖等待的問題上犯錯。

因為間隙鎖定在可重複讀取隔離等級下才有效，本文預設可重複讀取。

加鎖規則

原則1
加鎖的基本單位是next-key lock，前開後閉區間。
原則2
尋找過程中存取到的物件才會加鎖。
優化1
索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖。
優化2
索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock退化為間隙鎖定。
一個bug
唯一索引上的範圍查詢會存取到不滿足條件的第一個值為止。

資料準備

表名：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這行是可以的。

非唯一索引等值鎖定

只加在非唯一索引上的鎖定
##session A要給索引c的c=5這行加讀鎖定
根據原則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)鎖住。

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

這例說明，鎖是加在索引上的；同時，它給我們的指導是，如果你要用lock in share mode來給行加讀鎖避免資料被更新的話，就必須要繞過覆蓋索引的最佳化，在查詢欄位中加入索引中不存在的欄位。例如，將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型，這兩個語句就是等價的吧？其實，它們並不完全等價。

在邏輯上，這兩個查語句一定是等價的，但是它們的加鎖規則不太一樣。現在，我們就請session A執行第二個查詢語句，來看看加鎖效果。

圖3 主鍵索引上範圍查詢的鎖定

現在我們就用前面提到的加鎖規則，來分析一下session A 會加什麼鎖呢？

開始執行的時候，要找出第一個id=10的行，因此本該是next-key lock(5,10]。根據最佳化1，主鍵id上的等值條件，退化成行鎖，只加了id=10這一行的行鎖。

範圍查找就往後繼續找，找到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 設定 d =d 1 where c=15;(blocked)

#session1第一次用c=10定位記錄時，索引c加了(5,10] next-key lock
c是非唯一索引，無最佳化規則，即不會退化為行鎖定
因此最終sesion1加鎖為c的(5,10] 和(10,15] next-key lock。

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

非唯一索引要掃到c=15，才知道無需繼續往後遍歷。

唯一索引範圍鎖定bug

前四案例用到兩個原則和兩個最佳化，再看加鎖規則bug案例。

##session_1begin; select * from t set d=d 1

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

session1是範圍查詢

依原則1，索引id只加

(10,15] next-key lock
，因為id是唯一鍵，所以迴圈判斷到

id=15

這行就該停止遍歷。

但實作上，InnoDB會繼續掃描到第一個不滿足條件的行，即id=20

，且由於這是範圍掃描，因此id上的

(15,20] next-key lock
也會被鎖。
所以session2要更新id=20這行會被阻塞。

session3要插入id=16，也會被阻塞。

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

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

為更好地說明「間隙」概念。

插入記錄7

#新插入的這一行c=10，也就是現在表裡有兩個c=10。那麼，這時索引c上的間隙是什麼狀態了呢？

session_2	session_3
where id>10 and id<=15 for update;

	##update t	where id=20;(阻塞)

由於非唯一索引上包含主鍵的值，所以不可能存在「相同」兩行。但現在雖然有兩個c=10，它們的主鍵值id卻不同，因此這兩個c=10記錄之間也有間隙。看如下案例。6delete加鎖邏輯類似select ... for update ，即也符合一開始的規則。 session_3where c=10


session_1	session_2
begin; delete * 從 t

insert into t

update t set d=d 1 where c=15;然後，session1 向右找，直到碰到(c=15,id=15)這行，循環結束。根據最佳化2，等值查詢，向右查找到不滿足條件的行，所以退化成(c=10,id=10) 到(c=15,id=15)的間隙鎖（開區間，(c=5,id=5)和(c=15,id=15)這兩行無鎖）。session_1


#session1遍歷時先存取第一個c=10：	依原則1，這裡加是(c=5,id=5)到(c=10,id=10) next-key lock
7 limit 語句加上鎖定

###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語句執行通過了，跟案例六的結果不同。

這是因為，案例七里的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] ，進入鎖定等待；