mysql間隙鎖加鎖的規則有哪些

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 才放棄。根據第二個原則，需要對所有訪問進行加鎖，因此需要給區間(5,10]加上next-key lock。

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

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

因此，在主鍵索引上沒有新增任何鎖，這解釋了為什麼會話B的更新語句可以執行成功。

　　　　但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

#　　首先這個查詢語句的語意是order by id desc ，要拿到所有滿足條件的行，優化器必須先找到「 第
一個id　　這個過程是透過索引樹的搜尋過程得到的，在引擎內部，其實是要找到 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 鎖定，形成(15,20] 區間。向右遍歷，col1=25 不滿足條件，退化為間隙鎖所以會加上間隙鎖(20,25) 和next-key lock (15,20] 。

遍歷到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間隙鎖加鎖的規則有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章！