博客 / 詳情

返回

InnoDB 的併發控制：鎖與 MVCC

原文：https://nullwy.me/2023/07/innodb-locking/
如果覺得我的文章對你有用，請隨意讚賞

目前主流數據庫事務的併發控制實現，如 MySQL InnoDB、PostgreSQL、Oracle，都使用兩階段封鎖 2PL 與 MVCC 技術，但具體實現細節上存在差異。InnoDB 是在以封鎖技術為主體的情況下，用 MVCC 技術輔助實現讀-寫、寫-讀操作的併發。PostgreSQL 的併發控制技術是以 MVCC 技術為主，封鎖技術為輔。本文主要關注 InnoDB 事務的併發控制實現。

背景知識

併發控制，是數據庫系統的 ACID 特性中的隔離性（Isolation）的保障。所謂隔離性，就是事務的執行不應受到其他併發執行事務的干擾，事務的執行看上去應與其他事務是隔離的。被隔離的執行，等價於事務的某種串行執行，或者説，它等價於一個沒有併發的執行。保證串行性可能只允許極小的併發度，採用較弱隔離性，能帶來更高的併發度，是併發事務的正確性和性能之間的妥協。

早期各大數據庫廠商實現併發控制時多采用基於封鎖的併發控制技術，所以在基於封鎖的技術背景下，才在 ANSI SQL-92 標準中提出了四種隔離級別：未提交讀（Read Uncommitted）、己提交讀（Read Committed）、可重複讀（Repeatable Read）、可串行化（Serializable）（附註：為了書寫簡便本文將各個隔離級別依次縮寫為 RU、RC、RR、SER）。ANSI SQL-92 標準的四種隔離級別，是根據三種讀異常現象（phenomena）定義的，隔離級別和異常現象的關係如下：

隔離級別 P1 髒讀 P2 不可重複讀 P4 幻讀
Read Uncommitted 可能 可能 可能
Read Committed 避免 可能 可能
Repeatable Read 避免 避免 可能
Serializable 避免 避免 避免

ANSI SQL-92 標準文檔對三種讀異常現象的定義原文如下 [ref]：

The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL-transactions. The following phenomena are possible:
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

除了髒讀、不可重複讀和幻讀這 3 種讀數據異常外，還有寫數據異常，即髒寫和丟失更新。各個異常的含義如下：

  • P0 髒寫（Dirty Write）：事務 T1 寫某數據項，並且未提交或回滾，事務 T2 也寫該數據項，然後事務 T1 或事務 T2 回滾，回滾導致另外一個事務的修改被連帶回滾。髒寫異常會導致事務無法回滾，原子性無法得到保障，所以全部隔離級別下都應該避免。髒寫也可以叫回滾丟失。
  • P1 髒讀（Dirty Read）：讀到了其他事務還未提交的數據。
  • P2 不可重複讀（Non-Repeatable）：事務 T1 讀取某數據項，事務 T2 修改 update 或刪除 delete 該數據項，事務 T1 再次讀取該數據項，結果不同。
  • P3 幻讀（Phantom）：事務 T1 讀取滿足某條件的數據項集合，事務 T2 生成新的滿足該條件的數據項，事務 T2 再次讀取滿足該條件的數據項集合，結果不同。
  • P4 丟失更新（Lost Update）：事務 T1 讀取某數據項，事務 T2 更新該數據項並提交，事務 T1 忽略事務 T2 的更新，直接基於最初的讀取數據項做更新並提交，導致事務 T2 的更新丟失。丟失更新也可以叫覆蓋丟失。

各個異常的讀寫操作序列的簡化符號表示如下 [Berenson 1995]：

P0: w1[x]...w2[x]...(c1 or a1)                 事務 T2 髒寫
A1: w1[x]...r2[x]...(a1 and c2 in any order)   事務 T2 髒讀，r2[x] 為髒讀
A2: r1[x]...w2[x]...c2...r1[x]...c1            事務 T1 不可重複讀，兩次 r1[x] 結果不同
A3: r1[P]...w2[y in P]...c2...r1[P]...c1       事務 T1 幻讀，兩次 r1[P] 結果不同
P4: r1[x]...w2[x]...w1[x]...c1                 事務 T2 更新丟失，c1 導致 w2[x] 丟失

其中 w1[x] 表示事務 T1 寫入記錄 x，r1[x] 表示事務 T1 讀取記錄 x，c1 表示事務 T1 提交，a1 表示事務 T1 回滾，r1[P] 表示事務 T1 按照謂詞 P 的條件讀取若干條記錄，w1[y in P] 表示事務 T1 寫入記錄 y 滿足謂詞 P 的條件。

Berenson 的論文評判了 ANSI SQL-92 標準的異常定義。ANSI SQL-92 標準的異常的定義存在歧義，可以嚴格解釋，也可以寬鬆解釋，A1、A2 和 A3 的符號表示為嚴格解釋，按嚴格解釋，某些特殊的異常無法囊括，所以推薦寬鬆解釋。按照標準的定義，容易引起誤解的是，在排除 P1 髒讀、P2 不可重複、P3 幻讀這三種讀異常後就會得到可串行化隔離級別，但是事實並非如此。標準沒有定義 P0 髒寫和 P4 更新丟失異常。另外，基於 MVCC 技術實現的快照隔離（Snapshot Isolation），能避免標準定義的 P1 髒讀、P2 不可重複、P3 幻讀，並且避免 P0 髒寫和 P4 更新丟失，但還存在寫偏序（Write Skew）異常。

不可重複讀和幻讀的區別：

  • 不可重複讀對於事務 T2 的寫操作是更新或刪除操作，而幻讀對於事務 T2 的寫操作是插入（插入的新數據滿足條件）或更新（使不滿足條件的數據在更新後滿足條件）操作。
  • 對於幻讀現象中事務 T2 的操作，如果操作是對現有數據的更新或刪除操作，則表明這樣的操作等同於不可重複讀，即是在多個行數據上進行更新或刪除，即在多個行數據上批量化重演了不可重複讀現象。
  • 不可重複讀和幻象最大的區別就是前者只需要“鎖住”（考慮）已經讀過的數據，而幻讀需要對“還不存在的數據“做出預防。不可重複讀現象中事務 T2 着眼於對現有數據進行操作；而幻讀現象中事務 T2 着眼於對新增（或不在鎖定範圍內已經存在的數據上做更新後而得的數據滿足了謂詞條件）數據。

異常由併發衝突引起，對應關係如下：

  • 寫寫衝突：P0 髒寫、P4 丟失更新
  • 寫讀衝突：P1 髒讀
  • 讀寫衝突：P2 不可重複讀、P3 幻讀

早期各大數據庫廠商實現併發控制時多采用基於封鎖的併發控制技術，所以在基於封鎖的技術背景下，才在ANSI SQL 標準中提出了四種隔離級別。基於鎖的併發控制技術的加鎖方式與隔離級別的關係表 [Berenson 1995]：

隔離級別 寫鎖 數據項的讀鎖 謂詞的讀鎖
Read Uncommitted 長寫鎖 無鎖要求 無鎖要求
Read Commited 長寫鎖 短讀鎖 短謂詞鎖
Repeatable Read 長寫鎖 長讀鎖 短謂詞鎖
Serializable 長寫鎖 長讀鎖 長謂詞鎖

説明：

  • 短鎖（short duration lock），當前正在執行的語句持有鎖，語句執行完畢鎖被釋放。長鎖（long duration lock），當鎖被持有後，直到事務提交之後才被釋放。
  • RU 隔離級別，阻止 P0，長寫鎖
  • RC 隔離級別，阻止 P0、P1，長寫鎖 + 短讀鎖 + 短謂詞鎖
  • RR 隔離級別，阻止 P0、P1、P4、P2，長寫鎖 + 長寫鎖 + 短謂詞鎖
  • SER 隔離級別，阻止 P0、P1、P4、P2、P3，長寫鎖 + 長寫鎖 + 長謂詞鎖

基於鎖的併發控制下，隔離級別和異常現象的關係：

隔離級別 P0 髒寫 P1 髒讀 P4 丟失更新 P2 不可重複讀 P4 幻讀
Read Uncommitted 避免 可能 可能 可能 可能
Read Committed 避免 避免 可能 可能 可能
Repeatable Read 避免 避免 避免 避免 可能
Serializable 避免 避免 避免 避免 避免

各個隔離級別在基於鎖的併發控制技術下的具體的實現説明（參考自騰訊李海翔的《數據庫事務處理的藝術》第 2 章）：

基於鎖的併發控制

基於鎖的併發控制，讀-讀操作可以併發執行，但讀-寫、寫-讀、寫-寫操作無法併發執行，阻塞等待。MVCC 結合封鎖技術，使得讀－寫、寫－讀操作互不阻塞，即只有寫－寫操作不能併發，併發度被提高到 75%，這就是 MVCC 被廣為使用的原因。

InnoDB 的併發控制以封鎖技術為主，MVCC 技術為輔助。讓我們先看下 InnoDB 的封鎖技術。

共享鎖與排他鎖

InnoDB 存儲引擎實現兩種標準的行級鎖模式，共享鎖（讀鎖）和排他鎖（寫鎖）[doc]：

  • 共享鎖（shared lock，S）：允許事務讀一行數據。
  • 排他鎖（exclusive Lock，X）：允許事務刪除或更新一行數據。

如果事務 T1 持有行 r 上的共享鎖（S），則來自某個不同事務 T2 的對行 r 上的鎖的請求將按如下方式處理：

  • T2 對 S 鎖的請求可以立即被授予。因此，T1 和 T2 持有 r 上的鎖。
  • T2 對 X 鎖的請求不能立即被授予。

如果事務 T1 持有行 r 上的排他鎖（X），則某個不同事務 T2 對 r 上任一類型的鎖的請求無法立即被授予。相反，事務 T2 必須等待事務 T1 釋放其對行 r 的鎖定。

共享鎖和排他鎖的兼容性：

待申請 \ 已持有 共享鎖 S 排他鎖 X
共享鎖 S 兼容 衝突
排他鎖 X 衝突 衝突

區分共享鎖（讀鎖）和排它鎖（寫鎖）後，讀鎖與讀鎖的併發可被允許進行，併發能力得以提高。

對於 update、delete 和 insert 語句，InnoDB 會自動給涉及數據集加排他鎖（X）；對於普通 select 語句，InnoDB 不會加任何鎖（SERIALIZABLE隔離級別下除外）；事務可以通過以下語句顯式給查詢 select 顯式加共享鎖或排他鎖：

  • 共享鎖（S）：select ... for share
  • 排他鎖（X）：select ... for update

現在讓我們來試驗下共享鎖和排他鎖。創建 tbl 表，並添加表數據：

create table tbl 
  (a int, b int, c int, d int, primary key(a), unique key(b), key(c));
insert into tbl values
  (10, 10, 10, 10), (20, 20, 20, 20), (30, 30, 30, 30), 
  (40, 40, 40, 40), (50, 50, 50, 50), (60, 60, 60, 60), 
  (70, 70, 70, 70), (80, 80, 80, 80), (90, 90, 90, 90),
  (100, 100, 100, 100);

InnoDB 的排它鎖示例，如下：

事務1 事務2
mysql> begin; mysql> begin;
-- 在 a = 10 的索引記錄上添加排他鎖
mysql> select * from tbl where a = 10 for update;
-- 阻塞，獲取 a = 10 的排他鎖超時
mysql> update tbl set b = 42 where a = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 阻塞，獲取 a = 10 的排他鎖超時
mysql> update tbl set b = 42 where a >= 10;
-- 阻塞，獲取 a = 10 的排他鎖超時
mysql> delete from tbl where a = 10;
-- 阻塞，獲取 a = 10 的排他鎖超時
mysql> select * from tbl where a = 10 for update;
-- 更新成功，可以獲得其他記錄的排他鎖
mysql> update tbl set b = 42 where a = 20;
mysql> commit;
-- 更新成功，在事務 1 釋放鎖後，其他事務可以獲取排他鎖
mysql> update tbl set b = 42 where a = 10;

InnoDB的共享鎖示例，如下：

事務1 事務2
mysql> begin; mysql> begin;
-- 在 a = 10 的索引記錄上添加共享鎖
mysql> select * from tbl where a = 10 for share;
-- 獲取 a = 10 的共享鎖成功
mysql> select * from tbl where a = 10 for share;
-- 阻塞，獲取 a = 10 的排他鎖超時
mysql> update tbl set b = 42 where a = 10;
mysql> commit;
-- 更新成功，在事務 1 釋放鎖後，其他事務可以獲取排他鎖
mysql> update tbl set b = 42 where a = 10;

多粒度與意向鎖

InnoDB 存儲引擎支持多粒度鎖定（multiple granularity locking），這種鎖定允許事務在行級上的鎖和表級上的鎖同時存在。為了支持在不同粒度上進行加鎖操作，InnoDB 存儲引擎支持一種額外的鎖方式，稱之為意向鎖（Intention Lock）。意向鎖是將鎖定的對象分為多個層次，意向鎖意味着事務希望在更細粒度上進行加鎖。

若將上鎖的對象看成一棵樹，那麼對最下層的對象上鎖，也就是對最細粒度的對象進行上鎖，那麼首先需要對粗粒度的對象上鎖。如果需要對頁上的記錄 r 進行上 X 鎖，那麼分別需要對數據庫 A、表、頁上意向鎖 IX，最後對記錄 r 上 X 鎖。若其中任何一個部分導致等待，那麼該操作需要等待粗粒度鎖的完成。

在一個對象加鎖之前，該對象的全部祖先節點均加上了意向鎖。希望給某個記錄加鎖的事務必須遍歷從根到記錄的路徑。在遍歷樹的過程中，該事務給各節點加上意向鎖。

粒度層次圖

舉例來説，假設在表 1 的記錄 r 上持有 X 鎖，表 1 上必定持有 IX 鎖。如果其他事務想在表 1 上加 S 表鎖或 X 表鎖，但與已有 IX 鎖不兼容，所以該事務需要等待。再舉例，假設表 1 持有 S 鎖，如果其他事務想在表 1 的記錄 r 上加 X 鎖，需要先獲得表 1 的 IX 鎖，但與已有 S 鎖不兼容，所以該事務需要等待。有了意向鎖之後，就能快速判斷行鎖和表鎖之間是否兼容。

InnoDB 存儲引擎支持意向鎖設計比較簡練，其意向鎖即為表級別的鎖，兩種意向鎖 [doc]：

  • 意向共享鎖（IS）：事務打算給數據行加行共享鎖（S），事務在給一個數據行加共享鎖（S）前必須先取得該表的 IS 鎖。
  • 意向排他鎖（IX）：事務打算給數據行加行排他鎖（X），事務在給一個數據行加排他鎖（X）前必須先取得該表的 IX 鎖。

IS、IX、S、X 鎖的兼容性：

待申請 \ 已持有 IS IX S X
IS 兼容 兼容 兼容 衝突
IX 兼容 兼容 衝突 衝突
S 兼容 衝突 兼容 衝突
X 衝突 衝突 衝突 衝突

兼容關係：各種意向鎖（IS、IX）之間全部兼容，意向共享鎖 IS 和共享鎖 S 兼容，共享鎖 S 和共享鎖 S 兼容，其他衝突。

SQL 語句可以分為數據定義語言（DDL）、數據控制語言（DCL）、數據查詢語言（DQL）、數據操縱語言（DML）四種類型的語句，前兩種語句，涉及的對象在數據之上，所以加鎖的範圍，通常是表級，對應表級鎖。後兩種語句操作的對象是數據，加鎖的範圍，通常是數據級，這就對應行級鎖。

三種行鎖：記錄鎖、間隙鎖和 next-key 鎖

InnoDB 行鎖分為 3 種類型 [doc]：

  • 記錄鎖（record lock）：對索引記錄項加鎖。
  • 間隙鎖（gap lock）：間隙鎖，對索引記錄項之間的“間隙”、笫一條記錄前的“間隙”或最後一條記錄後的“間隙“加鎖。鎖定一個範圍，但不包含索引記錄本身。
  • next-key 鎖（next-key Lock）：前兩種鎖的組合，記錄鎖 + 間隙鎖，鎖定一個範圍，並且鎖定索引記錄本身。（中文世界有時將 next-key lock 翻譯為“臨鍵鎖”）

如果索引上包含 10, 20, 30, 40, 50 這些記錄，那麼可能的 next-key 鎖的鎖區間（interval），如下：

(-無窮, 10]     即，間隙鎖 (-無窮, 10) + 記錄鎖 10。區間為，左開右閉區間
(10, 20]       即，間隙鎖 (10, 20) + 記錄鎖 20
(20, 30]       即，間隙鎖 (20, 30) + 記錄鎖 30
(30, 40]       即，間隙鎖 (30, 40) + 記錄鎖 40
(40, 50]       即，間隙鎖 (40, 50) + 記錄鎖 50
(50, +無窮]     即，間隙鎖 (50, +無窮)

最後一個鎖區間 (50, +無窮]，對應的是上界偽記錄（supremum pseudo-record），不是真實存在的記錄。這個鎖區間用於防止在最大值 50 之後插入記錄。

記錄鎖總是會去鎖住索引記錄，如果 InnoDB 存儲引擎表在建立的時候沒有設置任何一個索引，那麼這時 InnoDB 存儲引擎會使用隱式的主鍵來進行鎖定。

MySQL 默認的事務隔離級別是可重複讀（REPEATABLE-READ），如果把事務隔離級別改成已提交讀（READ-COMMITTED），間隙鎖會被禁用。禁用間隙鎖後，幻讀異常會出現，因為其他事務可以在間隙中插入新行。InnoDB 的間隙鎖，就是為了解決幻讀異常而引入的。關於幻讀異常，參見官方文檔 doc。

RR 隔離級別下，InnoDB 的鎖通常使用 next-key 鎖。但是，在唯一索引（和主鍵索引）上的等值查詢，next-key 鎖退化為記錄鎖，間隙鎖並不需要，即僅鎖住索引本身，而不是範圍。如果在唯一索引（和主鍵索引）上做範圍查詢，間隙鎖依然需要。官方文檔描述如下 [doc]：

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

間隙鎖是“純抑制性的”，間隙鎖唯一的作用就是為了防止其他事務的插入到間隙中。間隙鎖和間隙鎖之間是互不衝突的，所以間隙共享 S 鎖和間隙排他 X 鎖沒有任何區別。

另外，還有一種鎖叫插入意向鎖（insert intention lock），基於間隙鎖，專門用於 insert 操作。在執行 insert 操作時，需要先申請獲取插入意向鎖，也就是説，需要先檢查當前插入位置上的下一條記錄上是否持有間隙鎖，如果被間隙鎖鎖住，則鎖衝突，插入被阻塞。多個事務做 insert 操作，被相同的間隙鎖阻塞，如果插入的值各不相同，這些事務的 insert 操作之間不阻塞。

所以，間隙鎖與插入意向鎖的兼容關係是，已持有的間隙鎖與待申請的插入意向鎖衝突，而插入意向鎖之間兼容，在一個間隙鎖鎖上可以有多個意向鎖等待。

IS、IX、X、S 鎖和記錄鎖、間隙鎖、next-key 鎖的關係：

  • IS、IX、S、X 是鎖模式（lock mode）（源碼層面上對應 lock_mode 枚舉）。
  • 記錄鎖、間隙鎖、next-key 鎖、插入意向鎖是行鎖類型（record lock type）。
  • 每一種行鎖類型，都包含 IS、IX、S、X 鎖模式，如共享的記錄鎖、排他的記錄鎖、共享的間隙錄鎖、排他的間隙鎖等。

鎖監控：data_locks 和 data_lock_waits 表

MySQL 8.0 之前，information_schema 庫提供 innodb_trx、innodb_locks 和 innodb_lock_waits 三張表，用來監控事務和診斷潛在的鎖問題，具體介紹可以參見官方 5.7 文檔 doc。

  • innodb_trx：當前事務表
  • innodb_locks：鎖等待中的鎖信息表
  • innodb_lock_waits：鎖等待中的事務表

在 MySQL 8.0 之前，要想獲得當前已持有的鎖信息，需要開啓參數 innodb_status_output_locks 並且執行命令 show engine innodb status，具體介紹可以參見官方文檔“15.17 InnoDB Monitors”，doc。

MySQL 8.0 開始，innodb_locks 表和 innodb_lock_waits 表，被 performance_schema 庫的 data_locks 表和 data_lock_waits 表替代。其中值得注意的不同點是，新的 data_locks 表，同時包含了已持有的鎖和請求中的鎖的信息，這樣查看當前已持有的鎖信息更加方便。相關 SQL 示例：

-- 查詢全部鎖信息
select * from performance_schema.data_locks \G
-- 查詢全部記錄鎖的鎖信息
select * from performance_schema.data_locks where LOCK_TYPE = 'RECORD' \G
-- 查詢等待中的鎖信息
select * from performance_schema.data_locks where LOCK_STATUS = 'WAITING' \G
-- 查詢鎖等待中的事務
select * from performance_schema.data_lock_waits \G
-- 使用 sys 庫的 innodb_lock_waits 視圖
-- 查詢鎖等待中的事務
select * from sys.innodb_lock_waits \G

命令 show engine innodb status 的輸出和 data_locks 表的對應關係，可以參考文章 link。

行鎖加鎖案例分析

RR 隔離級別

本文的全部案例採用的 MySQL 版本為 8.0.30。MySQL 的默認事務隔離級別是 REPEATABLE-READ（可重複讀），事務隔離級別可以通過系統變量 transaction_isolation 控制。

-- 事務隔離級別，默認為可重複讀（Repeatable Read）
mysql> select @@global.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

tbl 表的數據如下：

mysql> select * from tbl;
+-----+------+------+------+
| a   | b    | c    | d    |
+-----+------+------+------+
|  10 |   10 |   10 |   10 |
|  20 |   20 |   20 |   20 |
|  30 |   30 |   30 |   30 |
|  40 |   40 |   40 |   40 |
|  50 |   50 |   50 |   50 |
|  60 |   60 |   60 |   60 |
|  70 |   70 |   70 |   70 |
|  80 |   80 |   80 |   80 |
|  90 |   90 |   90 |   90 |
| 100 |  100 |  100 |  100 |
+-----+------+------+------+
10 rows in set (0.00 sec)

(a1) 主鍵索引上的等值查詢

SQL 語句：

select * from tbl where a = 10 for update;

data_locks 表中的行鎖數據：

mysql> select * from performance_schema.data_locks where LOCK_TYPE = 'RECORD' \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4816436360:185:4:3:140408965390368
ENGINE_TRANSACTION_ID: 56664
            THREAD_ID: 367
             EVENT_ID: 22
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tbl
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140408965390368
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10

加鎖情況：

  • 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）

其他 SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 a = 10 的索引記錄上添加共享記錄鎖（S,REC_NOT_GAP）
select * from tbl where a = 10 for share;

-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
update tbl set b = 42 where a = 10;

-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
delete from tbl where a = 10;

加鎖與鎖衝突 SQL 演示：

事務1 事務2
mysql> begin; mysql> begin;
-- 在 a = 10 的索引記錄上添加排他記錄鎖
mysql> select * from tbl where a = 10 for update;
-- 阻塞，因為 a = 10 上存在排他記錄鎖
mysql> select * from tbl where a = 10 for update;
-- 阻塞，因為 a = 10 上存在排他記錄鎖
mysql> insert into tbl (a) values (10);
-- 插入成功
mysql> insert into tbl (a) values (9);
-- 插入成功
mysql> insert into tbl (a) values (11);
mysql> rollback; mysql> rollback;

(a2) 唯一索引上的等值查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 b = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where b = 10 for update;

-- 在 b = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 覆蓋索引，但系統會認為接下來要更新數據，因此會順便給主鍵索引上滿足條件的行加上行鎖
select a from tbl where b = 10 for update;

-- 在 b = 10 的索引記錄上添加共享記錄鎖（S,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加共享記錄鎖（S,REC_NOT_GAP）
select * from tbl where b = 10 for share;

-- 在 b = 10 的索引記錄上添加共享記錄鎖（S,REC_NOT_GAP）
-- 覆蓋索引，所以只在字段 b 上加鎖
select a from tbl where b = 10 for share;

-- 在 b = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
update tbl set b = 42 where b = 10;

-- 在 b = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
delete from tbl where b = 10;

上面的全部 SQL，除了走覆蓋索引的 select for share 外，其他的加鎖範圍都相同。

(a3) 非唯一索引上的等值查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 c = 10 的索引記錄上添加排他 next-key 鎖，區間為 (-無窮, 10]（X）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 c = 20 的索引記錄上添加排他間隙鎖，區間為 (10, 20)（X,GAP）
select * from tbl where c = 10 for update;

-- 在 c = 10 的索引記錄上添加共享 next-key 鎖，區間為 (-無窮, 10]（S）
-- 在 a = 10 的索引記錄上添加共享記錄鎖（S,REC_NOT_GAP）
-- 在 c = 20 的索引記錄上添加共享間隙鎖，區間為 (10, 20)（S,GAP）
select * from tbl where c = 10 for share;

-- 在 c = 10 的索引記錄上添加共享 next-key 鎖，區間為 (-無窮, 10]（S）
-- 在 c = 20 的索引記錄上添加共享間隙鎖，區間為 (10, 20)（S,GAP）
-- 覆蓋索引，所以只在字段 c 上加鎖
select a from tbl where c = 10 for share;

-- 在 c = 10 的索引記錄上添加排他 next-key 鎖，區間為 (-無窮, 10]（X）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 c = 20 的索引記錄上添加排他間隙鎖，區間為 (10, 20)（X,GAP）
update tbl set c = 42 where c = 10;

-- 在 c = 10 的索引記錄上添加排他 next-key 鎖，區間為 (-無窮, 10]（X）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 c = 20 的索引記錄上添加排他間隙鎖，區間為 (10, 20)（X,GAP）
delete from tbl where c = 10;

上面的全部 SQL，除了走覆蓋索引的 select for share 外，其他的加鎖範圍都相同。

加鎖與鎖衝突 SQL 演示：

事務1 事務2
mysql> begin; mysql> begin;
-- 在 c = 10 的索引記錄上添加排他 next-key 鎖，區間為 (-無窮, 10]
-- 在 a = 10 的索引記錄上添加排他記錄鎖
-- 在 c = 20 的索引記錄上添加排他間隙鎖，區間為 (10, 20)
mysql> select * from tbl where c = 10 for update;
-- 阻塞，因為 c = 10 上存在排他 next-key 鎖
mysql> select from tbl where c = 10 for update;
-- 阻塞，因為 a = 10 上存在排他記錄鎖
mysql> select from tbl where a = 10 for update;
-- 阻塞，因為 c = 10 上存在排他 next-key 鎖，區間為 (-無窮, 10]
mysql> insert into tbl (a, c) values (1, 9);
-- 阻塞，因為 c = 10 上存在排他 next-key 鎖，區間為 (-無窮, 10]
mysql> insert into tbl (a, c) values (1, 10);
-- 阻塞，因為 c = (10, 20) 區間存在間隙鎖
mysql> insert into tbl (a, c) values (1, 11);
-- 插入成功
mysql> insert into tbl (a, c) values (1, 21);
mysql> rollback; mysql> rollback;

(a4) 無索引的等值查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 a 主鍵的全部索引記錄上添加排他 next-key 鎖
select * from tbl where d = 10 for update;

因為字段 d 上沒有索引，這個 SQL 語句，只能在聚簇索引上全表掃描。加鎖情況，在 a 主鍵的全部索引記錄上添加排他 next-key 鎖。表 tbl 共 10 條記錄，全部的持有的 next-key 鎖的鎖區間，如下：

(-無窮, 10]
  (10, 20]
  (20, 30]
  (30, 40]
  (40, 50]
  (50, 60]
  (60, 70]
  (70, 80]
  (80, 90]
  (90, 100]
(100, +無窮]

(a5) 值不存在的等值查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

---- 主鍵索引上的值不存在的等值查詢
-- 在 a = 100 的索引記錄上添加排他間隙鎖，區間為 (90, 100)（X,GAP）
select * from tbl where a = 95 for update;
-- 在 a 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where a = 105 for update;

---- 唯一索引上的值不存在的等值查詢
-- 在 b = 100 的索引記錄上添加間隙鎖，區間為 (90, 100)（X,GAP）
select * from tbl where b = 95 for update;
-- 在 b 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where b = 105 for update;

---- 非唯一索引上的值不存在的等值查詢
-- 在 c = 100 的索引記錄上添加間隙鎖，區間為 (90, 100)
select * from tbl where c = 95 for update;
-- 在 c 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where c = 105 for update;

(b1) 主鍵索引上的範圍查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）
-- 在 a 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where a >= 90 for update;

-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where a >= 100 for update;

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他間隙鎖，區間為 (90, 100)（X,GAP）
-- 附註：與主鍵上的等值查詢 `a = 90` 的加鎖範圍的區別是額外加了區間為 (90, 100) 間隙鎖
select * from tbl where a >= 90 and a < 91 for update;

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他間隙鎖，區間為 (90, 100)（X,GAP）
-- 附註：與相同查詢條件的 `select for update`，加鎖範圍相同
update tbl set d = 42 where a >= 90 and a < 91;

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他間隙鎖，區間為 (90, 100)（X,GAP）
-- 附註：與相同查詢條件的 `select for update` 的 SQL，加鎖範圍相同
delete from tbl where a >= 90 and a < 91;

(b2) 唯一索引上的範圍查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where b >= 90 for update;

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
select * from tbl where b >= 90 and b < 91 for update;

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）（不必要的記錄鎖）
update tbl set d = 42 where b >= 90 and b < 91;

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）（不必要的記錄鎖）
delete from tbl where b >= 90 and b < 91;

加鎖與鎖衝突 SQL 演示：

事務1 事務2
mysql> begin; mysql> begin;
-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]
-- 在 a = 90 的索引記錄上添加排他記錄鎖
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]
mysql> select * from tbl where b >= 90 and b < 91 for update;
-- 阻塞，因為 b = 90 上存在排他 next-key 鎖
mysql> select from tbl where b = 90 for update;
-- 阻塞，因為 b = 100 上存在排他 next-key 鎖（不必要的記錄鎖）
mysql> select from tbl where b = 100 for update;
-- 阻塞，因為 a = 90 上存在排他記錄鎖
mysql> select * from tbl where a = 90 for update;
mysql> rollback; mysql> rollback;

(b3) 非唯一索引上的範圍查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b 的索引記錄上添加排他 next-key 鎖，區間為 (100, +無窮]（X）
select * from tbl where c >= 90 for update;

-- 在 c = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 c = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
select * from tbl where c >= 90 and c < 91 for update;

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）（不必要的記錄鎖）
update tbl set d = 42 where c >= 90 and c < 91;

-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）（不必要的記錄鎖）
delete from tbl where c >= 90 and c < 91;

對比，唯一索引上的範圍查詢的加鎖情況，容易得出結論，唯一索引和普通索引上的範圍查詢的加鎖規則相同。

RC 隔離級別

把事務隔離級別修改為已提交讀（Read Committed）：

-- 事務隔離級別，修改為已提交讀（Read Committed）
mysql> set @@transaction_isolation = 'READ-COMMITTED';

(a1) 主鍵索引上的等值查詢

SQL 語句的加鎖情況（通過查 data_locks 表確認）：

-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where a = 10 for update;

-- 在 a = 10 的索引記錄上添加共享記錄鎖（S,REC_NOT_GAP）
select * from tbl where a = 10 for share;

-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
update tbl set b = 42 where a = 10;

-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
delete from tbl where a = 10;

結論：因為主鍵索引上的等值查詢不涉及間隙鎖，所以 RR 和 RC 隔離級別下的加鎖規則相同。

(a2) 唯一索引上的等值查詢

同樣的，因為唯一索引上的等值查詢不涉及間隙鎖，所以 RR 和 RC 隔離級別下的加鎖規則相同。

(a3) 非唯一索引上的等值查詢

-- 在 c = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where c = 10 for update;

-- 在 c = 10 的索引記錄上添加排他記錄鎖（S,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（S,REC_NOT_GAP）
select * from tbl where c = 10 for share;

-- 在 c = 10 的索引記錄上添加排他記錄鎖（S,REC_NOT_GAP）
-- 覆蓋索引，所以只在字段 b 上加鎖
select a from tbl where c = 10 for share;

-- 在 c = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
update tbl set c = 42 where c = 10;

-- 在 c = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
delete from tbl where c = 10;

(a4) 無索引的等值查詢

-- 在 a = 10 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where d = 10 for update;

(a5) 值不存在的等值查詢

---- 主鍵索引上的值不存在的等值查詢
-- 無鎖
select * from tbl where a = 95 for update;
-- 無鎖
select * from tbl where a = 105 for update;

---- 唯一索引上的值不存在的等值查詢
-- 無鎖
select * from tbl where b = 95 for update;
-- 無鎖
select * from tbl where b = 105 for update;

---- 非唯一索引上的值不存在的等值查詢
-- 無鎖
select * from tbl where c = 95 for update;
-- 無鎖
select * from tbl where c = 105 for update;

(b1) 主鍵索引上的範圍查詢

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where a >= 90 for update;

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where a >= 90 and a < 91 for update;

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
update tbl set d = 42 where a >= 90 and a < 91;

-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
delete from tbl where a >= 90 and a < 91;

(b2) 唯一索引上的範圍查詢

-- 在 b = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where b >= 90 for update;

-- 在 b = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
select * from tbl where b >= 90 and b < 91 for update;

-- 在 b = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
update tbl set d = 42 where b >= 90 and b < 91;

-- 在 b = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
delete from tbl where b >= 90 and b < 91;

(b3) 非唯一索引上的範圍查詢

加鎖情況，和 RC 隔離級別的唯一索引上的範圍查詢完全相同。

行鎖加鎖規則總結

RC 隔離級別時的加鎖規則：

  • 間隙鎖被禁用，只有記錄鎖，沒有間隙鎖和 next-key 鎖。
  • 對全部滿足查詢條件的索引記錄加記錄鎖。如果查詢條件滿足覆蓋索引，就只對輔助索引加記錄鎖。如果需要回表，對輔助索引記錄和聚簇索引記錄引同時加鎖。
  • 如果不存在滿足查詢條件的索引記錄，就不加鎖。

RR 隔離級別時的加鎖規則：

  • 加鎖的基本單位是 next-key 鎖。
  • 對全部滿足查詢條件的索引記錄加 next-key 鎖。如果查詢條件滿足覆蓋索引，就只對輔助索引加記錄鎖。如果需要回表，對輔助索引記錄和聚簇索引記錄引同時加鎖。
  • 範圍查詢時，或值不存在的等值查詢時，在從右掃描到的最後的不滿足查詢條件的記錄上加間隙鎖。如果索引的最大記錄值，滿足查詢條件，則在上界偽記錄（supremum pseudo-record）上加 next-key 鎖（相當於間隙鎖）。
  • 等值查詢時，在主鍵索引和唯一索引上加鎖，next-key 鎖退化為記錄鎖。

範圍查詢時的不必要加鎖 bug

注意，RR 隔離級別時，在主鍵索引上的範圍查詢時，確實是按上文的規則加間隙鎖。但實際驗證發現，在輔助索引（包括唯一索引和普通索引）上的範圍查詢時，在最後的不滿足查詢條件的記錄上實際加的是 next-key 鎖。這樣加鎖的問題是，會在不滿足查詢條件的記錄上記錄鎖，這個記錄鎖其實是不必要的，是一個 bug。

其實，這個不必要的記錄鎖 bug，在 MySQL 8.0.18 之前，主鍵索引的場景下也存在，MySQL 8.0.18 修復了，但只修復了主鍵索引的場景，輔助索引的場景未修復。修復對應 bug 為“Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN”，修復提交記錄見 github。

在 MySQL 8.0.19 版本上，有人再次提了 bug，“Bug #98639 Redundant row-level locking for secondary index”。不過 MySQL 官方認為“Not a Bug”。然後，提 bug 的人，也只好妥協認為這個是“performance issue”。

對比下面這 3 個 SQL 的加鎖情況，可以發現後 2 個 SQL 存在不必要的加鎖問題。

-- 主鍵索引上的範圍查詢
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 a = 100 的索引記錄上添加排他間隙鎖，區間為 (90, 100)（X,GAP）
select * from tbl where a >= 90 and a < 91 for update;

-- 唯一索引上的範圍查詢
-- 在 b = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 b = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
select * from tbl where b >= 90 and b < 91 for update;

-- 非唯一索引上的範圍查詢
-- 在 c = 90 的索引記錄上添加排他 next-key 鎖，區間為 (80, 90]（X）
-- 在 a = 90 的索引記錄上添加排他記錄鎖（X,REC_NOT_GAP）
-- 在 c = 100 的索引記錄上添加排他 next-key 鎖，區間為 (90, 100]（X）（不必要的記錄鎖）
select * from tbl where c >= 90 and c < 91 for update;

隔離級別的實現

上文提到，PostgreSQL 的併發控制技術是以 MVCC 技術為主，封鎖技術為輔。先看下 PostgreSQL 對隔離級別的實現 [doc]：

  • PostgreSQL 支持 SQL 標準的 4 種隔離級別，默認的隔離級別是 RC。但 PostgreSQL 內部只實現 3 種隔離級別 RC、RR 和 SER。若選擇 RU 隔離，實際上是 RC。
  • PostgreSQL 的 RR 隔離級別，底層是基於 MVCC 技術實現的快照隔離（SI，Snapshot Isolation）。快照隔離下，能避免 SQL-92 定義的三種異常，髒讀、不可重複讀和幻讀異常，但是可能會出現寫偏序（Write Skew）異常。
  • PostgreSQL 的 SER 隔離，底層是可串行化的快照隔離（SSI，Serializable Snapshot Isolation）。

InnoDB 的併發控制以封鎖技術為主，MVCC 技術輔助，各個隔離級別的具體實現是：

  • RC 隔離級別：快照讀 + 寫長鎖

    • 快照讀，能避免髒讀

  • RR 隔離級別：快照讀 + 寫長鎖 + 間隙鎖（沒有實現真正的快照隔離 SI）

    • 快照讀，能一定程度避免不可重複讀和幻讀異常，但因為 InnoDB 的刷新快照的特殊實現，不能完全避免
    • 間隙鎖，能避免幻讀異常，只有鎖定讀時才會申請獲取間隙鎖

  • 可串行化隔離級別：讀長鎖 + 寫長鎖 + 間隙鎖

    • 完全基於鎖實現串行化，併發度很低，性能不好

InnoDB 實現的 MVCC 技術，能讓事務以快照讀的方式執行查詢。快照讀（snapshot read），或者叫一致性非鎖定讀（consistent nonlocking read），或者一致性讀（consistent read），即使用多版本技術實現的讀取數據在某個時間點的快照的查詢。在 RR 和 RC 隔離級別下，一致性讀是普通的 select 語句的默認模式。快照讀避免加鎖，從而提高併發度。在 RR 和 RC 隔離級別下快照讀的區別：

  • RR 隔離級別時，事務中的所有一致性讀都會讀取該事務中第一次此類讀取建立的快照。
  • RC 隔離級別時，事務中的每個一致性讀都會設置並讀取其自己的最新快照，快照是最新已提交的數據。

如果事務在查詢數據後，要對該數據做修改操作，快照讀無法提供足夠的保護，因為其他事務可以對這些數據做修改操作。為了提供額外的保護，InnoDB 提供鎖定讀（locking read），即同時執行鎖定操作的 select 語句，鎖持有直到事務結束。鎖定讀分兩種：

  • select ... for share 是加共享鎖的查詢數據
  • select ... for update 是加排他鎖的查詢數據

RR 隔離級別下的不可重複讀和幻讀異常

上文提到，“快照讀，能一定程度避免不可重複讀和幻讀異常，但因為 InnoDB 的刷新快照的特殊實現，不能完全避免”。現在來看下 RR 隔離級別下的不可重複讀異常的示例：

事務1 事務2
mysql> begin; mysql> begin;
-- 返回值為 10，快照讀
mysql> select b from tbl where a = 10;
-- 把 b 值修改為 0
mysql> update tbl set b = 0 where a = 10;
mysql> commit;
-- 返回值為 10，沒有出現不可重複讀異常
mysql> select b from tbl where a = 10;
-- update 會讀取 a = 10 的已提交的最新值
-- 同時 a = 10 記錄的快照會被刷新
mysql> update tbl set b = b + 1 where a = 10;
-- 返回值為 1，出現不可重複讀異常
mysql> select b from tbl where a = 10;

這個問題在 MySQL 的 Bug 系統中可以找到，參見：Bug #57973、Bug #63870 等。官方認為，這不是 Bug，InnoDB 就是按這種方式設計。Bug #57973 下 MySQL 工程師 Kevin Lewis 對這個問題的解答 [ref]：

[16 Aug 2013 19:23] Kevin Lewis
Rejecting this bug because InnoDB is working as designed for the following reason;
...
But when InnoDB Repeatable Read transactions modify the database, it is possible to get phantom reads added into the static view of the database, just as the ANSI description allows. Moreover, InnoDB relaxes the ANSI description for Repeatable Read isolation in that it will also allow non-repeatable reads during an UPDATE or DELETE. Specifically, it will write to newly committed records within its read view. And because of gap locking, it will actually wait on other transactions that have pending records that may become committed within its read view. So not only is an UPDATE or DELETE affected by pending or newly committed records that satisfy the predicate, but also 'SELECT … LOCK IN SHARE MODE' and 'SELECT … FOR UPDATE'.
This WRITE COMMITTED implementation of REPEATABLE READ is not typical of any other database that I am aware of. But it has some real advantages over a standard 'Snapshot' isolation. When an update conflict would occur in other database engines that implement a snapshot isolation for Repeatable Read, an error message would typically say that you need to restart your transaction in order to see the current data. So the normal activity would be to restart the entire transaction and do the same changes over again. But InnoDB allows you to just keep going with the current transaction by waiting on other records which might join your view of the data and including them on the fly when the UPDATE or DELETE is done. This WRITE COMMITTED implementation combined with implicit record and gap locking actually adds a serializable component to Repeatable Read isolation.

就是説，InnoDB 實現的 RR 隔離級別，放鬆了 SQL 標準對 RR 隔離級別的要求。事務 T1 在快照讀後，如果其他事務 T2 修改了快照對應的記錄並提交，之後事務 T1 執行涉及快照的 DML 語句（update、delete、insert）或鎖定讀，會觸發快照刷新，事務 T2 最新提交的修改會刷新進快照。最終導致事務 T1 再次執行相同條件的快照讀，讀取結果不同，出現不可重複讀或幻讀異常。簡單概括就是，在快照失效後，又刷新快照，導致兩次讀到的快照不同。另外，如果實現上選擇不刷新快照，並且事務 T1 正常執行，會出現 P4 丟失更新異常。

不可重複讀異常的避免（一定程度上避免，但沒有完全避免）：

  • 如果事務重複的兩次讀都是快照讀（普通 select 語句），並且中間沒有執行涉及快照的 DML 或鎖定讀，這樣兩次讀到的是相同的快照讀，所以不會出現不可重複讀異常。
  • 如果事務重複的兩次讀都是當前讀（select for update/share），因為第一次加鎖，其他事務無法更新該記錄，所以也不會出現不可重複讀異常。
  • 如果事務重複的兩次讀都是快照讀，但是中間執行涉及快照的 DML 或鎖定讀，觸發了快照刷新，如果快照被更新，就會出現不可重複讀異常。

幻讀異常的避免（一定程度上避免，但沒有完全避免）：

  • 如果事務重複的兩次讀都是快照讀（普通 select 語句），並且中間沒有執行涉及快照的 DML 或鎖定讀，這樣兩次讀到的是相同的快照讀，所以不會出現幻讀異常。
  • 如果事務重複的兩次讀都是當前讀（select for update/share），因為第一次當前讀加間隙鎖，其他事務無法插入，被阻塞，所以也不會出現幻讀異常。
  • 如果事務重複的兩次讀都是快照讀，但是中間執行涉及快照的 DML 或鎖定讀，觸發了快照刷新，如果快照被更新，就會出現幻讀異常。

上述的快照失效的場景，PostgreSQL 的處理方式是，事務會被回滾並報錯提示，應用程序收到這個報錯，可以嘗試重試，重試的事務讀到的快照是最新的，這樣即避免丟失更新異常，也避免了幻讀和不可重複讀異常（參見官方文檔 doc）。

參考資料

MySQL 8.0 Reference Manual

  • 15.7 InnoDB Locking and Transaction Model https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

    • 15.7.1 InnoDB Locking https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
    • 15.7.2 InnoDB Transaction Model https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html
    • 15.7.3 Locks Set by Different SQL Statements in InnoDB https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
    • 15.7.4 Phantom Rows https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

  • 15.15 InnoDB INFORMATION_SCHEMA Tables

    • 15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html
  • 15.17 InnoDB Monitors https://dev.mysql.com/doc/refman/8.0/en/innodb-monitors.html

其他參考資料：

  • Hal Berenson, Philip A. Bernstein, Jim Gray, Jim Melton, Elizabeth J. O'Neil, Patrick E. O'Neil: A Critique of ANSI SQL Isolation Levels. SIGMOD Conference 1995: 1-10（隔離級別的經典論文，其中作者 Jim Gray 因“對數據庫和事務處理研究的開創性貢獻以及系統實現方面的技術領導力”而於 1998 年獲得圖靈獎）
  • 數據庫事務處理的藝術，騰訊李海翔 2017
  • MySQL技術內幕：InnoDB存儲引擎，姜承堯 第2版2013：第6章 鎖
  • 2013-12 何登成：MySQL 加鎖處理分析 https://web.archive.org/web/0/http://hedengcheng.com/?p=771 https://bit.ly/44rsCH7
  • 2019-04 阿里王德浩/孟勃榮：開發者都應該瞭解的數據庫隔離級別 https://mp.weixin.qq.com/s/bFg8XFYd9HLvEoYyzAD3jg
  • 2021-07 MySQL Data Locks: Mapping 8.0 to 5.7 https://hackmysql.com/post/mysql-data-locks-mapping-80-to-57/
  • 2019-07 Bug #29508068 UNNECESSARY NEXT-KEY LOCK TAKEN (MySQL 8.0.18 發佈) https://github.com/mysql/mysql-server/commit/d1b0afd75ee669f54b70794eb6dab6c121f1f179
  • 2020-02 Bug #98639 Redundant row-level locking for secondary index (8.0.19) https://bugs.mysql.com/bug.php?id=98639
  • 2010-11 Bug #57973 UPDATE tries to lock rows not visible to read view https://bugs.mysql.com/bug.php?id=57973#c403965
  • 2011-12 Bug #63870 Repeatable-read isolation violated in UPDATE (5.1.42, 5.5.20) https://bugs.mysql.com/bug.php?id=63870
  • PostgreSQL Documentation: 13.2. Transaction Isolation https://www.postgresql.org/docs/15/transaction-iso.html
MySQL , innodb
user avatar
0 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.