兩萬字詳解！InnoDB 鎖專題！

前言

大家好，我是撿田螺的小男孩。本文將跟大家聊聊 InnoDB 的鎖。本文比較長，包括一條 SQL 是如何加鎖的，一些加鎖規則、如何分析和解決死鎖問題等內容，建議耐心讀完，肯定對大家有幫助的。

1. 爲什麼需要加鎖呢？

2. InnoDB 的七種鎖介紹

3. 一條 SQL 是如何加鎖的

4. RR 隔離級別下的加鎖規則

5. 如何查看事務加鎖情況

6. 死鎖案例分析

7. 爲什麼需要加鎖？

數據庫爲什麼需要加鎖呢？

在日常生活中，如果你心情不好。想要一個人靜靜，不想被比別人打擾，你就可以把自己關進房間裏，並且反鎖。

同理，對於 MySQL 數據庫來說的話，一般的對象都是一個事務一個事務來說的。所以，如果一個事務內，正在寫某個 SQL，我們肯定不想它被別的事務影響到嘛？因此，數據庫設計大叔，就給被操作的 SQL 加上鎖。

專業一點的說法: 如果有多個併發請求存取數據，在數據就可能會產生多個事務同時操作同一行數據。如果併發操作不加控制，不加鎖的話，就可能寫入了不正確的數據，或者導致讀取了不正確的數據，破壞了數據的一致性。因此需要考慮加鎖。

1.1 事務併發存在的問題

• 髒讀: 一個事務 A 讀取到事務 B 未提交的數據，就是髒讀。

• 不可重複讀：事務 A 被事務 B 干擾到了！在事務 A 範圍內，兩個相同的查詢，讀取同一條記錄，卻返回了不同的數據，這就是不可重複讀。

• 幻讀：事務 A 查詢一個範圍的結果集，另一個併發事務 B 往這個範圍中插入 / 刪除了數據，並靜悄悄地提交，然後事務 A 再次查詢相同的範圍，兩次讀取得到的結果集不一樣了，這就是幻讀。

1.2 一個加鎖和不加鎖對比的例子

我們知道 MySQL 數據庫有四大隔離級別讀已提交（RC）、可重複讀（RR）、串行化、讀未提交。如果是讀未提交隔離級別，併發情況下，它是不加鎖的，因此就會存在髒讀、不可重複讀、幻讀的問題。

爲了更通俗易懂一點，還是給大家舉個例子吧，雖然東西挺簡單的。假設現在有表結構和數據如下：

CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_name_idx` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into account(id,name,balance)values (1,'Jay',100);
insert into account(id,name,balance)values (2,'Eason',100);
insert into account(id,name,balance)values (3,'Lin',100);

在 READ-UNCOMMITTED（讀未提交） 隔離級別下，假設現在有兩個事務 A、B：

• 假設現在 Jay 的餘額是 100，事務 A 正在準備查詢 Jay 的餘額

• 這時候，事務 B 先扣減 Jay 的餘額，扣了 10

• 最後 A 讀到的是扣減後的餘額

手動驗證了一把，流程如下：

由上圖可以發現，事務 A、B 交替執行，事務 A 被事務 B 干擾到了，因爲事務 A 讀取到事務 B 未提交的數據, 這就是髒讀。爲什麼存在髒讀問題呢？這是因爲在讀未提交的隔離級別下執行寫操作，並沒有對 SQL 加鎖，因此產生了髒讀這個問題。

我們再來看下，在串行化隔離級別下，同樣的 SQL 執行流程，又是怎樣的呢？

爲啥會阻塞等待超時呢？這是因爲串行化隔離級別下，對寫的 SQL 加鎖啦。我們可以再看下加了什麼鎖，命令如下：

SET GLOBAL innodb_status_output=ON; -- 開啓輸出
SET GLOBAL innodb_status_output_locks=ON; -- 開啓鎖信息輸出
SHOW ENGINE INNODB STATUS

鎖相關的輸出內容如下：

我們可以看到了這麼一把鎖：lock_mode X locks rec but not gap，它到底是一種什麼鎖呢？來來來，我們一起來學習下 InnoDB 的七種鎖。

2. InnoDB 的七種鎖介紹

2.1 共享 / 排他鎖

InnoDB 呢實現了兩種標準的行級鎖：共享鎖（簡稱 S 鎖）、排他鎖（簡稱 X 鎖）。

• 共享鎖：簡稱爲 S 鎖，在事務要讀取一條記錄時，需要先獲取該記錄的 S 鎖。

• 排他鎖：簡稱 X 鎖，在事務需要改動一條記錄時，需要先獲取該記錄的 X 鎖。

如果事務T1持有行 R 的S鎖，那麼另一個事務T2請求訪問這條記錄時，會做如下處理：

• T2 請求S鎖立即被允許，結果 T1和T2都持有 R 行的S鎖

• T2 請求X鎖不能被立即允許, 此操作會阻塞

如果T1持有行 R 的X鎖，那麼T2請求 R 的X、S鎖都不能被立即允許，T2 必須等待T1釋放X鎖纔可以，因爲X鎖與任何的鎖都不兼容。

S鎖和X鎖的兼容關係如下圖表格：

X鎖和S鎖是對於行記錄來說的話，因此可以稱它們爲行級鎖或者行鎖。我們認爲行鎖的粒度就比較細，其實一個事務也可以在表級別下加鎖，對應的，我們稱之爲表鎖。給表加的鎖，也是可以分爲X鎖和S鎖的哈。

如果一個事務給表已經加了S鎖，則：

• 別的事務可以繼續獲得該表的S鎖，也可以獲得該表中某些記錄的S鎖。

• 別的事務不可以繼續獲得該表的X鎖，也不可以獲得該表中某些記錄的X鎖。

如果一個事務給表加了X鎖，那麼

• 別的事務不可以獲得該表的S鎖，也不可以獲得該表某些記錄的S鎖。

• 別的事務不可以獲得該表的X鎖，也不可以繼續獲得該表某些記錄的X鎖。

2.2 意向鎖

什麼是意向鎖呢？意向鎖是一種不與行級鎖衝突的表級鎖。未來的某個時刻，事務可能要加共享或者排它鎖時，先提前聲明一個意向。注意一下，意向鎖，是一個表級別的鎖哈。

爲什麼需要意向鎖呢？ 或者換個通俗的說法，爲什麼要加共享鎖或排他鎖時的時候，需要提前聲明個意向鎖呢呢？

因爲 InnoDB 是支持表鎖和行鎖共存的，如果一個事務 A 獲取到某一行的排他鎖，並未提交，這時候事務 B 請求獲取同一個表的表共享鎖。因爲共享鎖和排他鎖是互斥的，因此事務 B 想對這個表加共享鎖時，需要保證沒有其他事務持有這個表的表排他鎖，同時還要保證沒有其他事務持有表中任意一行的排他鎖。

然後問題來了，你要保證沒有其他事務持有表中任意一行的排他鎖的話，去遍歷每一行？這樣顯然是一個效率很差的做法。爲了解決這個問題，InnoDB 的設計大叔提出了意向鎖。

意向鎖是如何解決這個問題的呢？  我們來看下

意向鎖分爲兩類：

• 意向共享鎖：簡稱IS鎖，當事務準備在某些記錄上加 S 鎖時，需要現在表級別加一個IS鎖。

• 意向排他鎖：簡稱IX鎖，當事務準備在某條記錄上加上 X 鎖時，需要現在表級別加一個IX鎖。

比如：

• select ... lock in share mode，要給表設置IS鎖;

• select ... for update，要給表設置IX鎖;

意向鎖又是如何解決這個效率低的問題呢：

如果一個事務 A 獲取到某一行的排他鎖，並未提交, 這時候表上就有意向排他鎖和這一行的排他鎖。這時候事務 B 想要獲取這個表的共享鎖，此時因爲檢測到事務 A 持有了表的意向排他鎖，因此事務 A 必然持有某些行的排他鎖，也就是說事務 B 對錶的加鎖請求需要阻塞等待，不再需要去檢測表的每一行數據是否存在排他鎖啦。這樣效率就高很多啦。

意向鎖僅僅表明意向的鎖，意向鎖之間並不會互斥，是可以並行的，整體兼容性如下圖所示：

2.3 記錄鎖（Record Lock）

記錄鎖是最簡單的行鎖，僅僅鎖住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果 c1 字段是主鍵或者是唯一索引的話，這個 SQL 會加一個記錄鎖（Record Lock）

記錄鎖永遠都是加在索引上的，即使一個表沒有索引，InnoDB 也會隱式的創建一個索引，並使用這個索引實施記錄鎖。它會阻塞其他事務對這行記錄的插入、更新、刪除。

一般我們看死鎖日誌時，都是找關鍵詞，比如lock_mode X locks rec but not gap），就表示一個 X 型的記錄鎖。記錄鎖的關鍵詞就是 rec but not gap。以下就是一個記錄鎖的日誌：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4 間隙鎖（Gap Lock）

爲了解決幻讀問題，InnoDB 引入了間隙鎖(Gap Lock)。間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。它鎖住的是一個區間，而不僅僅是這個區間中的每一條數據。

比如lock_mode X locks gap before rec表示 X 型 gap 鎖。以下就是一個間隙鎖的日誌：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

2.5 臨鍵鎖 (Next-Key Lock)

Next-key 鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。說得更具體一點就是: 臨鍵鎖會封鎖索引記錄本身，以及索引記錄之前的區間，即它的鎖區間是前開後閉，比如(5,10]。

如果一個會話佔有了索引記錄 R 的共享 / 排他鎖，其他會話不能立刻在 R 之前的區間插入新的索引記錄。官網是這麼描述的：

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

2.6 插入意向鎖

插入意向鎖, 是插入一行記錄操作之前設置的**一種間隙鎖。**這個鎖釋放了一種插入方式的信號。它解決的問題是：多個事務，在同一個索引，同一個範圍區間插入記錄時，如果插入的位置不衝突，就不會阻塞彼此。

假設有索引值 4、7，幾個不同的事務準備插入 5、6，每個鎖都在獲得插入行的獨佔鎖之前用插入意向鎖各自鎖住了 4、7 之間的間隙，但是不阻塞對方因爲插入行不衝突。以下就是一個插入意向鎖的日誌：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

鎖模式兼容矩陣（橫向是已持有鎖，縱向是正在請求的鎖）如下：

2.7 自增鎖

自增鎖是一種特殊的表級別鎖。它是專門針對AUTO_INCREMENT類型的列，對於這種列，如果表中新增數據時就會去持有自增鎖。簡言之，如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

官方文檔是這麼描述的：

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

假設有表結構以及自增模式是 1，如下：

mysql> create table t0 (id int NOT NULL AUTO_INCREMENT,name varchar(16),primary key ( id));

mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set, 1 warning (0.01 sec)

設置事務 A 和 B 交替執行流程如下：

通過上圖我們可以看到，當我們在事務 A 中進行自增列的插入操作時，另外會話事務 B 也進行插入操作，這種情況下會發生 2 個奇怪的現象：

• 事務 A 會話中的自增列好像直接增加了 2 個值。（如上圖中步驟 7、8）

• 事務 B 會話中的自增列直接從 2 開始增加的。（如上圖步驟 5、6）

自增鎖是一個表級別鎖，那爲什麼會話 A 事務還沒結束，事務會話 B 可以執行插入成功呢？不是應該鎖表嘛？

這是因爲在參數innodb_autoinc_lock_mode上，這個參數設置爲1的時候，相當於將這種auto_inc lock弱化爲了一個更輕量級的互斥自增長機制去實現，官方稱之爲mutex。

innodb_autoinc_lock_mode還可以設置爲 0 或者 2，

• 0：表示傳統鎖模式，使用表級AUTO_INC鎖。一個事務的INSERT-LIKE語句在語句執行結束後釋放 AUTO_INC 表級鎖，而不是在事務結束後釋放。

• 1: 連續鎖模式, 連續鎖模式對於Simple inserts不會使用表級鎖，而是使用一個輕量級鎖來生成自增值，因爲 InnoDB 可以提前直到插入多少行數據。自增值生成階段使用輕量級互斥鎖來生成所有的值，而不是一直加鎖直到插入完成。對於bulk inserts類語句使用 AUTO_INC 表級鎖直到語句完成。

• 2: 交錯鎖模式, 所有的INSERT-LIKE語句都不使用表級鎖，而是使用輕量級互斥鎖。

• INSERT-LIKE: 指所有的插入語句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA 等。

• Simple inserts: 指在插入前就能確定插入行數的語句，包括：INSERT、REPLACE，不包含 INSERT…ON DUPLICATE KEY UPDATE 這類語句。

• Bulk inserts: 指在插入錢不能確定行數的語句，包括：INSERT … SELECT/REPLACE … SELECT/LOAD DATA。

3. 一條 SQL 是如何加鎖的呢？

介紹完 InnoDB 的七種鎖後，我們來看下一條 SQL 是如何加鎖的哈，現在可以分 9 種情況進行：

• 組合一：查詢條件是主鍵，RC 隔離級別

• 組合二：查詢條件是唯一索引，RC 隔離級別

• 組合三：查詢條件是普通索引，RC 隔離級別

• 組合四：查詢條件上沒有索引，RC 隔離級別

• 組合五：查詢條件是主鍵，RR 隔離級別

• 組合六：查詢條件是唯一索引，RR 隔離級別

• 組合七：查詢條件是普通索引，RR 隔離級別

• 組合八：查詢條件上沒有索引，RR 隔離級別

• 組合九：Serializable 隔離級別

3.1  查詢條件是主鍵 + RC 隔離級別

在 RC（讀已提交） 的隔離級別下，對查詢條件列是主鍵 id 的話，會加什麼鎖呢？

我們搞個簡單的表，初始化幾條數據：

create table t1 (id int,name varchar(16),primary key ( id));
insert into t1 values(1,'a'),(3,'c'),(6,'b'),(9,'a'),(10,'d');

假設給定 SQL：delete from t1 where id = 6;，id 是主鍵。在 RC 隔離級別下，只需要將主鍵上id = 6的記錄，加上X鎖即可。

我們來驗證一下吧，先開啓事務會話 A，先執行以下操作：

begin;
//刪除id=6的這條記錄
delete from t1 where id = 6;

接着開啓事務會話 B

begin;
update t1 set name='b1' where id =6;
//發現這個阻塞等待，最後超時釋放鎖了

驗證流程圖如下：

事務會話 B 對id=6的記錄執行更新時，發現阻塞了，打開看下加了什麼鎖。發現是因爲id=6這一行加了一個 X 型的記錄鎖

如果我們事務 B 不是對id=6執行更新，而是其他記錄的話，是可以順利執行的，如下：

結論就是，在 RC（讀已提交） 的隔離級別下，對查詢條件是主鍵 id 的場景，會加一個排他鎖（X 鎖），或者說加一個 X 型的記錄鎖。

3.2 查詢條件是唯一索引 + RC 隔離級別

如果查詢條件 id，只是一個唯一索引呢？那在 RC（讀提交隔離級別下），又加了什麼鎖呢？我們搞個新的表，初始化幾條數據：

create table t2 (name varchar(16),id int,primary key (name),unique key(id));
insert into t2 values('a',1),('c',3),('b',6),('d',9);

id 是唯一索引，name 是主鍵的場景下，我們給定 SQL：delete from t2 where id = 6;。

在 RC 隔離級別下，該 SQL 需要加兩個X鎖，一個對應於 id 唯一索引上的id = 6的記錄，另一把鎖對應於聚簇索引上的[name=’b’,id=6]的記錄。

爲什麼主鍵索引上的記錄也要加鎖呢？

如果併發的一個 SQL，是通過主鍵索引來更新：update t2 set id = 666 where name = 'b';此時，如果 delete 語句沒有將主鍵索引上的記錄加鎖，那麼併發的 update 就會感知不到 delete 語句的存在，違背了同一記錄上的更新 / 刪除需要串行執行的約束。

3.3  查詢條件是普通索引 + RC 隔離級別

如果查詢條件是普通的二級索引，在 RC（讀提交隔離級別下），又加了什麼鎖呢？

若 id 列是普通索引，那麼對應的所有滿足 SQL 查詢條件的記錄，都會加上鎖。同時，這些記錄對應主鍵索引，也會上鎖。

我們初始化下表結構和數據

create table t3 (name varchar(16),id int,primary key (name),key(id));
insert into t3 values('a',1),('c',3),('b',6),('e',6),('d',9);

加鎖示意圖如下：

我們來驗證一下，先開啓事務會話 A，先執行以下操作：

begin;
//刪除id=6的這條記錄
delete from t3 where id = 6;

接着開啓事務會話 B

begin;
update t3 set id=7 where name ='e';
//發現這個阻塞等待，最後超時釋放鎖了

實踐流程如下：

事務會話 B 爲什麼會阻塞等待超時，是因爲事務會話 A 的delete語句確實有加主鍵索引的 X 鎖

3.4 查詢條件列無索引 + RC 隔離級別

如果 id 沒有加索引，只是一個常規的列，在 RC（讀提交隔離級別下），又加了什麼鎖呢？

若 id 列上沒有索引，MySQL 會走聚簇索引進行全表掃描過濾。每條記錄都會加上 X 鎖。但是，爲了效率考慮，MySQL 在這方面進行了改進，在掃描過程中，若記錄不滿足過濾條件，會進行解鎖操作。同時優化違背了 2PL 原則。

初始化下表結構和數據

create table t4 (name varchar(16),id int,primary key (name));
insert into t4 values('a',1),('c',3),('b',6),('e',6),('d',9);

加鎖示意圖圖下：

驗證流程如下，先開啓事務會話 A，先執行以下操作：

begin;
//刪除id=6的這條記錄
delete from t4 where id = 6;

接着開啓事務會話 B

begin;
//可以執行，MySQL因爲效率問題，解鎖了
update t4 set name='f' where id=3;
//阻塞等待
update t4 set name='f' where id=6;

驗證結果如下：

3.5 查詢條件是主鍵 + RR 隔離級別

給定 SQL：delete from t1 where id = 6;，如果 id 是主鍵的話，在 RR 隔離級別下，跟 RC 隔離級別，加鎖是一樣的，也都是在id = 6這條記錄上加上X鎖。大家感興趣可以照着 3.1 小節例子，自己驗證一下哈。

3.6 查詢條件是唯一索引 + RR 隔離級別

給定 SQL：delete from t1 where id = 6;，如果 id 是唯一索引的話，在 RR 隔離級別下，跟 RC 隔離級別，加鎖也是一樣的哈，加了兩個X鎖，id 唯一索引滿足條件的記錄上一個，對應的主鍵索引上的記錄一個。

3.7 查詢條件是普通索引 + RR 隔離級別

如果查詢條件是普通的二級索引，在 RR（可重複讀的隔離級別下），除了會加X鎖，還會加間隙Gap鎖。Gap 鎖的提出，是爲了解決幻讀問題引入的，它是一種加在兩個索引之間的鎖。

假設有表結構和初始化數據如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如果一條更新語句update t5 set d=d+1 where c = 10，加鎖示意圖如下：

我們來驗證一下吧，先開啓事務會話 A，先執行以下操作：

begin;
update t5 set d=d+1 where c = 10;

接着開啓事務會話 B

begin;
insert into t5 values(12,12,12);
//阻塞等待，最後超時釋放鎖了

驗證流程圖如下：

爲什麼會阻塞呢？因此c=10這個記錄更新時，不僅會有兩把X鎖，還會把區間（10,15）加間隙鎖，因此要插入（12,12,12）記錄時，會阻塞。

3.8 查詢條件列無索引 + RR 隔離級別

如果查詢條件列沒有索引呢？又是如何加的鎖呢？

假設有表結構和數據如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

給定一條更新語句update t5 set d=d+1 where c = 10，因爲c列沒有索引，加鎖示意圖如下：

如果查詢條件列沒有索引，主鍵索引的所有記錄，都將加上X鎖，每條記錄間也都加上間隙Gap鎖。大家可以想象一下，任何加鎖併發的 SQL，都是不能執行的，全表都是鎖死的狀態。如果表的數據量大，那效率就更低。

在這種情況下，MySQL 做了一些優化，即semi-consistent read，對於不滿足條件的記錄，MySQL 提前釋放鎖，同時 Gap 鎖也會釋放。而semi-consistent read是如何觸發的呢：要麼在Read Committed隔離級別下；要麼在Repeatable Read隔離級別下，設置了innodb_locks_unsafe_for_binlog參數。但是semi-consistent read本身也會帶來其他的問題，不建議使用。

我們來驗證一下哈，先開啓事務會話 A，先執行以下操作：

begin;
update t5 set d=d+1 where c = 20;

接着開啓事務會話 B

begin;
insert into t5 values(16,16,16);
//插入阻塞等待
update t5 set d=d+1 where c = 16;
//更新阻塞等待

我們去更新一條不存在的c=16的記錄，也會被 X 鎖阻塞的。驗證如下：

3.9 Serializable 串行化

在 Serializable 串行化的隔離級別下，對於寫的語句，比如update account set balance= balance-10 where name=‘Jay’;，跟 RC 和 RR 隔離級別是一樣的。不一樣的地方是，在查詢語句，如select balance from account where name = ‘Jay’;，在 RC 和 RR 是不會加鎖的，但是在 Serializable 串行化的隔離級別，即會加鎖。

如文章開始第一小節的那個例子，就是類似的：

4. RR 隔離級別下，加鎖規則到底是怎樣的呢？

對於 RC 隔離級別，加的排他鎖（X 鎖），是比較好理解的，哪裏更新就鎖哪裏嘛。但是 RR 隔離級別，間隙鎖是怎麼加的呢？我們一起來學習一下。

對 InnoDb 的鎖來說，面試的時候問的比較多，就是Record lock、Gap lock、Next-key lock。接下來我們來學習，RR 隔離級別，到底一個鎖是怎麼加上去的。丁奇的 MySQL45 講有講到，RR 隔離級別，是如何加鎖的。大家有興趣可以去訂購看下哈，非常不錯的課程。

首先 MySQL 的版本，是5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。加鎖規則一共包括：兩個原則、兩個優化和一個bug。

• 原則 1：加鎖的基本單位都是next-key lock。next-key lock（臨鍵鎖）是前開後閉區間。

• 原則 2：查找過程中訪問到的對象纔會加鎖。

• 優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化爲行鎖（Record lock）。

• 優化 2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock退化爲間隙鎖（Gap lock）。

• 一個 bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值爲止。

假設有表結構和數據如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t5 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

分 7 個案例去分析哈：

1. 等值查詢間隙鎖

2. 非唯一索引等值鎖

3. 主鍵索引範圍鎖

4. 非唯一索引範圍鎖

5. 唯一索引範圍鎖 bug

6. 普通索引上存在 "等值" 的例子

7. limit 語句減少加鎖範圍

4.1 案例一：等值查詢間隙鎖

我們同時開啓 A、B、C 三個會話事務，如下：

發現事務 B 會阻塞等待，而 C 可以執行成功。如下：

爲什麼事務 B 會阻塞呢？

• 這是因爲根據加鎖原則 1：加鎖基本單位是next-key lock，因此事務會話 A 的加鎖範圍是（5,10]，這裏爲什麼是區間（5,10]，這是因爲更新的記錄，所在的表已有數據的區間就是 5-10 哈，又因爲next-key lock是左開右閉的，所以加鎖範圍是（5,10]。

• 同時根據優化 2，這是一個等值查詢 (id=6)，而 id=10 不滿足查詢條件。所以next-key lock退化成間隙Gap鎖，因此最終加鎖的範圍是(5,10)。

• 然後事務Session B中，你要插入的是 9,9 在區間 (5,10) 內，而區間 (5,10) 都被鎖了。因此事務 B 會阻塞等到。

爲什麼事務 C 可以正常執行呢？

這是因爲鎖住的區間是(5,10)，沒有包括 10，所以事務 C 可以正常執行。

4.2 案例二：非唯一索引等值鎖

按順序執行事務會話 A、B、C，如下：

發現事務 B 可以執行成功，而 C 阻塞等待。如下：

爲什麼事務會話 B 沒有阻塞，事務會話 C 卻阻塞了？

事務會話 A 執行時，會給索引樹c=5的這一行加上讀共享鎖。

1. 根據加鎖原則 1，加鎖單位是next-key lock，因此會加上next-key lock(0,5]。

2. 因爲 c 只是普通索引，所以僅訪問c=5這一條記錄時不會馬上停下來，需要繼續向右遍歷，查到c=10才結束。根據加鎖原則 2，訪問到的都要加鎖，因此要給(5,10]加next-key lock。

3. 由加鎖優化 2：等值判斷，向右遍歷，最後一個值 10不滿足c=5 這個等值條件，因此退化成間隙鎖 (5,10)。

4. 根據加鎖原則 2 ：只有訪問到的對象纔會加鎖，事務 A 的這個查詢使用了覆蓋索引，沒有回表，並不需要訪問主鍵索引，因此主鍵索引上沒有加任何鎖，事務會話 B 是對主鍵 id 的更新，因此事務會話 B 的update語句不會阻塞。

5. 但是事務會話 C，要插入一個（6,6,6) 的記錄時，會被事務會話 A 的間隙鎖(5,10)鎖住，因此事務會話 C 阻塞了。

4.3 案例三：主鍵索引範圍鎖

主鍵範圍查詢又是怎麼加鎖的呢？比如給定 SQL:

select * from t5 where id>=10 and id<11 for update;

按順序執行事務會話 A、B、C，如下：

執行結果如下：

發現事務會話 B 中，插入 12，即insert into t5 values(12,12,12);時，阻塞了，而插入 6，insert into t5 values(6,6,6);卻可以順利執行。同時事務 C 中，Update t5 set d=d+1 where id =15;也會阻塞，爲什麼呢？

事務會話 A 執行時，要找到第一個id=10的行：

• 根據加鎖原則 1：加鎖單位是next-key lock，因此會加上next-key lock(5,10]。

• 又因爲 id 是主鍵，也就是唯一值，因此根據優化 1：索引上的等值查詢，給唯一索引加鎖時，next-key lock退化爲行鎖（Record lock）。所以只加了id=10這個行鎖。

• 範圍查找就往後繼續找，找到id=15這一行停下來，因此還需要加next-key lock(10,15]。

事務會話 A 執行完後，加的鎖是id=10這個行鎖，以及臨鍵鎖next-key lock(10,15]。這就是爲什麼事務 B 插入 6 那個記錄可以順利執行，插入 12 就不行啦。同理，事務 C 那個更新 id=15 的記錄，也是會被阻塞的。

4.4 案例四：非唯一索引範圍鎖

如果是普通索引，範圍查詢又加什麼鎖呢？按順序執行事務會話 A、B、C，如下：

執行結果如下：

發現事務會話 B 和事務會話 C 的執行 SQL 都被阻塞了。

這是因爲，事務會話 A 執行時，要找到第一個c=10的行：

1. 根據加鎖原則 1：加鎖單位是 next-key lock，因此會加上next-key lock(5,10]。又因爲 c 不是唯一索引，所以它不會退化爲行鎖。因此加的鎖還是next-key lock(5,10]。

2. 範圍查找就往後繼續找，找到id=15這一行停下來，因此還需要加next-key lock(10,15]。

因此事務 B 和事務 C 插入的insert into t5 values(6,6,6);和Update t5 set d=d+1 where c =15; 都會阻塞。

4.5 案例五：唯一索引範圍鎖 bug

前面四種方案中，加鎖的兩個原則和兩個優化都已經用上啦，那個唯一索引範圍 bug 是如何觸發的呢？

按順序執行事務會話 A、B、C，如下：

執行結果如下：

發現事務 B 的更新語句Update t5 set d=d+1 where id =20; 和事務 Cinsert into t5 values(18,18,18);的插入語句均已阻塞了。

這是因爲，事務會話 A 執行時，要找到第一個id=15的行，根據加鎖原則 1：加鎖單位是next-key lock，因此會加上next-key lock(10,15]。因爲 id 是主鍵，即唯一的，因此循環判斷到 id=15 這一行就應該停止了。但是實現上，InnoDB 會往前掃描到第一個不滿足條件的行爲止，直到掃描到id=20。而且由於這是個範圍掃描，因此索引 id 上的(15,20]這個 next-key lock 也會被鎖上。

所以，事務 B 要更新 id=20 這一行時，會阻塞鎖住。同樣地事務會話 C 要插入id=16的一行，也會被鎖住。

4.6  案例六：普通索引上存在 "等值" 的例子

如果查詢條件列是普通索引，且存在相等的值，加鎖又是怎樣的呢？

在原來 t5 表的數據基礎上，插入：

insert into t5 values(28,10,66);

則c索引樹如下：

c 索引值有相等的，但是它們對應的主鍵是有間隙的。比如（c=10，id=10）和（c=10，id=28）之間。

我們來看個例子，按順序執行事務會話 A、B、C，如下：

執行結果如下：

爲什麼事務 B 插入語句會阻塞，事務 C 的更新語句不會呢？

• 這是因爲事務會話 A 在遍歷的時候，先訪問第一個c=10的記錄。它根據原則 1，加一個 (c=5,id=5) 到 (c=10,id=10) 的 next-key lock。

• 然後，事務會話 A 向右查找，直到碰到 (c=15,id=15) 這一行，循環才結束。根據優化 2，這是一個等值查詢，向右查找到了不滿足條件的行，所以會退化成(c=10,id=10) 到 (c=15,id=15)的間隙 Gap 鎖。即事務會話 A 這個select...for update語句在索引 c 上的加鎖範圍，就是下圖灰色陰影部分的：

因爲 c=13 是這個區間內的，所以事務 B 插入insert into t5 values(13,13,13);會阻塞。因爲根據優化 2，已經退化成 (c=10,id=10) 到 (c=15,id=15) 的間隙 Gap 鎖, 即不包括 c=15，所以事務 C，Update t5 set d=d+1 where c=15不會阻塞

4.7 案例七：limit 語句減少加鎖範圍

如果一個 SQL 有 limit，會不會對加鎖有什麼影響呢？我們用 4.6 的例子，然後給查詢語句加個 limit：

Select * from t5 where c=10 limit 2 for update;

事務 A、B 執行如下：

發現事務 B 並沒有阻塞，而是可以順利執行

這是爲什麼呢？跟上個例子，怎麼事務會話 B 的 SQL 卻不會阻塞了，事務會話 A 的select只是加多了一個limit 2。

這是因爲明確加了limit 2的限制後，因此在遍歷到 (c=10, id=30) 這一行之後，滿足條件的語句已經有兩條，循環就結束了。因此，索引 c 上的加鎖範圍就變成了從（c=5,id=5) 到（c=10,id=30) 這個前開後閉區間，如下圖所示：

索引平時我們寫 SQL 的時候，比如查詢select或者delete語句時，儘量加一下limit哈，你看着這個例子不就減少了鎖範圍了嘛，哈哈。

5. 如何查看事務加鎖情況

我門怎麼查看執行中的 SQL 加了什麼鎖呢？或者換個說法，如何查看事務的加鎖情況呢？有這兩種方法：

• 使用infomation_schema數據庫中的表獲取鎖信息

• 使用 show engine innodb status 命令

5.1 使用 infomation_schema 數據庫中的表獲取鎖信息

infomation_schema數據庫中，有幾個表跟鎖緊密關聯的。

• INNODB_TRX：該表存儲了 InnoDB 當前正在執行的事務信息，包括事務 id、事務狀態（比如事務是在運行還是在等待獲取某個所）等。

• INNODB_LOCKS：該表記錄了一些鎖信息，包括兩個方面：1. 如果一個事務想要獲取某個鎖，但未獲取到，則記錄該鎖信息。2. 如果一個事務獲取到了某個鎖，但是這個鎖阻塞了別的事務，則記錄該鎖信息。

• INNODB_LOCK_WAITS: 表明每個阻塞的事務是因爲獲取不到哪個事務持有的鎖而阻塞。

5.1.1 INNODB_TRX

我們在一個會話中執行加鎖的語句，在另外一個會話窗口，即可查看INNODB_TRX的信息啦，如下：

表中可以看到一個事務 id 爲1644837正在運行匯中，它的隔離級別爲REPEATABLE READ。我們一般關注這幾個參數：

• trx_tables_locked：該事務當前加了多少個表級鎖。

• trx_rows_locked：表示當前加了多少個行級鎖。

• trx_lock_structs：表示該事務生成了多少個內存中的鎖結構。

5.1.2 INNODB_LOCKS

一般系統中，發生某個事務因爲獲取不到鎖而被阻塞時，該表纔會有記錄。

事務 A、B 執行如下：

使用select * from information_schema.INNODB_LOCKS;查看

可以看到兩個事務 Id 1644842和1644843都持有什麼鎖，就是看那個lock_mode和lock_type哈。但是並看不出是哪個鎖在等待那個鎖導致的阻塞，這時候就可以看INNODB_LOCK_WAITS表啦。

5.1.3 INNODB_LOCK_WAITS

INNODB_LOCK_WAITS 表明每個事務是因爲獲取不到哪個事務持有的鎖而阻塞。

• requesting_trx_id：表示因爲獲取不到鎖而被阻塞的事務的事務 id

• blocking_trx_id：表示因爲獲取到別的事務需要的鎖而導致其被阻塞的事務的事務 Id。

即requesting_trx_id表示事務 B 的事務 Id，blocking_trx_id表示事務 A 的事務 Id。

5.2 show engine innodb status

INNODB_LOCKS 和 INNODB_LOCK_WAITS 在 MySQL 8.0 已被移除，其實就是不鼓勵我們用這兩個表來獲取表信息。而我們還可以用show engine innodb status獲取當前系統各個事務的加鎖信息。

在看死鎖日誌的時候，我們一般先把這個變量innodb_status_output_locks打開哈，它是 MySQL 5.6.16 引入的

set global  innodb_status_output_locks =on;

在 RR 隔離級別下，我們交替執行事務 A 和 B：

show engine innodb status 查看日誌，如下：

TRANSACTIONS
------------
Trx id counter 1644854
Purge done for trx's n:o < 1644847 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283263895935640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1644853, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 11956, query id 563 localhost ::1 root update
insert into t5 values(6,6,6)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

------------------
TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

這結構鎖的關鍵詞需要記住一下哈：

• lock_mode X locks gap before rec表示 X 型的 gap 鎖

• lock_mode X locks rec but not gap表示 X 型的記錄鎖（Record Lock）

• lock mode X 一般表示 X 型臨鍵鎖（next-key 鎖）

以上的鎖日誌，我們一般關注點，是一下這幾個地方：

• TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED表示它在等這個鎖

• RECORD LOCKS space id 267 page no 4 n bits 80 index c of table `test2`.`t5` trx id 1644853 lock_mode X locks gap before rec insert intention waiting表示一個鎖結構，這個鎖結構的 Space ID 是 267，page number 是 4，n_bits 屬性爲 80，對應的索引是c，這個鎖結構中存放的鎖類型是 X 型的插入意向 Gap 鎖。

• 0: len 4; hex 8000000a; asc ;;對應加鎖記錄的詳細信息，8000000a 代表的值就是 10，a 的 16 進制是 10。

• TABLE LOCK table `test2`.`t5` trx id 1644853 lock mode IX 表示一個插入意向表鎖

這個日誌例子，其實理解起來，就是事務 A 持有了索引 c 的間隙鎖（~，10），而事務 B 想獲得這個 gap 鎖，而獲取不到，就一直在等待這個插入意向鎖。

6. 手把手死鎖案例分析

如果發生死鎖了，我們應該如何分析呢？一般分爲四個步驟：

1. show engine innodb status，查看最近一次死鎖日誌。

2. 分析死鎖日誌，找到關鍵詞TRANSACTION

3. 分析死鎖日誌，查看正在執行的 SQL

4. 看 SQL 持有什麼鎖，又在等待什麼鎖。

6.1 一個死鎖的簡單例子

表結構和數據如下：

CREATE TABLE t6 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t6 values(5,5,5),(10,10,10);

我們開啓 A、B 事務，執行流程如下：

6.2 分析死鎖日誌

1. show engine innodb status，查看最近一次死鎖日誌。如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-03 22:53:22 0x2eb4
*** (1) TRANSACTION:
TRANSACTION 1644867, ACTIVE 31 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 5, OS thread handle 7068, query id 607 localhost ::1 root statistics
Select * from t6 where id=10 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644867 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd00000191011d; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1644868, ACTIVE 17 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 11956, query id 608 localhost ::1 root statistics
Select * from t6 where id=5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd00000191011d; asc        ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table `test2`.`t6` trx id 1644868 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000019193c; asc      <;;
 2: len 7; hex dd000001910110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

2. 先找到關鍵詞TRANSACTION，可以發現兩部分的事務日誌，如下：

3. 查看正在執行，產生死鎖的對應的 SQL，如下：

4. 查看分開兩部分的 TRANSACTION，分別持有什麼鎖，和等待什麼鎖。

所謂的死鎖，其實就是，我持有你的需要的鎖，你持有我需要的鎖，形成相互等待的閉環。所以排查死鎖問題時，照着這個思維去思考就好啦。

最後

本文參考了極客時間《MySQL45 講》，其實這個課程挺好的，我看了幾遍啦。建議有興趣的小夥伴們都買來看看哈。

撿田螺的小男孩 專注後端技術棧，熱愛分享，熱愛交朋友，熱愛工作總結。畢業於華南理工大學，軟件工程專業~

參考與感謝

• 一條簡單的更新語句，MySQL 是如何加鎖的 [1]

• 極客時間《MySQL45 講》[2]

• 《MySQL 是怎麼運行的？》

• 這次終於懂了，InnoDB 的七種鎖 [3]

• MySQL InnoDB 中的鎖 - 自增鎖（AUTO-INC Locks）[4]

參考資料

[1]

一條簡單的更新語句，MySQL 是如何加鎖的: https://urlify.cn/f6ZnIn

[2]

極客時間《MySQL45 講》: https://time.geekbang.org/column/article/75659

[3]

這次終於懂了，InnoDB 的七種鎖: https://developer.51cto.com/article/660111.html

[4]

MySQL InnoDB 中的鎖 - 自增鎖（AUTO-INC Locks）: https://blog.csdn.net/fofcn/article/details/123243225?spm=1001.2101.3001.6650.4&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-4.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-4.pc_relevant_default&utm_relevant_index=9

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/FwtMWBT_GBH2cSgod2oUzA