Innodb 間隙鎖實戰

鎖概念

InnoDB 存儲引擎包含了三種行鎖的算法，分別如下所示：

• Record Lock：行鎖，針對的是單行記錄；

• Gap Lock：間隙鎖，鎖定的是一個範圍，但是不包含記錄本身；

• Next-Key Lock：其實就是行鎖 + 間隙鎖，包含了記錄本身和範圍；

爲什麼需要間隙鎖

數據庫一般都有四種隔離級別，其中最常用的就是：已提交讀 (Read committed) 和可重複讀(Repeatable read)；在已提交讀隔離級別下會出現不可重複讀的現象，而在可重複讀隔離級別下會出現幻讀(Phantom Read) 的現象；

幻讀：同一事務下，連續執行兩次同樣的 SQL 可能會導致不同的結果；

Innodb 引擎在可重複讀隔離級別下並不會出現幻讀的現象，這主要是因爲 Innodb 提供了多版本併發控制 MVCC 和間隙鎖；常見的快照讀其實就是使用的 MVCC，而當前讀就使用了間隙鎖；

以下實例有兩點說明：

• Innodb 的可重複讀隔離級別下，對當前讀使用了間隙鎖來解決幻讀的問題，所以下面的實例都是基於默認隔離級別 RR；

• Innodb 的鎖機制都依賴索引，所以下面的實例圍繞索引來展開；

實戰

無索引的情況

首先創建一個無索引的表，並初始化數據：

mysql> create table t1(a int);
mysql> insert into t1 values(1),(3),(5);

啓動事務 1，執行當前讀：

mysql> begin;
mysql> select * from t1 where a=3 for update;

以上事務沒有提交，再啓動事務 2，以下語句都被阻塞：

mysql> select * from t1 where a=3 for update;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(5);
mysql> insert into t1 values(7);

但是這時候去執行快照讀還是可以的：

mysql> select * from t1 where a=3;

可以發現在沒有索引的情況下，除了快照讀什麼都幹不了，感覺像是表被鎖住了，表鎖分爲讀和寫鎖，在寫鎖的情況下快照讀同樣被鎖住，而在讀鎖的情況下可以使用快照讀，類似上面無索引的情況；

mysql> lock table t1 read;  ## 讀鎖
mysql> lock table t1 write;  ## 寫鎖
mysql> unlock tables;  ## 解鎖

那是不是無索引的情況下就使用了表鎖那，可以通過如下命令進行查看，首先看一下在表鎖的情況下執行插入操作：

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+
| Id  | User | Host            | db   | Command | Time | State                        | Info                     |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+
|  75 | ODBC | localhost:65316 | test | Query   |   98 | Waiting for table level lock | insert into t1 values(7) |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+

可以發現狀態一欄顯示Waiting for table level lock，表示當前插入語句在等待表鎖；再看一下無索引的情況：

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+--------+--------------------------+
| Id  | User | Host            | db   | Command | Time | State  | Info                     |
+-----+------+-----------------+------+---------+------+--------+--------------------------+
|  75 | ODBC | localhost:65316 | test | Query   |    6 | update | insert into t1 values(7) |
+-----+------+-----------------+------+---------+------+--------+--------------------------+

** 總結：** 以上狀態並不是在等待表鎖，其實 InnoDB 在沒有任何索引的情況下，會使用隱式的主鍵來進行鎖定。

普通索引的情況

首先創建一個普通索引的表，並初始化數據：

mysql> create table t2 (a int,key(a));
mysql> insert into t2 values(1),(3),(5);

啓動事務 1，執行當前讀：

mysql> begin;
mysql> select * from t2 where a=3 for update;

啓動事務 2，以下語句都被阻塞：

mysql> select * from t2 where a=3 for update;
mysql> insert into t2 values(1);
mysql> insert into t2 values(2);
mysql> insert into t2 values(4);

以下語句不會被阻塞：

mysql> insert into t2 values(5);
mysql> insert into t2 values(7);

mysql> select * from t2 where a=1 for update;
mysql> select * from t2 where a=5 for update;

總結：可以發現在使用普通索引的情況下，鎖定包含了行鎖和間隙鎖，行鎖就是a=3這行，間隙鎖包含了(1,3)和(3,5)，注意這裏兩邊都是開區間，所以當前讀a=1和a=5都可以成功；但是在插入數據的時候可以發現a=5可以插入，但是a=1不能插入，這是因爲對Insert操作會檢查插入的下一條記錄是否被鎖定，如果鎖定則無法執行，否則可以執行。

唯一索引的情況

首先創建一個唯一索引的表，並初始化數據：

mysql> create table t3 (a int,unique key(a));
mysql> insert into t3 values (1),(3),(5);

啓動事務 1，執行當前讀：

mysql> begin;
mysql> select * from t3 where a=3 for update;

啓動事務 2，以下語句都被阻塞：

mysql> select * from t3 where a=3 for update;

以下語句不會被阻塞：

mysql> select * from t3 where a=1 for update;
mysql> insert into t3 values(2);
mysql> insert into t3 values(4);
mysql> insert into t3 values(7);

總結：可以發現當是唯一索引的時候，這時候會降級爲行鎖，只會鎖定當前記錄，其他插入 SQL 都不會被阻塞；同樣主鍵索引效果也是一樣的。

無索引 + 普通索引的情況

首先創建一個擁有無索引和普通索引的表，並初始化數據：

mysql> create table t4 (a int,b int,key(a));
mysql> insert into t4 values(1,1),(5,5),(9,9);

因爲存在多個字段的情況，所以這時候的加鎖情況就和查詢的條件有關了：

條件爲 a 字段

啓動事務 1，執行當前讀：

mysql> begin;
mysql>  select * from t4 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

同時查出了 a=5 和 b=5 這條記錄，所以會根據無索引和普通索引的情況加鎖；啓動事務 2，以下語句都被阻塞：

mysql> select * from t4 where a=5 for update;
mysql> insert into t4 values(1,5);
mysql> insert into t4 values(2,5);
mysql> insert into t4 values(8,5);

mysql> select * from t4 where b=1 for update;
mysql> select * from t4 where b=2 for update;
mysql> select * from t4 where b=9 for update;
mysql> select * from t4 where b=11 for update;

以下語句不會被阻塞：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

這裏 a 上有普通索引，所以效果和直接使用普通索引類似；但是同時查出來的結果還有 b，因爲 b 上是無索引的，所以關於 b 的一系列操作都被阻塞了；

條件爲 b 字段

啓動事務 1，執行當前讀：

mysql> begin;
mysql> select * from t4 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

啓動事務 2，以下語句都被阻塞：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

mysql> select * from t4 where a=1 for update;
mysql> select * from t4 where a=9 for update;

可以發現條件改變後，原來a=5條件下可以插入的數據，這b=5條件下不能插入；並且 a 條件下的兩個開區間 1 和 9 也無法查詢了；應該是使用了隱式的主鍵來進行鎖定，整個表被鎖住了；

條件爲 a+b 字段

啓動事務 1，執行當前讀：

mysql> begin;
mysql> select * from t4 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

啓動事務 2，阻塞的 SQL 同條件爲 a 的情況，Mysql 執行計劃會優先使用有索引的字段；以下 SQL 都可以執行：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

通過上面的實例，我們大概可以下面兩件事：

• 具體使用哪些鎖和我們使用的查詢有很大關係；

• 查出來記錄中的多個字段都會啓動鎖機制，就算查詢條件裏面沒有這個字段；

唯一索引 + 普通索引的情況

首先創建一個擁有唯一索引和普通索引的表，並初始化數據：

mysql> create table t5 (a int,b int,unique key(a),key(b));
mysql> insert into t5 values(1,1),(5,5),(9,9);

條件爲 a 字段

mysql> begin;
mysql> select * from t5 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

這種情況基本和使用唯一索引一樣，以下 SQL 都可以執行：

mysql> insert into t5 values(2,2);
mysql> insert into t5 values(10,2);
mysql> select * from t5 where a=1 for update;

阻塞的 SQL 主要包含如下：

mysql> select * from t5 where a=5 for update;
mysql> select * from t5 where b=5 for update;

條件爲 b 字段

mysql> begin;
mysql>  select * from t5 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

這種情況基本和使用普通索引一樣，以下 SQL 會阻塞：

mysql> insert into t5 values(2,3);
mysql> insert into t5 values(2,7);
mysql> insert into t5 values(2,1);

以下插入 SQL 不會阻塞：

mysql> insert into t5 values(2,9);

條件爲 a+b 字段

mysql> begin;
mysql> select * from t5 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

此種情況基本和使用條件 a 一樣，優先使用唯一索引，可以查看執行計劃：

mysql> explain select * from t5 where b=5 and a=5 for update;
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t5    | const | a,b           | a    | 5       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+

以上實例我們發現在多索引的情況下，要看執行計劃具體使用的什麼索引，纔會使用什麼類型的鎖；

組合索引的情況

首先創建擁有組合索引的表，並初始化數據：

mysql> create table t6 (a int,b int,key(a,b));
mysql> insert into t6 values(1,1),(5,5),(9,9);

條件爲 a 字段

首先開啓事務一，執行如下 SQL：

mysql> begin;
mysql> select * from t6 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

啓動事務 2，以下語句都被阻塞：

mysql> insert into t6 values(1,2);
mysql> insert into t6 values(2,2);
mysql> insert into t6 values(7,2);
mysql> insert into t6 values(9,2);

除了 (9,2) 這條記錄其他和使用普通索引沒有差別，這裏 (9,2) 被阻塞的原因是因爲組合索引的存在，導致 (9,9) 記錄在 (9,2) 記錄的後面，索引會被鎖住，同理如果是 (9,10) 是不會被鎖住的；

mysql> insert into t6 values(9,10);
mysql> insert into t6 values(10,2);

條件爲 b 字段

首先開啓事務一，執行如下 SQL：

mysql> begin;
mysql> select * from t6 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

啓動事務 2，以下語句都被阻塞：

mysql> insert into t6 values(9,2);
mysql> insert into t6 values(9,10);
mysql> insert into t6 values(10,2);

這個其實也好理解，因爲使用的是組合索引，會按照最左匹配原則，這時候條件 b=5 其實是無法使用索引的，所以這時候和無索引的情況是一樣的，以上語句都會被鎖住；

條件爲 a+b 字段

首先開啓事務一，執行如下 SQL：

mysql> begin;
mysql> select * from t6 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

啓動事務 2，阻塞和不阻塞的情況基本一致，執行計劃會走組合索引；

總結

經過以上的實例測試大致做了以下幾點總結：

• 無索引的情況會使用隱式的主鍵來進行鎖定，效果就是整個表被鎖住了；

• 普通索引的情況下會使用間隙鎖，對當前值前後添加開區間的間隙鎖，並且對當前值添加行鎖；對 Insert 操作會檢查插入的下一條記錄是否被鎖定；

• 唯一索引的情況，直接使用行鎖，只對當前行加鎖；

• 具體使用哪些鎖和我們使用的查詢有很大關係；

• 查出來記錄中的多個字段都會啓動鎖機制，就算查詢條件裏面沒有這個字段；

• 對於查詢條件有多個的情況，會根據執行計劃選擇索引，然後選擇對應的鎖機制。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/1yy7tDB7w1hKLu6S_VymwA