行鎖：InnoDB 替代 MyISAM 的重要原因

2019 年 11 月 24 日
筆記

來源：慕課網專欄《一線數據庫工程師帶你深入理解 MySQL》地址：https://www.imooc.com/read/43 作者：馬聽老師

MySQL 5.5 之前的默認存儲引擎是 MyISAM，5.5 之後改成了 InnoDB。InnoDB 後來居上最主要的原因就是：

InnoDB 支持事務：適合在並發條件下要求數據一致的場景。
InnoDB 支持行鎖：有效降低由於刪除或者更新導致的鎖定。

本節就一起來探討 InnoDB 的行鎖。

在講解行鎖之前，我們首先來看一下兩階段鎖協議。

1 兩階段鎖

傳統的關係型數據庫加鎖的一個原則是：兩階段鎖原則。

兩階段鎖：鎖操作分為兩個階段，加鎖階段和解鎖階段，並且保證加鎖階段和解鎖階段不相交。在執行語句的時候加上鎖，但並不是語句執行完就立刻釋放鎖，而是要等到事務結束時才釋放。

我們可以通過下面這張表理解兩階段鎖：

序號	MySQL 操作	解釋	鎖階段
1	begin;	事務開始
2	insert into …..;	加 insert 對應的鎖	加鎖階段
3	update table …..;	加 update 對應的鎖	加鎖階段
4	delete from …..;	加 delete 對應的鎖	加鎖階段
5	commit;	事務結束，同時釋放 2、3、4 步驟中加的鎖	解鎖階段

2 InnoDB 行鎖模式

InnoDB 實現了以下兩種類型的行鎖：

共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。

對於普通 select 語句，InnoDB 不會加任何鎖，事務可以通過以下語句顯式給記錄集加共享鎖或排他鎖：

共享鎖（S）：select * from table_name where … lock in share mode;
排他鎖（X）：select * from tablbe_name where … for update;

3 InnoDB 行鎖算法

InnoDB 行鎖的三種算法：

Record Lock：單個記錄上的索引加鎖
Gap Lock：間隙鎖，對索引項之間的間隙加鎖，但不包括記錄本身
Next-Key Lock：Gap Lock + Record Lock，鎖定一個範圍，並且鎖定記錄本身。

InnoDB 行鎖實現特點意味着：如果不通過索引條件檢索數據，那麼 InnoDB 將對表中所有記錄加鎖，實際效果跟表鎖一樣。

4 事務隔離級別

不同事務隔離級別對應的行鎖也是不一樣的，因此在講解行鎖的鎖定範圍之前，先簡單聊聊事務隔離級別。事務隔離級別的詳細介紹放在下一章。

MySQL 的 4 種隔離級別：

Read uncommitted（讀未提交）: 在該隔離級別，所有事務都可以看到其他未提交的事務的執行結果。可能會出現臟讀。
Read Committed（讀已提交，簡稱：RC）：一個事務只能看見已經提交事務所做的改變。因為同一事務的其他實例在該實例處理期間可能會有新的 commit，所以可能出現幻讀。
Repeatable Read（可重複讀，簡稱：RR）：這是 MySQL 的默認事務隔離級別，它確保同一事務的多個實例在並發讀取數據時，會看到同樣的數據行。消除了臟讀、不可重複讀，默認也不會出現幻讀。
Serializable（串行）：這是最高的隔離級別，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。

這裡解釋一下臟讀和幻讀：

臟讀：讀取未提交的事務。
幻讀：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據。

5 RC 隔離級別下的行鎖實驗

有時我們可能會思考，某條語句（類似 select * from table_name where a=… for update）是怎麼加鎖的？

要想分析某條 SQL 是怎麼加鎖的，如果其他信息都不知道，那就得分幾種情況了，不同情況加鎖的方式也各不一樣，比較常見的一些情況如下：

RC 隔離級別，a 字段沒索引。
RC 隔離級別，a 字段有唯一索引。
RC 隔離級別，a 字段有非唯一索引。
RR 隔離級別，a 字段沒索引。
RR 隔離級別，a 字段有唯一索引。
RR 隔離級別，a 字段有非唯一索引。
……

Read uncommitted 和 Serializable 這兩種隔離級別在生產環境基本不用，就不做分析了。

我們先驗證 RC 隔離級別下的幾種情況，RR 隔離級別的實驗放在下節：

首先創建測試表及寫入數據:

use muke;    drop table if exists t16;    CREATE TABLE `t16` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `a` int(11) NOT NULL,   `b` int(11) NOT NULL,   `c` int(11) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `uniq_a` (`a`) USING BTREE,   KEY `idx_c` (`c`)  ) ENGINE=InnoDB  CHARSET=utf8mb4;    insert into t16(a,b,c) values (1,1,1),(2,2,2),(3,3,3),(4,4,3);

讓我們開始實驗吧！

5.1 通過非索引字段查詢

我們首先來看一下條件字段不使用索引的例子：

session1	session2
set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/	set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/
begin;
use muke;select * from t16 where b=1 for update;……1 row in set (0.00 sec)
	use muke;select * from t16 where b=2 for update;（等待）
commit;	select * from t16 where b=2 for update;……1 row in set (6.81 sec)（session1 執行 commit 後立馬返回結果）

這裡解釋一下為什麼要用 for update？ 我們常使用的查詢語句，比如 select * from t16 where b=1 屬於快照讀，是不會看到別的事務插入的數據的。而在查詢語句後面加了 for update 顯式給記錄集加了排他鎖，也就讓查詢變成了當前讀。插入、更新、刪除操作，都屬於當前讀。其實也就可以理解 select … for update 是為了讓普通查詢獲得插入、更新、刪除操作時所獲得的鎖。

表面看起來 session1 只給了 b=1 這一行加了排他鎖，但 session2 在請求其他行的排他鎖時，卻出現了鎖等待。看下圖：

由於 b 字段沒有索引，因此只能走聚簇索引，進行全表掃描。從上圖中可以看到，滿足條件的記錄有一條，但是聚簇索引上的所有記錄，都被加上了 X 鎖。

為什麼不是只在滿足條件的記錄上加鎖呢？

這是因為在 MySQL 中，如果一個條件無法通過索引快速過濾，那麼存儲引擎層面就會將所有記錄加鎖後返回，然後由 server 層進行過濾。因此也就把所有記錄都鎖上了。

當然 MySQL 在這裡有一些改進的，在 server 層過濾掉不滿足條件的數據後，會把不滿足條件的記錄放鎖。保證了最後只會持有滿足條件的鎖，但是每條記錄的加鎖操作還是不會省略。

總結：沒有索引的情況下，InnoDB 的當前讀會對所有記錄都加鎖。所以在工作中應該特別注意 InnoDB 這一特性，否則可能會產生大量的鎖衝突。

5.2 通過唯一索引查詢

我們再來看一下條件字段有唯一索引的例子：

session1	session2
set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/	set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/
begin;use muke;select * from t16 where a=1 for update;……1 row in set (0.00 sec)
	use muke;select * from t16 where a=2 for update;……1 row in set (0.00 sec)
	select * from t16 where a=1 for update;（等待）
commit;	select * from t16 where a=1 for update;……1 row in set (4.70 sec)（session1提交後，馬上返回結果）

session1 給了 a=1 這一行加了排他鎖，在 session2 中請求其他行的排他鎖時，不會發生等待；但是在 session2 中請求 a=1 這一行的排他鎖時，會發生等待。看下圖：

由於 a 是唯一索引，因此 select * from t16 where a=1 for update;(後面稱為 SQL2) 語句會選擇走 a 列的索引進行條件過濾，在找到 a=1 的記錄後，會將唯一索引上 a=1 索引記錄上加 X 鎖，同時，會根據讀取到的 id 列，回到聚簇索引，然後將 id=1 對應的聚簇索引項加 X 鎖。索引的原理可以複習第 10 節《為什麼添加索引能提高查詢速度》

為什麼聚簇索引上的記錄也要加鎖呢？

比如，並發的一條 SQL，是通過主鍵索引來更新：update t16 set b=10 where id =1; 如果 SQL2 沒有將主鍵索引上的記錄加鎖，那麼並發的 update 並不知道 SQL2 在執行，所以如果 update 執行了，就違背了同一記錄上的更新或者刪除需要串行執行的約束。

總結：如果查詢的條件是唯一索引，那麼 SQL 需要在滿足條件的唯一索引上加鎖，並且會在對應的聚簇索引上加鎖。

5.3 通過非唯一索引查詢

我們再來看一下條件字段有非唯一索引的例子：

session1	session2	session3
set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/	set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/	set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/
begin;
use muke;select * from t16 where c=3 for update;……2 rows in set (0.00 sec)
	use muke;select * from t16 where a=1 for update;……1 row in set (0.00 sec)	use muke;select * from t16 where a=2 for update;……1 row in set (0.00 sec)
	select * from t16 where a=3 for update;(等待)	select * from t16 where a=4 for update;(等待)
commit;	select * from t16 where a=3 for update;…..(session1提交後，馬上返回結果)	select * from t16 where a=4 for update;…..(session1提交後，馬上返回結果)

我們在滿足條件 c=3 的數據上加了排他鎖，如上面結果，就是第 3、4 行。因此第 1、2 行的數據沒被鎖，而 3、4 行的數據被鎖了。如下圖：

通過上圖可以看到，在 a 字段的非唯一索引上，滿足 c=3 的所有記錄，都被加了鎖。同時，對應的主鍵索引上的記錄也都加上了鎖。與通過唯一索引查詢的情況相比，唯一索引查詢最多有一行記錄被鎖，而非唯一索引將會把滿足條件的所有記錄都加上鎖。

總結：如果查詢的條件是非唯一索引，那麼 SQL 需要在滿足條件的非唯一索引上都加上鎖，並且會在它們對應的聚簇索引上加鎖。

6 總結

今天我們聊了一下 InnoDB 行鎖，這是 InnoDB 替代 MyISAM（只支持表鎖）的一個比較重要的原因。

在文稿的開始，跟大家講解了兩階段鎖、行鎖模式、行鎖算法以及事務隔離級別等。

我們做了 RC 隔離級別下不同場景的行鎖實驗，比較重要的一點是：在更新數據時，如果條件字段沒索引，則表中所有記錄都會被加上 X 鎖。所以在工作中應該儘可能的讓查詢走索引。

本節講解了 RC 隔離級別的鎖實驗，在下節會講解 RR 隔離級別下的行鎖情況，從而理解間隙鎖的意義。

7 問題

還是拿本節的測試表 t16，進行如下實驗：

session1	session2
set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/	set session transaction_isolation='READ-COMMITTED';/* 設置會話隔離級別為RC*/
begin;	begin;
	use muke;select * from t16 where c=3 for update;Result1
use muke;insert into t16(a,b,c) values (5,5,3);
commit;
	select * from t16 where c=3 for update;Result2
	commit;

Result1 和 Result 結果是相同的還是不同的，你可以實驗一下，然後分析為什麼會出現這種情況？歡迎把你的思路分享在留言區一起討論，我也會把結果驗證和原因分析放在下一節。

8 參考資料

何登成的 github：https://github.com/hedengcheng/tech/tree/master/database/MySQL

《深入淺出 MySQL》（第 2 版）：20.3.4 InnoDB 行鎖實現方式

更多 MySQL 乾貨，見慕課網專欄《一線數據庫工程師帶你深入理解 MySQL》

專欄知識圖如下：