本文將跟大家聊聊InnoDb的鎖，以及如何分析和解決死鎖問題，希望對大家有幫助哈。

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

• 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.

假設有表：

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 = 10這條記錄上加上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，因此事務Session 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卻不會阻塞了，事務會話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，

• 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. 看它持有什么鎖，等待什么鎖。

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，分別持有什么鎖，和等待什么鎖。

 

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

作者：撿田螺的小男孩
鏈接：
https://juejin.cn/post/7094049650428084232