MySQL鎖機制全解析

MYSQL存儲引擎支持的鎖

InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖。
MyISAM采用表級鎖(table-level locking)

鎖的基本分類

1. 按照鎖的使用方式 , Mysql的鎖大致分為共享鎖和排它鎖

a. 共享鎖(S)

共享鎖，Share lock，又稱為讀鎖，簡稱S鎖，顧名思義，共享鎖就是多個事務對于同一數據可以共享一把鎖，都能訪問到數據，但是只能讀不能修改

當前事務加鎖之后，其他事務也可以加鎖

select ... lock in share mode;select … for shore;  （mysql8.0）

共享鎖案例：

數據準備：

-- 查詢事務隔離級別
SELECT @@transaction_isolation;-- 首先檢查當前事務是否自動提交
SELECT @@autocommit;-- 狀態說明
-- 1 表示自動提交已啟用（每個SQL語句都會在執行后立即提交）。
-- 0 表示自動提交已禁用（需要手動執行 COMMIT; 才能提交事務）。
-- 開啟自動提交：
SET autocommit = 1;
-- 關閉自動提交：
SET autocommit = 0;-- 開啟事務
START TRANSACTION;
BEGIN;-- 釋放方式：
COMMIT;      -- 提交事務時釋放
ROLLBACK;    -- 回滾事務時釋放-- 創建賬戶表
CREATE TABLE accounts (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) UNIQUE NOT NULL,balance DECIMAL(12,2) NOT NULL,version INT DEFAULT 0,INDEX idx_username (username)
) ENGINE=InnoDB;-- 插入測試數據
INSERT INTO accounts (username, balance) VALUES 
('Alice', 1000.00),
('Bob', 2000.00),
('Charlie', 3000.00);-- 創建交易記錄表
CREATE TABLE transactions (id INT PRIMARY KEY AUTO_INCREMENT,from_user VARCHAR(50),to_user VARCHAR(50),amount DECIMAL(12,2),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

基礎共享鎖場景

場景1：多事務共享讀取

事務A:

START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Alice' FOR SHARE;
-- 保持事務不提交

事務B:

START TRANSACTION;
-- 可以同時加共享鎖
SELECT * FROM accounts WHERE username = 'Alice' FOR SHARE;  -- 成功
COMMIT;-- 普通查詢不受影響
SELECT * FROM accounts WHERE username = 'Alice';  -- 成功

場景2：共享鎖與修改沖突

事務A (保持共享鎖):

START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Bob' FOR SHARE;
-- 保持鎖不釋放

事務B (嘗試修改):

START TRANSACTION;
-- 嘗試更新被鎖記錄
UPDATE accounts SET balance = balance + 100 WHERE username = 'Bob';  -- 阻塞-- 在另一個會話查看鎖等待
SELECT * FROM performance_schema.data_lock_waits

當事務A釋放鎖后，事務B更新成功

此時查看鎖等待為空

當事務B提交事務后數據發生改變

DQL操作在共享鎖下的表現

場景3：不同查詢類型的共享鎖

事務A:

START TRANSACTION;
-- 加共享鎖
SELECT * FROM accounts WHERE username = 'Charlie' FOR SHARE;

事務B 測試各種查詢:

-- 1. 普通SELECT
SELECT * FROM accounts WHERE username = 'Charlie';  -- 成功-- 2. 聚合查詢
SELECT SUM(balance) FROM accounts;  -- 成功-- 3. 子查詢
SELECT * FROM accounts WHERE balance > (SELECT balance FROM accounts WHERE username = 'Charlie' FOR SHARE);  -- 成功-- 4. JOIN查詢
SELECT a.* FROM accounts a JOIN accounts b 
ON a.id = b.id WHERE a.username = 'Charlie' FOR SHARE;  -- 成功

DML操作在共享鎖下的表現

場景4：INSERT 操作

事務A:

START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR SHARE;

事務B:

-- 插入新記錄(不受共享鎖影響)
INSERT INTO accounts (username, balance) VALUES ('David', 4000.00);  -- 成功-- 嘗試插入沖突的唯一鍵
INSERT INTO accounts (username, balance) VALUES ('Bob', 5000.00);  -- 唯一鍵沖突錯誤(非鎖導致)

場景5：UPDATE 操作

事務A:

START TRANSACTION;
-- 范圍共享鎖
SELECT * FROM accounts WHERE balance BETWEEN 1000 AND 3000 FOR SHARE;

事務B:

-- 更新被鎖定的記錄
UPDATE accounts SET balance = 1500 WHERE username = 'Alice';  -- 阻塞

ALTER TABLE accounts ADD INDEX idx_balance (balance);-- 更新未被鎖定的記錄
UPDATE accounts SET balance = 5000 WHERE username = 'David';  -- 預測成功(如果David存在)

預測是成功的，但是實際是被阻塞的，為什么呢？

我們查看當前鎖情況：

-- 查看當前鎖情況
SELECT * FROM performance_schema.data_locks;-- 查看鎖等待情況
SELECT * FROM performance_schema.data_lock_waits;-- 查看當前事務
SELECT * FROM information_schema.INNODB_TRX;

鎖ID	事務ID	對象類型	索引	鎖模式	狀態	鎖定記錄	說明
858503:2806	858503	TABLE	-	IX	GRANTED	-	事務2(更新操作)持有的表級意向排他鎖
858503:1749:5:5	858503	RECORD	username	X	GRANTED	'David'	事務2已獲取username索引上David記錄的排他鎖
858503:1749:4:5	858503	RECORD	PRIMARY	X	GRANTED	4	事務2已獲取主鍵ID=4的排他鎖
858503:1749:7:6	858503	RECORD	idx_balance	X	WAITING	0x8000000FA000, 4	事務2正在等待獲取balance=4000的索引記錄排他鎖(被阻塞點)
283408490434960:2806	283408490434960	TABLE	-	IS	GRANTED	-	事務1(查詢操作)持有的表級意向共享鎖
283408490434960:1749:7:3	283408490434960	RECORD	idx_balance	S	GRANTED	0x800000083400, 2	事務1持有balance=2000的共享鎖
283408490434960:1749:7:4	283408490434960	RECORD	idx_balance	S	GRANTED	0x8000000BB800, 3	事務1持有balance=3000的共享鎖
283408490434960:1749:7:6	283408490434960	RECORD	idx_balance	S	GRANTED	0x8000000FA000, 4	事務1持有balance=4000的共享鎖(阻塞源頭)
283408490434960:1749:7:7	283408490434960	RECORD	idx_balance	S	GRANTED	0x80000003E800, 1	事務1持有balance=1000的共享鎖
283408490434960:1749:4:2	283408490434960	RECORD	PRIMARY	S	GRANTED	1	事務1持有主鍵ID=1的共享鎖
283408490434960:1749:4:3	283408490434960	RECORD	PRIMARY	S	GRANTED	2	事務1持有主鍵ID=2的共享鎖
283408490434960:1749:4:4	283408490434960	RECORD	PRIMARY	S	GRANTED	3	事務1持有主鍵ID=3的共享鎖

鎖等待鏈條分析

1. 事務1（SELECT操作）

持有idx_balance索引上4條記錄的共享鎖(S鎖)：

- balance=1000（ID=1）
- balance=2000（ID=2）
- balance=3000（ID=3）
- balance=4000（ID=4） ← 關鍵阻塞點

持有主鍵索引上ID 1-3的共享鎖
持有表級意向共享鎖(IS)

2. 事務2（UPDATE操作）

已成功獲取：

- 表級意向排他鎖(IX)
- username索引上'David'記錄的排他鎖(X)
- 主鍵ID=4的排他鎖(X)

正在等待：

- idx_balance索引上balance=4000記錄的排他鎖(X) （導致阻塞）

關鍵沖突點

事務1的SELECT查詢意外鎖定了balance=4000的記錄（盡管查詢條件是1000-3000）
事務2需要修改這條索引記錄，但被事務1的S鎖阻塞

如果將事務隔離級別調整為讀已提交，則不再使用間隙鎖，即不在阻塞：

間隙鎖(Gap Lock)的消除：

REPEATABLE READ：默認使用間隙鎖防止幻讀

READ COMMITTED：禁用間隙鎖，僅鎖定實際存在的記錄

-- 設置當前會話為讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

場景6：DELETE 操作

事務A:

START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Alice' FOR SHARE;

事務B:

-- 刪除被鎖定的記錄
DELETE FROM accounts WHERE username = 'Alice';  -- 阻塞-- 刪除未被鎖定的記錄
DELETE FROM accounts WHERE username = 'David';  -- 成功(如果David存在)

共享鎖其他場景

場景7：索引對共享鎖的影響

事務A (無索引條件):

START TRANSACTION;
-- 不使用索引的查詢會導致表鎖
SELECT * FROM accounts WHERE balance = 2000.00 FOR SHARE;

事務B:

-- 所有修改操作都會被阻塞
UPDATE accounts SET username = 'Bobby' WHERE id = 2;  -- 阻塞
INSERT INTO accounts (username, balance) VALUES ('Eve', 5000.00);  -- 阻塞

場景8：共享鎖導致的死鎖

事務A:

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 然后嘗試更新
UPDATE accounts SET balance = 1500 WHERE id = 2;  -- 需要等待事務B的鎖

事務B:

START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR SHARE;
-- 然后嘗試更新
UPDATE accounts SET balance = 2500 WHERE id = 1;  -- 需要等待事務A的鎖
-- 此時會發生死鎖

1213 - Deadlock found when trying to get lock; try restarting transaction

在嘗試獲取鎖時發現死鎖；請重新啟動事務。

查看死鎖日志:

SHOW ENGINE INNODB STATUS

b. 排它鎖(X)

排它鎖，Exclusive Lock,又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存，如一個事務獲取了一個數據行的排它鎖，其他事務就不能再獲取該行的其他鎖，包括共享鎖和排它鎖，但是獲取排它鎖，但是獲取排它鎖的事務是可以對數據就行讀取和修改。

當前事務加鎖之后，其他事務不可以加鎖

select … for update;

排他鎖(X鎖)的特性：

獨占性：一個數據行上只能有一個X鎖
排他性：持有X鎖時，其他事務不能獲取任何鎖(S/X)
讀寫權限：持有X鎖的事務可以讀取和修改數據

案例1：基本排他鎖使用

-- 事務1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 獲取id=1的排他鎖
-- 此時可以修改這條記錄
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;-- 事務2 (同時運行)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 會被阻塞，直到事務1提交

案例2：排他鎖與共享鎖的互斥

-- 事務1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- 獲取共享鎖-- 事務2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 會被阻塞，因為與共享鎖沖突

案例3：不同索引上的排他鎖

-- 表結構：accounts(id PK, username UNIQUE, balance INDEX)-- 事務1
START TRANSACTION;
SELECT * FROM accounts WHERE username = 'Alice' FOR UPDATE; -- 在username索引上加X鎖-- 事務2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 可以執行(如果id=1不是Alice的記錄)

案例4：排他鎖的死鎖場景

-- 事務1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 獲取id=1的X鎖
-- 假設此時事務2執行了下面的語句-- 事務2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 獲取id=2的X鎖
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 等待事務1釋放id=1的鎖-- 此時事務1執行：
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 等待事務2釋放id=2的鎖
-- 形成死鎖，InnoDB會自動檢測并回滾其中一個事務

案例5：排他鎖的范圍鎖定

-- 事務1 (REPEATABLE READ隔離級別)
START TRANSACTION;
SELECT * FROM accounts WHERE balance BETWEEN 1000 AND 3000 FOR UPDATE;
-- 鎖定balance在1000-3000之間的所有記錄及間隙-- 事務2
START TRANSACTION;
INSERT INTO accounts VALUES(NULL, 'Bob', 1500); -- 會被阻塞(因為1500在鎖定范圍內)

案例6：排他鎖與DDL操作

-- 事務1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;-- 事務2
ALTER TABLE accounts ADD COLUMN last_login DATETIME; -- 會被阻塞，需要等待元數據鎖

2. 按照加鎖的范圍 , Mysql的鎖大致可以分為全局鎖和表級鎖和行鎖。

a. 全局鎖 (Global Lock)

全局鎖是對整個 MySQL 數據庫實例加鎖，加鎖后數據庫處于只讀狀態，所有數據變更操作(增刪改)都會被阻塞。

FLUSH TABLES WITH READ LOCK;  -- 加全局讀鎖
UNLOCK TABLES;                -- 釋放全局鎖

特性：

阻塞所有寫操作：包括 DML(INSERT/UPDATE/DELETE) 和 DDL(ALTER/CREATE 等)
允許讀操作：SELECT 查詢可以正常執行
影響范圍：整個 MySQL 實例的所有數據庫
自動釋放：當會話斷開時自動釋放(除非使用 SET GLOBAL read_only=ON)

使用場景：

全庫邏輯備份：確保備份數據的一致性
主從同步初始化：保證主庫在導出數據時不發生變化
數據庫維護：執行需要數據靜止狀態的操作

注意：

長時間持有全局鎖會導致業務停滯
在 InnoDB 引擎下，推薦使用 mysqldump --single-transaction 進行熱備份替代全局鎖
執行 FTWRL 時會自動提交當前活動事務

測試數據模擬：


-- 創建用戶表
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,balance DECIMAL(10,2) DEFAULT 0.00,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;-- 創建訂單表
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,amount DECIMAL(10,2) NOT NULL,status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;-- 創建商品表
CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL,stock INT NOT NULL DEFAULT 0,description TEXT
) ENGINE=InnoDB;-- 插入用戶數據
INSERT INTO users (username, email, balance) VALUES
('john_doe', 'john@example.com', 1000.00),
('jane_smith', 'jane@example.com', 1500.50),
('bob_johnson', 'bob@example.com', 750.25),
('alice_williams', 'alice@example.com', 2000.00);-- 插入商品數據
INSERT INTO products (name, price, stock, description) VALUES
('Laptop', 999.99, 50, 'High performance laptop'),
('Smartphone', 699.99, 100, 'Latest model smartphone'),
('Headphones', 149.99, 200, 'Noise cancelling headphones'),
('Tablet', 399.99, 75, '10-inch tablet');-- 插入訂單數據
INSERT INTO orders (user_id, amount, status) VALUES
(1, 999.99, 'completed'),
(2, 699.99, 'completed'),
(3, 149.99, 'pending'),
(4, 399.99, 'completed'),
(1, 149.99, 'completed');

ⅰ. 全局鎖實際應用場景模擬：

1. 場景：全庫邏輯備份

-- 會話1（管理員連接） - 執行備份操作-- 1. 首先查看當前活動事務
SELECT * FROM information_schema.INNODB_TRX;-- 2. 加全局讀鎖（會自動提交當前活動事務）
FLUSH TABLES WITH READ LOCK;
然后執行
INSERT INTO users (username, email, balance) VALUES
('bow', 'Bow@example.com', 1000.00)-- 3. 查看鎖狀態（在新會話中執行）
-- 在另一個終端連接MySQL執行：
SHOW PROCESSLIST;
/* 4	event_scheduler	localhost		Daemon	13163	Waiting on empty queue	
107	root	localhost:58662	sql_lock1_demo	Sleep	18		
108	root	localhost:58668	sql_lock1_demo	Query	10	Waiting for global read lock	INSERT INTO users (username, email, balance) VALUES
('bow', 'Bow@example.com', 1000.00)
109	root	localhost:58672	sql_lock1_demo	Query	0	starting	SHOW PROCESSLIST
*/-- 4. 執行備份操作（這里用SELECT模擬）
-- 編輯 my.ini  重啟
[mysqld]
secure_file_priv = ""-- 備份用戶數據
SELECT * FROM users INTO OUTFILE '/users_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';-- 備份訂單數據
SELECT * FROM orders INTO OUTFILE '/orders_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';-- 備份商品數據
SELECT * FROM products INTO OUTFILE '/products_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';-- 5. 釋放全局鎖
UNLOCK TABLES;-- 6. 驗證備份文件
-- 在系統shell中執行：
-- cat /users_backup.csv
-- cat /orders_backup.csv
-- cat /products_backup.csv

b. 表級鎖 (Table Lock)

表級鎖是對整張表加鎖，MyISAM 引擎默認使用表級鎖，InnoDB 也支持手動表鎖。

ⅰ. 主要類型：

1. 表共享讀鎖 (Table Read Lock)-讀鎖

特性：

允許多個會話同時獲取讀鎖
持有讀鎖的會話只能讀表，不能寫
其他會話可以讀表，但不能寫表

LOCK TABLES table_name READ;  -- 加表讀鎖
UNLOCK TABLES;                -- 釋放鎖

表共享讀鎖(Table Read Lock)案例：

-- 會話1（報表生成）
-- 加表讀鎖
LOCK TABLES employees READ;-- 可以正常讀取數據
SELECT * FROM employees WHERE department = '研發部';
/*
+----+------+------------+----------+------------+
| id | name | department | salary   | join_date  |
+----+------+------------+----------+------------+
|  1 | 張三 | 研發部     | 15000.00 | 2020-05-10 |
|  2 | 李四 | 研發部     | 18000.00 | 2019-03-15 |
+----+------+------------+----------+------------+
*/-- 嘗試修改數據會報錯
UPDATE employees SET salary = 16000 WHERE id = 1;
-- 錯誤：Table 'employees' was locked with a READ lock and can't be updated-- 保持鎖不釋放，繼續在會話2測試-- 會話2（其他操作）
-- 可以加讀鎖（允許多個會話同時持有讀鎖）
LOCK TABLES employees READ;
SELECT * FROM employees WHERE department = '市場部'; -- 成功
UNLOCK TABLES;-- 可以不加鎖直接讀
SELECT * FROM employees; -- 成功-- 嘗試寫操作會被阻塞
UPDATE employees SET salary = salary + 1000 WHERE id = 3; -- 被阻塞-- 會話1釋放鎖后
UNLOCK TABLES;
-- 會話2的更新操作會繼續執行

2. 表獨占寫鎖 (Table Write Lock)-寫鎖

特性：

只有一個會話能獲取寫鎖
持有寫鎖的會話可以除select以外的所有操作
其他會話不能讀也不能寫表

LOCK TABLES table_name WRITE;  -- 加表寫鎖

表獨占寫鎖(Table Write Lock)案例：

-- 會話1（數據維護）
-- 加表寫鎖
LOCK TABLES employees WRITE;-- 不可以讀寫數據  阻塞
SELECT * FROM employees WHERE salary < 13000;-- 執行批量更新
UPDATE employees SET salary = salary * 1.1 WHERE salary < 13000;-- 保持鎖不釋放，繼續在會話2測試-- 會話2（其他操作）
-- 嘗試讀操作會被阻塞
SELECT * FROM employees; -- 被阻塞-- 嘗試寫操作會被阻塞
INSERT INTO employees VALUES (NULL, '錢七', '市場部', 15000.00, CURDATE()); -- 被阻塞-- 會話1釋放鎖
UNLOCK TABLES;
-- 會話2的操作會繼續執行

3. 元數據鎖 (Metadata Lock, MDL)

元數據鎖：metadata lock，簡稱MDL，它是在MySQL 5.5版本引進的。元數據鎖不用像表鎖那樣顯式的加鎖和釋放鎖，而是在訪問表時被自動加上，以保證讀寫的正確性。加鎖和釋放鎖規則如下：

MDL讀鎖之間不互斥，也就是說，允許多個線程同時對加了 MDL讀鎖的表進行CRUD(增刪改查)操作；

MDL寫鎖，它和讀鎖、寫鎖都是互斥的，目的是用來保證變更表結構操作的安全性。也就是說，當對表結構進行變更時，會被默認加 MDL寫鎖，因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執行完才能開始執行。

MDL讀寫鎖是在事務commit之后才會被釋放；

自動加鎖，無需顯式命令：

當訪問表時自動加 MDL 讀鎖
當修改表結構時自動加 MDL 寫鎖

元數據鎖(MDL)案例：

-- 會話1：
START TRANSACTION;
-- 獲取MDL讀鎖
SELECT * FROM employees WHERE id = 1;
-- 不提交事務，保持連接-- 會話2：
-- 嘗試修改表結構（需要MDL寫鎖）
ALTER TABLE employees ADD COLUMN bonus DECIMAL(10,2); -- 被阻塞-- 會話3：
-- 可以正常查詢（MDL讀鎖兼容）
SELECT * FROM employees; -- 成功-- 會話1提交后
COMMIT;
-- 會話2的ALTER操作會繼續執行

ⅱ. 使用場景

MyISAM 表的讀寫操作(自動加鎖)
需要鎖定整表的特殊操作
表結構變更(自動 MDL 鎖)

ⅲ. 注意事項

表鎖粒度大，并發性能差
LOCK TABLES 會隱式提交當前事務
使用 UNLOCK TABLES 會釋放當前會話持有的所有表鎖
MDL 鎖可能導致長時間阻塞(如長事務中執行 DDL)

c. 行級鎖 (Row Lock)

行級鎖是 InnoDB 引擎特有的鎖機制，可以精確鎖定表中的單行或多行記錄，大大提高了并發性能。

ⅰ. 特性

粒度小：只鎖定需要的行，并發度高
開銷大：加鎖需要更多資源
死鎖風險：容易出現循環等待
自動釋放：事務結束時自動釋放

CREATE TABLE `lock_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`value` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_value` (`value`)
) ENGINE=InnoDB;INSERT INTO `lock_test` VALUES 
(5, 100, 'A'),
(10, 200, 'B'),
(15, 300, 'C'),
(20, 400, 'D');

ⅱ. 主要類型

1. 記錄鎖 (Record Lock)

Record Lock，記錄鎖，它是針對索引記錄的鎖，鎖定的總是索引記錄。在多用戶數據庫系統中，多個事務可能會同時嘗試讀取或修改同一條記錄，Record Lock確保只有一個事務能在某一時刻修改該記錄，其他事務只能讀取，或者在寫鎖釋放后再進行修改。

鎖定索引中的單條記錄
總是鎖定索引記錄，即使表沒有定義索引，InnoDB 也會創建隱藏的聚簇索引

-- 會話1
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 10 FOR UPDATE;  -- 對id=10加X鎖-- 會話2
UPDATE lock_test SET name = 'B2' WHERE id = 10;  -- 被阻塞
UPDATE lock_test SET name = 'A2' WHERE id = 5;   -- 成功（未鎖定）

2. 間隙鎖 (Gap Lock)

Gap Lock，間隙鎖，它是一種行級鎖，鎖住兩個索引記錄之間的間隙，而不是實際的數據記錄，由InnoDB隱式添加。

鎖定索引記錄之間的間隙
防止其他事務在間隙中插入數據
只在 REPEATABLE READ 隔離級別下有效

間隙讀鎖(FOR SHARE)：

-- 會話1（獲取間隙讀鎖）
START TRANSACTION;
SELECT * FROM lock_test WHERE value BETWEEN 150 AND 350 FOR SHARE;
-- 鎖定(100,200], (200,300], (300,400]區間-- 會話2（測試插入）
INSERT INTO lock_test VALUES (34, 90, 'Z');   -- 未阻塞
INSERT INTO lock_test VALUES (8, 110, 'E');  -- 被阻塞（落在(100,200]）
INSERT INTO lock_test VALUES (12, 250, 'F'); -- 被阻塞（落在(200,300]）
INSERT INTO lock_test VALUES (17, 380, 'G'); -- 被阻塞（落在(300,400]）INSERT INTO lock_test VALUES (31, 500, 'I');   -- 阻塞INSERT INTO lock_test VALUES (39, 100, 'X');   --- 阻塞-- 會話3（測試讀取）
SELECT * FROM lock_test WHERE value = 200; -- 成功（間隙讀鎖不阻塞讀）

說明：由 InnoDB 間隙鎖的具體實現機制決定的

現有數據：(5,100), (10,200), (15,300), (20,400)
查詢條件：value BETWEEN 150 AND 350

區間鎖定規則：

- 查詢條件 BETWEEN 150 AND 350 會鎖定所有掃描過的區間
- 區間：(100,200]，(200,300]，(300,400]

邊界處理：

- 不鎖定查詢范圍之外的區間（如 (-∞,100] 和 (400,+∞)）
- 對邊界值200和300采用"閉"原則（包含這些值）

為什么 INSERT INTO lock_test VALUES (39, 100, 'X'); 無法插入呢？

InnoDB 實際上使用的是 Next-Key Lock（臨鍵鎖），它是 記錄鎖(Record Lock) + 間隙鎖(Gap Lock) 的組合。插入 value=100 時需要獲取 插入意向鎖、插入意向鎖會與 (100,200] 區間鎖沖突、InnoDB 的鎖檢查是 保守型 的，會阻止潛在的沖突

間隙寫鎖(FOR UPDATE)

InnoDB在REPEATABLE READ隔離級別下實際使用的是臨鍵鎖(Next-Key Lock)，它是記錄鎖和間隙鎖的組合。

場景1：記錄+間隙鎖定

-- 會話1
START TRANSACTION;
SELECT * FROM lock_test WHERE value = 200 FOR UPDATE;
-- 鎖定記錄200和(100,200)間隙-- 會話2
INSERT INTO lock_test VALUES (8, 150, 'K'); -- 被阻塞（在間隙中）
UPDATE lock_test SET name = 'B2' WHERE value = 200; -- 被阻塞（記錄被鎖）
INSERT INTO lock_test VALUES (12, 250, 'L'); -- 成功（不在鎖定范圍）

場景2：范圍鎖定

-- 會話1
START TRANSACTION;
SELECT * FROM lock_test WHERE value > 200 AND value < 300 FOR UPDATE;
-- 鎖定[200,300]區間及周圍間隙-- 會話2
INSERT INTO lock_test VALUES (6, 90, 'T');   -- 成功
INSERT INTO lock_test VALUES (7, 199, 'M');  -- 被阻塞（鎖定到左邊界擴展）
INSERT INTO lock_test VALUES (12, 250, 'N'); -- 被阻塞（區間內）
INSERT INTO lock_test VALUES (17, 301, 'O'); -- 被阻塞（右邊界擴展）

-- 明確排除邊界
SELECT * FROM lock_test 
WHERE value > 200 AND value < 300
AND value NOT IN (200,300) FOR UPDATE;INSERT INTO lock_test VALUES (22, 200, 'X');
INSERT INTO lock_test VALUES (299, 300, 'X');

3. 臨鍵鎖 (Next-Key Lock)

Next-Key Lock，稱為臨鍵鎖，它是Record Lock + Gap Lock的組合，用來鎖定一個范圍，并且鎖定記錄本身鎖，它是一種左開右閉的范圍，可以用符號表示為：(a,b]。

記錄鎖 + 間隙鎖的組合
鎖定記錄本身和記錄前面的間隙
InnoDB 默認的行鎖類型

-- 創建測試表
CREATE TABLE next_key_lock_demo (id INT PRIMARY KEY AUTO_INCREMENT,range_val INT NOT NULL,data VARCHAR(100),INDEX idx_range (range_val)
) ENGINE=InnoDB;-- 插入測試數據
INSERT INTO next_key_lock_demo (range_val, data) VALUES 
(10, 'A'), (20, 'B'), (30, 'C'), (40, 'D'), (50, 'E');

場景1：普通范圍查詢：

-- 會話1
START TRANSACTION;
-- 使用臨鍵鎖鎖定20-40范圍（不包含邊界）
SELECT * FROM next_key_lock_demo 
WHERE range_val > 20 AND range_val < 40 
FOR UPDATE;-- 查看實際獲取的鎖
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA 
FROM performance_schema.data_locks 
WHERE OBJECT_NAME = 'next_key_lock_demo';
-- 測試不同位置的插入
INSERT INTO next_key_lock_demo VALUES (NULL, 15, 'F');  -- ? 成功 (在10-20區間)
INSERT INTO next_key_lock_demo VALUES (NULL, 25, 'G');  -- ? 被阻塞 (在20-30區間)
INSERT INTO next_key_lock_demo VALUES (NULL, 35, 'H');  -- ? 被阻塞 (在30-40區間)
INSERT INTO next_key_lock_demo VALUES (NULL, 45, 'I');  -- ? 成功 (在40-50區間)-- 測試邊界值
INSERT INTO next_key_lock_demo VALUES (NULL, 20, 'J');  -- ? 被阻塞 (臨鍵鎖保護)
INSERT INTO next_key_lock_demo VALUES (NULL, 40, 'K');  -- ? 成功 (上邊界不鎖)

對比不同查詢條件

查詢條件	左邊界	右邊界	鎖定邏輯
`>20 AND <40`	鎖定	不鎖	基本案例
`>=20 AND <=40`	鎖定	鎖定	包含邊界
`>20 AND <=40`	鎖定	鎖定	右包含
`>=20 AND <40`	鎖定	不鎖	左包含

為什么左邊界(20)要鎖定？

防止幻讀的需要：

- 假設表中有數據：10, 20, 30, 40, 50
- 你的查詢條件是 >20 AND <40，應該返回30
- 如果不鎖定20之后的間隙，其他事務可以插入25這樣的值，導致你的第二次查詢可能返回25和30，這就是幻讀

鎖定的是"20之后"的間隙：

- 不是鎖定20這個值本身(因為條件是>20，不包含等于)
- 鎖定的是(20,30)這個間隙
- 技術上實現為：在20這條記錄上加臨鍵鎖，鎖定的是20到下一個實際存在的值(30)之間的間隙

實際鎖定范圍：

- 鎖定所有大于20的記錄，直到遇到第一個大于等于40的記錄
- 在示例數據中：鎖定(20,30)間隙，鎖定30這條記錄本身，(30,40)間隙

為什么不是從21開始：

- 數據庫索引不是按連續整數組織的
- 無法預知20和下一個值之間有多大間隔(可能是20.0001，也可能是29)
- 鎖定20之后的間隙是唯一可靠的方法
- 因為索引判斷"是否可能進入鎖定間隙"時，會把等于左邊界的插入也視為潛在沖突

右邊界(40)為什么不鎖定？

條件是不包含40(<40)
只需要保證沒有記錄插入到小于40的位置
已經通過鎖定30和(30,40)間隙實現了這一點
不需要鎖定40本身，因為條件明確排除了40

鎖升級案例

-- 會話1：大范圍查詢導致鎖升級
START TRANSACTION;
SELECT * FROM next_key_lock_demo 
WHERE range_val > 10 
FOR UPDATE;-- 會話2：所有插入都被阻塞
INSERT INTO next_key_lock_demo VALUES (NULL, 5, 'R');   -- ? 被阻塞
INSERT INTO next_key_lock_demo VALUES (NULL, 60, 'S');  -- ? 被阻塞

4. 插入意向鎖 (Insert Intention Lock)

意向鎖(Intention Lock)是InnoDB中一種特殊的表級鎖，但它與行鎖密切相關。

當一個事務在需要獲取資源的鎖定時，如果該資源已經被排他鎖占用，則數據庫會自動給該事務申請一個該表的意向鎖。如果自己需要一個共享鎖定，就申請一個意向共享鎖。如果需要的是某行（或者某些行）的排他鎖定，則申請一個意向排他鎖。

意向鎖的存在是為了協調行鎖和表鎖的關系，用于優化InnoDB加鎖的策略。意向鎖的主要功能就是：避免為了判斷表是否存在行鎖而去全表掃描。

意向鎖是由InnoDB在操作數據之前自動加的，不需要用戶干預；

意向共享鎖（IS鎖）：事務在請求S鎖前，要先獲得IS鎖
意向排他鎖（IX鎖）：事務在請求X鎖前，要先獲得IX鎖

特點：

特殊的間隙鎖
表示事務想在某個間隙插入記錄
多個事務可以在同一間隙的不同位置插入

-- 創建測試表
CREATE TABLE intention_lock_demo (id INT PRIMARY KEY,name VARCHAR(20),age INT,INDEX idx_name (name)
) ENGINE=InnoDB;-- 插入測試數據
INSERT INTO intention_lock_demo VALUES 
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 35);

意向共享鎖(IS)演示：

-- 會話1  獲取行共享鎖(S鎖)
START TRANSACTION;
SELECT * FROM intention_lock_demo WHERE id = 1 LOCK IN SHARE MODE;-- 會話2 查看鎖狀態
-- 在新會話中執行
SELECT ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'intention_lock_demo';

鎖ID	數據庫	表名	索引	鎖類型	鎖模式	狀態	鎖定數據	說明
283676627564352	sql_lock1_demo	intention_lock_demo		TABLE	IS	GRANTED		表級意向共享鎖，表示事務準備在表的某些行上加共享鎖
283676627564352	sql_lock1_demo	intention_lock_demo	PRIMARY	RECORD	S	GRANTED	1	行級共享鎖，已鎖定主鍵值為1的記錄，允許其他事務讀但禁止修改該行數據

意向排他鎖(IX)演示：

-- 會話1  獲取行排他鎖(X鎖)
START TRANSACTION;
SELECT * FROM intention_lock_demo WHERE id = 2 FOR UPDATE;-- 會話2 查看鎖等待
SELECT ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'intention_lock_demo';

鎖ID	數據庫	表名	索引	鎖類型	鎖模式	狀態	鎖定數據	說明
859404	sql_lock1_demo	intention_lock_demo		TABLE	IX	GRANTED		表級意向排他鎖，表示事務準備在表的某些行上加排他鎖
859404	sql_lock1_demo	intention_lock_demo	PRIMARY	RECORD	X	GRANTED	2	行級排他鎖，已鎖定主鍵值為2的記錄，禁止其他事務讀寫該行數據