InnoDB常用鎖總結（行鎖、間隙鎖、臨鍵鎖、表鎖）

Hello-Brand發表於2024-04-16

資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能
資料庫系列：字首索引和索引長度的取捨
資料庫系列：MySQL引擎MyISAM和InnoDB的比較
資料庫系列：InnoDB下實現高併發控制
資料庫系列：事務的4種隔離級別
資料庫系列：RR和RC下，快照讀的區別
資料庫系列：MySQL InnoDB鎖機制介紹
資料庫系列：MySQL不同操作分別用什麼鎖？
資料庫系列：業內主流MySQL資料中介軟體梳理
資料庫系列：大廠使用資料庫中介軟體解決什麼問題？
資料庫系列：索引失效場景總結

1 背景

我們在之前的一篇文章《資料庫系列：MySQL InnoDB鎖機制介紹》中介紹過InnodB引擎下幾種常見鎖的機制和原理。而在實際的select...for update操作中，鎖影響的範圍還是有區別的，下面就詳細討論下select 操作中的加鎖規則。

2 回顧常見的鎖型別

★InnoDB預設的事務隔離級別為可重複讀(Repeated Read, RR)，我們當下的所有介紹都是基於這個隔離級別為前提的。

記錄鎖(Record Locks)：鎖定單一行記錄，InnoDB 使用記錄鎖來實現行級鎖，這樣允許多個事務併發訪問不同的行。
間隙鎖(Gap Locks)：InnoDB 的特性，用於鎖定一個範圍，但不包括實際的記錄。這主要用於防止幻讀（Phantom Reads）。
臨鍵鎖(Next-Key Locks)：InnoDB 儲存引擎的一種鎖定機制，在執行查詢語句時，根據查詢條件所鎖定的一個範圍。這個範圍中包含有間隙鎖和記錄鎖。它的設計目的是為了解決幻讀（Phantom Reads）。

2.1 記錄鎖(Record Locks)

記錄鎖一般在使用主鍵或者唯一索引進行查詢時體現

記錄鎖，它封鎖索引記錄，例如：

select * from table where id=5 for update;

它會在id=1的索引記錄上加鎖，以阻止其他事務插入，更新，刪除id=1的這一行。

需要說明的是：

select * from table where id=5;

則是快照讀(SnapShot Read)，它並不加鎖，快照讀可以參考作者這篇文章：資料庫系列：RR和RC下，快照讀的區別

2.2 間隙鎖(Gap Locks)

間隙鎖通常在不使用唯一索引進行範圍查詢時出現

間隙鎖，它封鎖索引記錄中的間隔，或者第一條索引記錄之前的範圍，又或者最後一條索引記錄之後的範圍。
延續上面的那個例子繼續演示：

# 表結構
users (Id PK, Name , Company);

# 表中包含四條記錄
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Elison, Oracle

執行SQL語句如下：

select * from users
    where id between 7 and 13 
    for update;

-- 假設我們要刪除id在7到13之間的所有使用者記錄（不包括id=7和id=13）  
DELETE FROM users WHERE id BETWEEN 7 AND 13;

這樣的話，會封鎖資料的區間，以防止其他事務插入id=8的記錄。
假設沒有間隙鎖，則可能夠插入成功，而之前的select事務，會發現檢索的結果集莫名多了一條記錄，即幻影資料。
所以間隙鎖主要目的用於防止幻讀（Phantom Reads），避免其他事務在間隔中插入資料，導致『不可重複讀』。

如果把事務的隔離級別降級為讀提交(Read Committed, RC)，對，就是網際網路最常用的隔離級別，間隙鎖則會自動失效。

2.3 臨鍵鎖(Next-Key Locks)

臨鍵鎖（Next-Key Locks）是資料庫管理系統InnoDB中的一種重要鎖定機制。這種鎖是查詢時根據查詢條件鎖定的一個範圍，這個範圍包括間隙鎖和記錄鎖，左開右閉，即不鎖住左邊界，但會鎖住右邊界。臨鍵鎖的主要設計目的是為了解決所謂的“幻讀”問題。

# 左開右閉 示例
(-infinity, 1]
(1, 7]
(7, +infinity)

依然沿用上面的例子，InnoDB引擎，RR隔離級別：

-- 建立一個示例表  
CREATE TABLE users (  
    Id INT PRIMARY KEY,  
    Name VARCHAR(255) NOT NULL,  
    Company VARCHAR(255) NOT NULL,  
);  
  
-- 插入一些示例資料  
INSERT INTO users (id, name, company) VALUES (1, 'Alice', 'ali');
INSERT INTO users (id, name, company) VALUES (2, 'Brand', 'tencent');
INSERT INTO users (id, name, company) VALUES (3, 'Charlie', 'baidu');
  
-- 開始一個事務，並使用臨鍵鎖查詢資料  
START TRANSACTION;  
SELECT * FROM users WHERE id > 1 FOR UPDATE;  
  
-- 在另一個事務中嘗試插入新資料，將會被阻塞直到第一個事務釋放鎖 
START TRANSACTION;  
INSERT INTO users (id, name, age) VALUES (4, 'David', 30);  
COMMIT;  
  
-- 第一個事務提交後，第二個事務可以繼續執行插入操作  
COMMIT;

臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)，在事務隔離級別為可重複讀的情況下，InnoDB儲存引擎預設使用臨鍵鎖。這種鎖提供了一種有效的機制來保證在併發環境中資料的完整性和一致性。
如果把事務的隔離級別降級為RC，臨鍵鎖則也會失效。

3 不同select操作的加鎖規則

3.0 前置條件

# 表結構(姓名、公司、工號)
userinfo (Id PK, username, company, usercode);

# 表中包含四條記錄
5, Gates, Microsoft, 24
7, Bezos, Amazon,35
11, Jobs, Apple,37
14, Elison, Oracle,38

3.1 主鍵檢索

1. 記錄存在的情況

# 5是存在的記錄，行鎖
mysql> select * from userinfo where id=5 for update;

mysql> update userinfo set username = "Brand" where id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# X 排他鎖
# RECORD 記錄鎖
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X             | RECORD   |
+---------------+-------------+

2. 記錄不存在的情況

# 6是不存在的記錄，間隙鎖，鎖住的區間為（5,7）,對應上面的前置條件
mysql> select * from userinfo where id = 6 for update;

mysql>  insert into user values(6, 'Brand', 'Ali',100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactio

# X 排他鎖 + Gap 間隙鎖
# RECORD 記錄鎖
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

3.2 唯一索引檢索

與主鍵檢索結果一致，因為這兩種都是可以唯一確定索引值和區間範圍的。

3.3 普通索引檢索