mysql間隙鎖加鎖11個規則(案例分析)

mysql 間隙鎖加鎖11個規則

間隙鎖是在可重複讀隔離級別下才會生效的： next-key lock 實際上是由間隙鎖加行鎖實現的，如果切換到讀提交隔離級別 (read-committed) 的話，就好理解了，過程中去掉間隙鎖的部分，也就是隻剩下行鎖的部分。而在讀提交隔離級別下間隙鎖就沒有了，為了解決可能出現的資料和紀錄檔不一致問題，需要把binlog 格式設定為 row 。也就是說，許多公司的設定為：讀提交隔離級別加 binlog_format=row。業務不需要可重複讀的保證，這樣考慮到讀提交下運算元據的鎖範圍更小（沒有間隙鎖），這個選擇是合理
的。

next-key lock的加鎖規則

　 總結的加鎖規則裡面，包含了兩個 “ “ 原則 ” ” 、兩個 “ “ 優化 ” ” 和一個 “bug” 。
　　原則 1 ：加鎖的基本單位是 next-key lock 。 next-key lock 是前開後閉區間。
　　原則 2 ：查詢過程中存取到的物件才會加鎖。任何輔助索引上的鎖，或者非索引列上的鎖，最終都要回溯到主鍵上，在主鍵上也要加一把鎖。
　　優化 1 ：索引上的等值查詢，給唯一索引加鎖的時候， next-key lock 退化為行鎖。也就是說如果InnoDB掃描的是一個主鍵、或是一個唯一索引的話，那InnoDB只會採用行鎖方式來加鎖
　　優化 2 ：索引上（不一定是唯一索引）的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候， next-keylock 退化為間隙鎖。
　　一個 bug ：唯一索引上的範圍查詢會存取到不滿足條件的第一個值為止。

案例分析

我們以表test作為例子，建表語句和初始化語句如下：其中id為主鍵索引

CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：唯一索引等值查詢間隙鎖

由於表 test 中沒有 id=7 的記錄

根據原則 1 ，加鎖單位是 next-key lock ， session A 加鎖範圍就是 (5,10] ；

同時根據優化 2 ，這是一個等值查詢 (id=7) ，而 id=10 不滿足查詢條件， next-key lock 退化成間隙鎖，因此最終加鎖的範圍是 (5,10)

案例二：非唯一索引等值查詢鎖

這裡 session A 要給索引 col1 上 col1=5 的這一行加上讀鎖.

　　 根據原則 1 ，加鎖單位是 next-key lock ，左開右閉，5是閉上的，因此會給 (0,5]加上 next-key lock

　　要注意 c 是普通索引，因此僅存取 c=5 這一條記錄是不能馬上停下來的（可能有col1=5的其他記錄），

需要向右遍歷，查到c=10 才放棄。根據原則 2 ，存取到的都要加鎖，因此要給 (5,10] 加next-key lock 。

　　　　但是同時這個符合優化 2 ：等值判斷，向右遍歷，最後一個值不滿足 col1=5 這個等值條件，因此退化成間隙鎖 (5,10) 。

　　　　根據原則 2 ， 只有存取到的物件才會加鎖，這個查詢使用覆蓋索引，並不需要存取主鍵索引，

　　所以主鍵索引上沒有加任何鎖，這就是為什麼 session B 的 update 語句可以執行完成。

　　　　但 session C 要插入一個 (7,7,7) 的記錄，就會被 session A 的間隙鎖 (5,10) 鎖住 這個例子說明，鎖是加在索引上的。

　　　　執行 for update 時，系統會認為你接下來要更新資料，因此會順便給主鍵索引上滿足條件的行加上行鎖。

　　　　如果你要用 lock in share mode來給行加讀鎖避免資料被更新的話，就必須得繞過覆蓋索引的優化，因為覆蓋索引不會存取主鍵索引，不會給主鍵索引上加鎖

案例三：主鍵索引範圍查詢鎖

　　開始執行的時候，要找到第一個 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 A 定位查詢
id=10 的行的時候，是當做等值查詢來判斷的，而向右掃描到 id=15 的時候，用的是範圍查詢判斷。

案例四：非唯一索引範圍查詢鎖

　　在第一次用 col1=10 定位記錄的時候，索引 c 上加了 (5,10] 這個 next-key lock 後，由於索引 col1 是非唯
一索引，沒有優化規則，也就是 說不會蛻變為行鎖，因此最終 sesion A 加的鎖是，索引 c 上的 (5,10] 和
(10,15] 這兩個 next-keylock 。
　　這裡需要掃描到 col1=15 才停止掃描，是合理的，因為 InnoDB 要掃到 col1=15 ，才知道不需要繼續往後
找了。

案例五：唯一索引範圍查詢鎖 bug

　　session A 是一個範圍查詢，按照原則 1 的話，應該是索引 id 上只加 (10,15] 這個 next-key lock ，並且因
為 id 是唯一鍵，所以迴圈判斷到 id=15 這一行就應該停止了。
　　但是實現上， InnoDB 會往前掃描到第一個不滿足條件的行為止，也就是 id=20 。而且由於這是個範圍掃
描，因此索引 id 上的 (15,20] 這個 next-key lock 也會被鎖上。照理說，這裡鎖住 id=20 這一行的行為，其
實是沒有必要的。因為掃描到 id=15 ，就可以確定不用往後再找了。

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

這裡，我給表 t 插入一條新記錄：insert into t values(30,10,30);也就是說，現在表裡面有兩個c=10的行
但是它們的主鍵值 id 是不同的（分別是 10 和 30 ），因此這兩個c=10 的記錄之間，也是有間隙的。

　　這次我們用 delete 語句來驗證。注意， delete 語句加鎖的邏輯，其實跟 select ... for update 是類似的，
也就是我在文章開始總結的兩個 “ 原則 ” 、兩個 “ 優化 ” 和一個 “bug” 。
　　這時， session A 在遍歷的時候，先存取第一個 col1=10 的記錄。同樣地，根據原則 1 ，這裡加的是
(col1=5,id=5) 到 (col1=10,id=10) 這個 next-key lock 。
　　由於c是普通索引，所以繼續向右查詢，直到碰到 (col1=15,id=15) 這一行迴圈才結束。根據優化 2 ，這是
一個等值查詢，向右查詢到了不滿足條件的行，所以會退化成 (col1=10,id=10) 到 (col1=15,id=15) 的間隙
鎖。

　　這個 delete 語句在索引 c 上的加鎖範圍，就是上面圖中藍色區域覆蓋的部分。這個藍色區域左右兩邊都
是虛線，表示開區間，即 (col1=5,id=5) 和 (col1=15,id=15) 這兩行上都沒有鎖

案例七： limit 語句加鎖

　　session A 的 delete 語句加了 limit 2 。你知道表 t 裡 c=10 的記錄其實只有兩條，因此加不加 limit 2 ，刪
除的效果都是一樣的。但是加鎖效果卻不一樣
　　這是因為，案例七裡的 delete 語句明確加了 limit 2 的限制，因此在遍歷到 (col1=10, id=30) 這一行之後，
滿足條件的語句已經有兩條，迴圈就結束了。因此，索引 col1 上的加鎖範圍就變成了從（ col1=5,id=5)
到（ col1=10,id=30) 這個前開後閉區間，如下圖所示：

　　這個例子對我們實踐的指導意義就是， 在刪除資料的時候儘量加 limit 。
　　這樣不僅可以控制刪除資料的條數，讓操作更安全，還可以減小加鎖的範圍。

案例八：一個死鎖的例子

　　session A 啟動事務後執行查詢語句加 lock in share mode ，在索引 col1 上加了 next-keylock(5,10] 和
間隙鎖 (10,15) （索引向右遍歷退化為間隙鎖）；
　　session B 的 update 語句也要在索引 c 上加 next-key lock(5,10] ，進入鎖等待； 實際上分成了兩步，
先是加 (5,10) 的間隙鎖，加鎖成功；然後加 col1=10 的行鎖，因為sessionA上已經給這行加上了讀
鎖，此時申請死鎖時會被阻塞
　　然後 session A 要再插入 (8,8,8) 這一行，被 session B 的間隙鎖鎖住。由於出現了死鎖， InnoDB 讓
session B 回滾

案例九：order by索引排序的間隙鎖1

如下面一條語句
下圖為這個表的索引id的示意圖。
begin;
select * from test where id>9 and id<12 order by id desc for update;

　　首先這個查詢語句的語意是 order by id desc ，要拿到滿足條件的所有行，優化器必須先找到 “ 第
一個 id<12 的值 ” 。
　　這個過程是通過索引樹的搜尋過程得到的，在引擎內部，其實是要找到 id=12 的這個值，只是最終
沒找到，但找到了 (10,15) 這個間隙。（ id=15 不滿足條件，所以 next-key lock 退化為了間隙鎖 (10,
15) 。）
　　然後向左遍歷，在遍歷過程中，就不是等值查詢了，會掃描到 id=5 這一行，又因為區間是左開右
閉的，所以會加一個next-key lock (0,5] 。 也就是說，在執行過程中，通過樹搜尋的方式定位記錄
的時候，用的是 “ 等值查詢 ” 的方法。

案例十：order by索引排序的間隙鎖2

　　由於是 order by col1 desc ，第一個要定位的是索引 col1 上 “ 最右邊的 ”col1=20 的行。這是一個非唯一索引的等值查詢：

　　左開右閉區間，首先加上 next-key lock (15,20] 。 向右遍歷，col1=25不滿足條件，退化為間隙鎖 所以會加上間隙鎖(20,25) 和 next-key lock (15,20] 。

　　在索引 col1 上向左遍歷，要掃描到 col1=10 才停下來。同時又因為左開右閉區間，所以 next-keylock 會加到 (5,10]，

這正是阻塞session B 的 insert 語句的原因。在掃描過程中， col1=20 、 col1=15 、 col1=10 這三行都存在值，由於是 select * ，所以會在主鍵

id 上加三個行鎖。 因此， session A 的 select 語句鎖的範圍就是：
　　　　索引 col1 上 (5, 25) ；
　　　　主鍵索引上 id=15 、 20 兩個行鎖。

案例十一：update修改資料的例子-先插入後刪除

　　注意：根據 col1>5 查到的第一個記錄是 col1=10 ，因此不會加 (0,5] 這個 next-key lock 。
session A 的加鎖範圍是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。
之後 session B 的第一個 update 語句，要把 col1=5 改成 col1=1 ，你可以理解為兩步：
　　　　插入 (col1=1, id=5) 這個記錄；
　　　　刪除 (col1=5, id=5) 這個記錄。
　　通過這個操作， session A 的加鎖範圍變成了圖 7 所示的樣子：

　　好，接下來 session B 要執行 update t set col1 = 5 where col1 = 1 這個語句了，一樣地可以拆成兩步：
　　　　插入 (col1=5, id=5) 這個記錄；
　　　　刪除 (col1=1, id=5) 這個記錄。 第一步試圖在已經加了間隙鎖的 (1,10) 中插入資料，所以就被堵住
　　了。

到此這篇關於mysql間隙鎖加鎖11個規則的文章就介紹到這了,更多相關mysql間隙鎖加鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！