MySQL 加鎖機制驗證記錄

作者：三點水 2020-12-14 12:17:47

MySQL 官方文檔給出了不同類型語句的加鎖情形，但我覺得這個總結更到位，因此想結合文章的幾種情形，結合 InnoDB Monitor Output 做分析。文章是驗證過程的記錄，全文比較長，建議結合目錄查看感興趣的部分。

MySQL 官方文檔給出了不同類型語句的加鎖情形，但我覺得這個總結更到位，因此想結合文章的幾種情形，結合 InnoDB Monitor Output 做分析。

文章是驗證過程的記錄，全文比較長，建議結合目錄查看感興趣的部分。

開啟 InnoDB Monitor

SET GLOBAL innodb_status_output=ON; -- 開啟輸出  
SET GLOBAL innodb_status_output_locks=ON; -- 開啟鎖信息輸出

注意這些選項在 mysql 重啟后會恢復默認值。接下來使用命令查看信息：

SHOW ENGINE INNODB STATUS\G

樣例輸出，我們只關心鎖相關的內容：

---TRANSACTION 929632, ACTIVE 27 sec 
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9179 localhost root 
TABLE LOCK table `test`.`id_pk_rc` trx id 929632 lock mode IX 
RECORD LOCKS space id 1813 page no 3 n bits 72 index PRIMARY of table `test`.`id_pk_rc` trx id 929632 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 
 0: len 4; hex 80000005; asc     ;; 
 1: len 6; hex 0000000e2f60; asc     /`;; 
 2: len 7; hex 4c000002222e83; asc L   ". ;; 
 3: len 1; hex 63; asc c;;

“page no 3 n bits 72” 代表在第 3 頁的記錄上，lock bitmap 共 72 位
“index PRIMARY of …” 代表鎖在某個索引上，PRIMARY 代表鎖在主鍵上
“lock_mode X” 鎖模式，X 代表互斥，鎖模式可以參數官方文檔 InnoDB Locking
“locks rec but not gap” 代表記錄鎖，“locks gap before rec” 代表間隙鎖，沒有說明則代表 Next Key Lock
“heap no 4” 代表記錄的序號，0 代表 infimum 記錄、1 代表 supremum 記錄，用戶記錄從 2 開始
PHYSICAL RECORD 后面的內容是索引記錄的內存結構，通常沒辦法直接閱讀

這個記錄里沒法直接看出鎖住了哪些記錄。一種方法是通過 select * from information_schema.innodb_locks \G; 查看搶鎖沒搶到的信息，為了查看記錄，在測試時可以另開一個會話，用諸如 SELECT * FROM ... WHERE ... FOR UPDATE 來搶鎖，這樣就可以看出鎖在哪個記錄上了。樣例輸出：

lock_id     | 929771:1817:4:4 
lock_trx_id | 929771 
lock_mode   | X 
lock_type   | RECORD 
lock_table  | `test`.`id_si_rc` 
lock_index  | id_si 
lock_space  | 1817 
lock_page   | 4 
lock_rec    | 4 
lock_data   | 5, 3 -- 注意這里是數據標識

還有一個工具好用的工具 innodb_ruby 可以用來解析 MySQL 的靜態文件。Monitor 日志里我們知道是哪個頁的哪條記錄，可以使用innodb_ruby 來找到對應的記錄。(不過不建議在生產上使用)

不同情形下加鎖驗證

我們會考查 DELETE FROM t1 WHERE id = 5 語句在不同情形下的加鎖情況，通過構造數據、執行語句、查看 Monitor 日志來驗證加鎖的機制。

主鍵 + RC

結論：只對 ID = 5 這條記錄加 Record Lock

首先建表準備數據：

-- 建表 
CREATE TABLE id_pk_rc(id int primary key, name varchar(32)); 
 
-- 準備數據 
INSERT INTO id_pk_rc values(1, 'a'); 
INSERT INTO id_pk_rc values(3, 'b'); 
INSERT INTO id_pk_rc values(5, 'c'); 
INSERT INTO id_pk_rc values(7, 'c'); 
INSERT INTO id_pk_rc values(9, 'b');

執行語句

-- 設置為 RC 隔離級別 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
BEGIN; -- 開啟事務 
DELETE FROM id_pk_rc WHERE id = 5; 
-- 先不結束事務，驗證 Monitor Output 再用 ROLLBACK; 回滾

Monitor 輸出日志：

---TRANSACTION 929632, ACTIVE 27 sec 
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9179 localhost root 
TABLE LOCK table `test`.`id_pk_rc` trx id 929632 lock mode IX 
RECORD LOCKS space id 1813 page no 3 n bits 72 index PRIMARY of table `test`.`id_pk_rc` trx id 929632 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 
 0: len 4; hex 80000005; asc     ;; 
 1: len 6; hex 0000000e2f60; asc     /`;; 
 2: len 7; hex 4c000002222e83; asc L   ". ;; 
 3: len 1; hex 63; asc c;;

看到輸出里有 lock_mode X locks rec but not gap ，可以確定持有的是記錄鎖。

唯一索引 + RC

結論：索引和聚簇索引/主鍵中都對 ID = 5 加 Record Lock

首先建表準備數據：

-- 建表 
CREATE TABLE id_ui_rc(pk int primary key, id int, name varchar(32)); 
CREATE UNIQUE INDEX id_ui ON id_ui_rc(id); 
 
-- 準備數據 
INSERT INTO id_ui_rc values(1, 1, 'a'); 
INSERT INTO id_ui_rc values(2, 3, 'b'); 
INSERT INTO id_ui_rc values(3, 5, 'c'); 
INSERT INTO id_ui_rc values(4, 7, 'c'); 
INSERT INTO id_ui_rc values(5, 9, 'b');

執行語句：

-- 設置為 RC 隔離級別 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
BEGIN; -- 開啟事務 
DELETE FROM id_ui_rc WHERE id = 5; 
-- 先不結束事務，驗證 Monitor Output 再用 ROLLBACK; 回滾

Monitor 輸出日志：

---TRANSACTION 929694, ACTIVE 6 sec 
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9241 localhost root 
TABLE LOCK table `test`.`id_ui_rc` trx id 929694 lock mode IX 
RECORD LOCKS space id 1815 page no 4 n bits 72 index id_ui of table `test`.`id_ui_rc` trx id 929694 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 0: len 4; hex 80000005; asc     ;; 
 1: len 4; hex 80000003; asc     ;; 
 
RECORD LOCKS space id 1815 page no 3 n bits 72 index PRIMARY of table `test`.`id_ui_rc` trx id 929694 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 0: len 4; hex 80000003; asc     ;; 
 1: len 6; hex 0000000e2f9e; asc     / ;; 
 2: len 7; hex 7a0000059525c9; asc z    % ;; 
 3: len 4; hex 80000005; asc     ;; 
 4: len 1; hex 63; asc c;;

可以看到分別對 index id_ui 和 index PRIMARY 加了 Record Lock。

非唯一索引 + RC

結論：會對所有 ID = 5 的索引記錄加 Record Lock，同時對主鍵加 Record Lock。

首先建表準備數據：

-- 建表 
CREATE TABLE id_si_rc(pk int primary key, id int, name varchar(32)); 
CREATE INDEX id_si ON id_si_rc(id); 
 
-- 準備數據 
INSERT INTO id_si_rc values(1, 1, 'a'); 
INSERT INTO id_si_rc values(2, 3, 'b'); 
INSERT INTO id_si_rc values(3, 5, 'c'); 
INSERT INTO id_si_rc values(4, 7, 'c'); 
INSERT INTO id_si_rc values(5, 5, 'b');

執行語句：

-- 設置為 RC 隔離級別 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
BEGIN; -- 開啟事務 
DELETE FROM id_si_rc WHERE id = 5; 
-- 先不結束事務，驗證 Monitor Output 再用 ROLLBACK; 回滾

Monitor 輸出日志(省略了 PHYSICAL RECORD 的內容)：

---TRANSACTION 929779, ACTIVE 3 sec 
3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9325 localhost root 
TABLE LOCK table `test`.`id_si_rc` trx id 929779 lock mode IX 
RECORD LOCKS space id 1817 page no 4 n bits 72 index id_si of table `test`.`id_si_rc` trx id 929779 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 ... 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 ... 
 
RECORD LOCKS space id 1817 page no 3 n bits 72 index PRIMARY of table `test`.`id_si_rc` trx id 929779 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ...

可以看到一共有 4 條記錄，首先可以看到索引 id_si 和 PRIMARY 分別鎖住了兩條記錄，加的鎖都是 X Record Lock No Gap，也就是記錄鎖。我們通過 select * from information_schema.innodb_locks \G; 查看是鎖住了 3, 5 這兩條記錄。

lock_id     | 929779:1817:4:4 
lock_trx_id | 929779 
lock_mode   | X 
lock_type   | RECORD 
lock_table  | `test`.`id_si_rc` 
lock_index  | id_si 
lock_space  | 1817 
lock_page   | 4 
lock_rec    | 4 
lock_data   | 5, 3  <- 注意這里

無索引 + RC

結論：對所有記錄加 Record Lock 再釋放不匹配的記錄鎖

這個情形比較特殊，涉及兩個知識點

MySQL 加鎖時是對處理過程中“掃描”到的記錄加鎖，不管這條記錄最終是不是通過 WHERE 語句剔除了
對于 READ COMMITTED，MySQL 在掃描結束后，會違反 #1，釋放 WHERE 條件不滿足的記錄鎖

首先建表準備數據：

-- 建表 
CREATE TABLE id_ni_rc(pk int primary key, id int, name varchar(32)); 
 
-- 準備數據 
INSERT INTO id_ni_rc values(1, 1, 'a'); 
INSERT INTO id_ni_rc values(2, 3, 'b'); 
INSERT INTO id_ni_rc values(3, 5, 'c'); 
INSERT INTO id_ni_rc values(4, 7, 'c'); 
INSERT INTO id_ni_rc values(5, 5, 'b');

執行語句：

-- 設置為 RC 隔離級別 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
BEGIN; -- 開啟事務 
DELETE FROM id_ni_rc WHERE id = 5; 
-- 先不結束事務，驗證 Monitor Output 再用 ROLLBACK; 回滾

Monitor 輸出日志(省略了 PHYSICAL RECORD 的內容)：

---TRANSACTION 1446, ACTIVE 17 sec 
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 
MySQL thread id 7, OS thread handle 123145446559744, query id 267 localhost root 
TABLE LOCK table `test`.`id_ni_rc` trx id 1446 lock mode IX 
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`id_ni_rc` trx id 1446 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ...

看到 TABLE LOCK 的狀態是 IX 說明沒有加表鎖。同時看到最終鎖住的只有heap_no = 4 和 6 的兩條記錄。

主鍵 + RR

當 ID 為主鍵時，在 RR 隔離級別下，加鎖情況與一致，都是對主鍵記錄加 Record Lock。

唯一索引 + RR

當 ID 為唯一索引時，在 RR 隔離級別下，加鎖情況與一致，都是對索引記錄和聚簇索引/主鍵 Record Lock。

非唯一索引 + RR

結論：對索引記錄 Next Key Lock，末尾加 Gap Lock，同時對主鍵加 Record Lock

Repeatable Read 和 Read Committed 隔離級別的主要區別是 RR 要防止幻讀。幻讀指的是執行同一個 SQL 兩次得到的結果不同。考慮下面的場景：

SELECT count(*) FROM t WHERE id = 5 FOR UPDATE 
id = 5 
SELECT count(*) FROM t WHERE id = 5 FOR UPDATE

為了要避免這種情況，在 RR 隔離級別下，在 #1 執行時不僅要鎖住現有的 ID=5 的索引，還需要阻止 ID = 5 的記錄插入(即 #2)。而 Gap Lock 就是實現這個目的的一種手段。

考慮到索引是有序的，因此如果索引里有 [3, 5, 5, 7] 這幾個元素，則可以通過鎖住 (3, 5) 、 (5, 7) 這幾個區間，加上 [5] 這幾個已經存在的元素，就可以阻止 ID = 5 的記錄插入。Gap Lock(間隙鎖)的含義是鎖住區間，而如果加上右邊的閉區間，如 (3, 5] 就稱為記錄 5 的 Next-Key Lock。

InnoDB 在掃描行時會為掃到的行加上 Next-Key Lock，對于上面的數據，掃到記錄 5 時，會加上 (3, 5] 鎖，同時，還會對下一個記錄加上 Gap Lock，即 (5, 7) ，造成 (3, 7) 都無法插入的現象，驗證 MySQL 實現如下：

首先建表準備數據：

-- 建表 
CREATE TABLE id_si_rr(pk int primary key, id int, name varchar(32)); 
CREATE INDEX id_si ON id_si_rr(id); 
 
 
-- 準備數據 
INSERT INTO id_si_rr values(1, 1, 'a'); 
INSERT INTO id_si_rr values(2, 3, 'b'); 
INSERT INTO id_si_rr values(3, 5, 'c'); 
INSERT INTO id_si_rr values(4, 7, 'c'); 
INSERT INTO id_si_rr values(5, 5, 'b');

執行語句：

-- 設置為 RC 隔離級別 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
BEGIN; -- 開啟事務 
DELETE FROM id_si_rr WHERE id = 5; 
-- 先不結束事務，驗證 Monitor Output 再用 ROLLBACK; 回滾

Monitor 輸出日志(省略 PHYSICAL RECORD 的內容)：

---TRANSACTION 929891, ACTIVE 6 sec 
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9442 localhost root 
TABLE LOCK table `test`.`id_si_rr` trx id 929891 lock mode IX 
RECORD LOCKS space id 1820 page no 4 n bits 72 index id_si of table `test`.`id_si_rr` trx id 929891 lock_mode X 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 ... 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
 ... 
 
RECORD LOCKS space id 1820 page no 3 n bits 72 index PRIMARY of table `test`.`id_si_rr` trx id 929891 lock_mode X locks rec but not gap 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
 
RECORD LOCKS space id 1820 page no 4 n bits 72 index id_si of table `test`.`id_si_rr` trx id 929891 lock_mode X locks gap before rec 
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 ...

首先我們看到：

id_si 
id_si

為什么唯一索引 + RR 就不需要 Gap Lock 呢?是因為我們的核心目的是不讓其它事務插入 ID = 5 的記錄，如果 ID 是唯一索引，鎖住記錄本身就能夠滿足要求了，不再需要 Gap Lock。

無索引 + RR

結論：對所有行都加記錄鎖，且索引前后都要加 Gap Lock

首先建表準備數據：

-- 建表 
CREATE TABLE id_ni_rr(pk int primary key, id int, name varchar(32)); 
 
-- 準備數據 
INSERT INTO id_ni_rr values(1, 1, 'a'); 
INSERT INTO id_ni_rr values(2, 3, 'b'); 
INSERT INTO id_ni_rr values(3, 5, 'c'); 
INSERT INTO id_ni_rr values(4, 7, 'c'); 
INSERT INTO id_ni_rr values(5, 5, 'b');

執行語句：

-- 設置為 RC 隔離級別 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
BEGIN; -- 開啟事務 
DELETE FROM id_ni_rr WHERE id = 5; 
-- 先不結束事務，驗證 Monitor Output 再用 ROLLBACK; 回滾

Monitor 輸出日志(省略了部分信息)：

---TRANSACTION 929980, ACTIVE 5 sec 
2 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9529 localhost root 
TABLE LOCK table `test`.`id_ni_rr` trx id 929980 lock mode IX 
RECORD LOCKS space id 1822 page no 3 n bits 72 index PRIMARY of table `test`.`id_ni_rr` trx id 929980 lock_mode X 
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 
 0: len 8; hex 73757072656d756d; asc supremum;; 
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 
 ... 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 
 ... 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 
 ... 
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ...

首先看到 TABLE LOCK 的狀態是 IX 說明沒有加表鎖。同時看到鎖住了 heap no 2~6的記錄，對應數據庫中的 5 條記錄。另外這里的鎖是 Next Key Lock，加上 heap no 為 1 的 “supremum” 記錄的 gap lock，鎖住了所有已經存在和不存在的行。因此如果執行 SELECT * FROM id_ni_rc WHERE id = 0 FOR UPDATE 也會阻塞，盡管 0 記錄不在數據庫中。

死鎖驗證

死鎖與獲取鎖的順序有關，一條語句(如 INSERT、DELETE)中對不同行、不同索引的加鎖存在先后，因此不同事務內的語句執行時，有可能產生死鎖。常見死鎖原因(摘自 MySQL InnoDB鎖和死鎖 )：

同一索引上，兩個session相反的順序加鎖多行記錄
UPDATE/DELETE 通過不同的二級索引更新多條記錄，可能造成在 Primary key 上不同的加鎖順序
Primary key 和 Secondary index，通過 primary key 找到記錄，更新 Secondary index 字段與通過 Secondary index 更新記錄

樣例情形：

首先建表準備數據：

CREATE TABLE deadlock(id int primary key, name varchar(32), reg int); 
CREATE INDEX deadlock_name ON deadlock(name); 
CREATE INDEX deadlock_reg ON deadlock(reg); 
 
-- 準備數據 
INSERT INTO deadlock values(1, 'x', 5); 
INSERT INTO deadlock values(2, 'b', 4); 
INSERT INTO deadlock values(3, 'x', 3); 
INSERT INTO deadlock values(4, 'd', 2); 
INSERT INTO deadlock values(5, 'e', 1);

兩個事務分別“同時”執行：

-- Transaction A                       | -- Transaction B 
DELETE FROM deadlock WHERE name = 'x'; | DELETE FROM deadlock WHERE reg >= 2;

其中一個事務可能會檢測到死鎖而出錯。Monitor 日志里找到 “LATEST DETECTED DEADLOCK” 可以看到記錄的死鎖原因(這個示例復現出的問題與上圖不直接一致)：

------------------------ 
LATEST DETECTED DEADLOCK 
------------------------ 
2020-12-13 15:59:40 0x700007a56000 
*** (1) TRANSACTION: 
TRANSACTION 930064, ACTIVE 0 sec starting index read 
mysql tables in use 1, locked 1 
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) 
MySQL thread id 1309, OS thread handle 123145430310912, query id 9616 localhost root updating 
DELETE FROM deadlock WHERE name = 'x' 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 1825 page no 3 n bits 72 index PRIMARY of table `test`.`deadlock` trx id 930064 lock_mode X locks rec but not gap waiting 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
 
*** (2) TRANSACTION: 
TRANSACTION 930063, ACTIVE 0 sec updating or deleting 
mysql tables in use 1, locked 1 
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 
MySQL thread id 1308, OS thread handle 123145430589440, query id 9615 localhost root updating 
DELETE FROM deadlock WHERE reg >= 2 
*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS space id 1825 page no 3 n bits 72 index PRIMARY of table `test`.`deadlock` trx id 930063 lock_mode X 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 
 ... 
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 1825 page no 4 n bits 72 index deadlock_name of table `test`.`deadlock` trx id 930063 lock_mode X locks rec but not gap waiting 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 ... 
 
*** WE ROLL BACK TRANSACTION (1)

我們看到：

第一個事務在等待 PRIMARY 索引上 heap_no = 2 的記錄的 Record Lock
第二個事務已經取得 PRIMARY 索引上 heap_no = 2 的 Next Key Lock
同時第二個事務在等待 deadlock_name 索引上 heap_no = 2 的 Record Lock
MySQL 選擇回滾第一個事務

更新操作如 UPDATE/DELETE 加鎖的順序為：查詢索引 > 主鍵索引 > 其它二級索引。如上例中，第二個事務已經鎖住了主鍵索引，準備鎖住另一個二級索引 deadlock_name ，而第一個已經鎖住了 deadlock_name ，準備鎖主鍵索引，造成死鎖。

責任編輯：未麗燕來源： lotabout.me

MySQL 記錄語句