MySQL innodb 的間隙鎖定（next-key locking）

擊水三千里發表於2019-02-26
MySql

行鎖的分類

MySQL InnoDB支援三種行鎖定方式：
行鎖（Record Lock）：鎖直接加在索引記錄上面。
間隙鎖（Gap Lock）：鎖加在不存在的空閒空間，可以是兩個索引記錄之間，也可能是第一個索引記錄之前或最後一個索引之後的空間。
Next-Key Lock：行鎖與間隙鎖組合起來用就叫做Next-Key Lock。InnoDB預設加鎖方式是next-key 鎖。
 

什麼是間隙鎖

當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖。

 

innodb自動使用間隙鎖的條件

（1）必須在RR級別下
（2）檢索條件必須有索引（沒有索引的話，mysql會全表掃描，那樣會鎖定整張表所有的記錄，包括不存在的記錄，此時其他事務不能修改不能刪除不能新增）

 

如何開啟和關閉間隙鎖

隙鎖在InnoDB的唯一作用就是防止其它事務的插入操作，以此來達到防止幻讀的發生，所以間隙鎖不分什麼共享鎖與排它鎖。 
要禁止間隙鎖的話，可以把隔離級別降為讀已提交，或者開啟引數innodb_locks_unsafe_for_binlog。

檢視是否開啟間隙鎖：
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
innodb_locks_unsafe_for_binlog：預設值為0，即啟用gap lock。

這個引數最主要的作用就是控制innodb是否對gap加鎖。
但是，這一設定變更並不影響外來鍵和唯一索引（含主鍵）對gap進行加鎖的需要。
開啟innodb_locks_unsafe_for_binlog的REPEATABLE-READ事務隔離級別，很大程度上已經蛻變成了READ-COMMITTED。


關閉間隙鎖(gap lock)方法:
在my.cnf裡面的[mysqld]新增
[mysqld]
innodb_locks_unsafe_for_binlog = 1
重啟MySQL後生效. 

 

舉例來說明間隙鎖

案例一：

````
session 1:
start  transaction ;
select  * from news where number=4 for update ;

session 2:
start  transaction ;
insert into news value(2,4);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,5);#（阻塞）
insert into news value(7,5);#（執行成功）
insert into news value(9,5);#（執行成功）
insert into news value(11,5);#（執行成功）
````

間隙鎖鎖定的區間為（2，4），（4，5），即記錄（id=1,number=2）和記錄（id=3,number=4）之間間隙會被鎖定，記錄（id=3,number=4）和記錄（id=6,number=5）之間間隙被鎖定。

因此記錄（id=2,number=4），（id=2,number=2），（id=4,number=4），（id=4,number=5）正好處在（id=3,number=4）和（id=6,number=5）之間，所以插入不了，需要等待鎖的釋放，而記錄(id=7,number=5)，（id=9,number=5），（id=11,number=5）不在上述鎖定的範圍內，因此都會插入成功。

 

案例二：

````
session 1:
start  transaction ;
select  * from news where number=13 for update ;

session 2:
start  transaction ;
insert into news value(11,5);#(執行成功)
insert into news value(12,11);#(執行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
update news set id=14 where number=11;#(阻塞)
update news set id=11 where number=11;#(執行成功)
````

 檢索條件number=13,向左取得最靠近的值11作為左區間，向右由於沒有記錄因此取得無窮大作為右區間，因此，session 1的間隙鎖的範圍（11，無窮大）

 

案例三：

````
session 1:
start  transaction ;
select  * from news where number=5 for update;

session 2:
start  transaction ;
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(5,5);#(阻塞)
insert into news value(7,11);#(阻塞)
insert into news value(9,12);#(執行成功)
insert into news value(12,11);#(阻塞)
update news set number=5 where id=1;#(阻塞)
update news set id=11 where number=11;#(阻塞)
update news set id=2 where number=4 ;#（執行成功）
update news set id=4 where number=4 ;#（阻塞）
````

檢索條件number=5,向左取得最靠近的值4作為左區間，向右取得11為右區間，因此，session 1的間隙鎖的範圍（4，5），（5，11），如下圖所示：

案例四： 

session 1:
start  transaction;
select * from news where number>4 for update;

session 2:
start  transaction;
update news set id=2 where number=4 ;#(執行成功)
update news set id=4 where number=4 ;#(阻塞)
update news set id=5 where number=5 ;#(阻塞)
insert into news value(2,3);#(執行成功)
insert into news value(null,13);#(阻塞)

檢索條件number>4,向左取得最靠近的值4作為左區間，向右取無窮大，因此，session 1的間隙鎖的範圍（4，無窮大），如下圖所示：

 

相關文章