InnoDB兩萬字詳解

語言: CN / TW / HK
時間 2022-06-05 21:27:03
主題:

前言

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

  1. 為什麼需要加鎖呢？

  2. InnoDB的七種鎖介紹

  3. 一條SQL是如何加鎖的

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

  5. 如何檢視事務加鎖情況

  6. 死鎖案例分析

1. 為什麼需要加鎖？

資料庫為什麼需要加鎖呢？

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

同理，對於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 請求訪問這條記錄時，會做如下處理： 

  • S
 T1和T2
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; 和事務C insert 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     ;;
  1. 先找到關鍵詞 TRANSACTION ，可以發現兩部分的事務日誌，如下：

  1. 檢視正在執行，產生死鎖的對應的SQL，如下：

  1. 檢視分開兩部分的TRANSACTION，分別持有什麼鎖，和等待什麼鎖。

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

最後

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

如果這篇文章對您有所幫助，或者有所啟發的話，幫忙掃描下發二維碼關注一下，您的支援是我堅持寫作最大的動力。

求一鍵三連：點贊、轉發、在看。

「」