MySQL加鎖機制的驗證記錄是什么

本篇內容介紹了“MySQL加鎖機制的驗證記錄是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

開啟 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 再釋放不匹配的記錄鎖

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

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. MySQL 加鎖時是對處理過程中“掃描”到的記錄加鎖，不管這條記錄最終是不是通過 WHERE 語句剔除了

3. 對于 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  ，準備鎖主鍵索引，造成死鎖。

“MySQL加鎖機制的驗證記錄是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！