溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹了mysql間隙鎖加鎖的規則有哪些的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇mysql間隙鎖加鎖的規則有哪些文章都會有所收獲，下面我們一起來看看吧。
間隙鎖是在可重復讀隔離級別下才會生效的： next-key lock 實際上是由間隙鎖加行鎖實現的，如果切換到讀提交隔離級別 (read-committed) 的話，就好理解了，過程中去掉間隙鎖的部分，也就是只剩下行鎖的部分。而在讀提交隔離級別下間隙鎖就沒有了，為了解決可能出現的數據和日志不一致問題，需要把binlog 格式設置為 row 。也就是說，許多公司的配置為：讀提交隔離級別加 binlog_format=row。業務不需要可重復讀的保證，這樣考慮到讀提交下操作數據的鎖范圍更小（沒有間隙鎖），這個選擇是合理
的。
總結的加鎖規則里面，包含了兩個 “ “ 原則 ” ” 、兩個 “ “ 優化 ” ” 和一個 “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 ，才知道不需要繼續往后
找了。
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) 這兩行上都沒有鎖
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 回滾
如下面一條語句
下圖為這個表的索引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 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 兩個行鎖。
注意：根據 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間隙鎖加鎖的規則有哪些”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“mysql間隙鎖加鎖的規則有哪些”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
