InnoDB鎖機制之行級鎖（四）臨鍵鎖 (Next-Key Locks)

一、前言

Next-key鎖是記錄鎖和間隙鎖的組合，可以了解為一種特殊的間隙鎖。通過臨建鎖可以解決幻讀的問題。

每個資料行上的非唯一索引列上都會存在一把臨鍵鎖，當某個事務持有該資料行的臨鍵鎖時，會鎖住一段左開右閉區間的資料。

當InnoDB 搜尋或掃描索引時，InnoDB在它遇到的索引記錄上所設定的鎖就是next-key lock，它會鎖定索引記錄本身以及該索引記錄前面的gap。

二、加鎖原則

1. 加鎖的基本機關是next-key lock。next-key lock是左開右閉區間。
2. 查找過程中通路到的對象才會加鎖。
3. 索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖。
4. 索引上的等值查詢，向右周遊時且最後一個值不滿足等值條件的時候，next-key lock退化為間隙鎖。
5. 唯一索引上的範圍查詢會通路到不滿足條件的第一個值為止。

三、slock in share mode 與 for update 的差別

• lock in share mode 加的是讀鎖(共享鎖) , lock in share mode 隻鎖覆寫索引,也就說：lock in share mode 隻鎖非主鍵索引對應的B+樹中的索引内容。
• for update 加的是寫鎖(排它鎖) , for update 的加鎖内容是非主鍵索引樹上符合條件的索引項,以及這些索引項對應的主鍵索引樹上相應的索引項。在兩個索引上都加了鎖。
• 共同點: 兩者都屬于目前讀範圍。

四、Next-Key Lock案例示範

1、資料準備

create table test_NK(
id int primary key,
num1 int,
num2 int,
key idx_num1(num1)
);

insert into test_NK values(5,5,5),(10,10,10),(20,20,20),(25,25,25);           

2、在更新時,不僅對下面的五條資料加行鎖, 還會對中間的取值範圍增加6個臨鍵鎖, 該索引可能被鎖住的範圍如下

(-∞,5],(5,10],(10,15],(15,20],(20,25],(25,+∞)           

測試1: 等值查詢普通索引

-- 事務1,開啟事務,查詢 num1 = 5的記錄, 并添加共享鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from test_NK where num1 = 5 lock in share mode;
+----+
| id |
+----+
| 5 |
+----+
1 row in set (0.01 sec)

-- 事務2,執行插入操作
Database changed
mysql> update test_NK set num2 = num2+1 where id = 5; -- 執行成功
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_NK values(7,7,7); --阻塞
mysql> insert into test_NK values(4,4,4); --阻塞
mysql> insert into test_NK values(10,10,10); -- 主鍵已存在
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'           

分析上面部分SQL語句發生阻塞的原因

1. 初步加鎖，加鎖的範圍是（0,5]，（5,10]的臨鍵鎖。
2. 根據原則4,由于查詢是等值查詢,并且最後一個值不滿足查詢要求,所next-key退化 為間隙鎖,最終鎖定的範圍是(0,10)。
3. 因為加鎖範圍是(0,10) , 是以 insert id = 4和 id=7都阻塞了。
4. 因為num1是普通索引列, lock in share mode 因為覆寫索引,沒有對主鍵索引加 鎖,是以update操作正常執行。

測試2: 範圍查詢 唯一索引

-- 事務1 開啟事務,執行一個條件是id的範圍查詢
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_NK where id > 10 and id < 15 for update;
Empty set (0.00 sec)

-- 視窗2, 每一條SQL都是一個獨立事務
-- 間隙範圍: (-∞,5],(5,10],(10,15],(15,20],(20,25],(25,+∞)
mysql> insert into test_NK values(9,9,9); -- 正常
mysql> insert into test_NK values(12,12,12); -- 阻塞
mysql> insert into test_NK values(14,14,14); -- 阻塞
mysql> insert into test_NK values(16,16,16); -- 阻塞
mysql> insert into test_NK values(17,17,17); -- 阻塞
mysql> insert into test_NK values(18,18,18); -- 阻塞
mysql> insert into test_NK values(19,19,19); -- 阻塞
mysql> insert into test_NK values(20,20,20); --阻塞
mysql> insert into test_NK values(21,21,21); --成功
Query OK, 1 row affected (0.00 sec)           

分析上面部分SQL語句發生阻塞的原因:

根據原則5 唯一索引上的範圍查詢,會通路到不滿足條件的第一個值為止。是以鎖定範圍是(10,15],(15,20] ,也就是(10,20)。

測試3: 範圍查詢 普通索引

-- 事務1 開啟事務,通過索引字段num1 進行範圍查詢,并加排它鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_NK where num1 >= 10 and num1 < 15 for update;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
| 10 | 10 | 10 |
+----+------+------+
  
-- 視窗2, 每一條SQL都是一個獨立事務
-- 間隙範圍: (-∞,5],(5,10],(10,15],(15,20],(20,25],(25,+∞)
mysql> insert into test_NK values(9,9,9);-- 阻塞
mysql> insert into test_NK values(13,13,13);-- 阻塞
mysql> insert into test_NK values(16,16,16);-- 阻塞
mysql> insert into test_NK values(19,19,19);-- 阻塞
mysql> insert into test_NK values(21,21,21); -- 正常
Query OK, 1 row affected (0.00 sec)           

分析上面部分SQL語句發生阻塞的原因

InnoDB存儲引擎會對普通索引的下一個鍵值加上gap lock.是以原本臨鍵鎖鎖定是(5,10],(10,15]。而15下一個鍵值是(15,20],是以插入5~20之間的值的時候都會被鎖定，要求等待。

下文将分析幻讀是如何産生的，怎麼根據臨鍵鎖進行處理的；