MySQL 資料庫事務隔離性的實現

本文分享自華為雲社群《【資料庫事務與鎖機制】- 事務隔離的實現》，原文作者：技術火炬手 。

事實上在資料庫引擎的實現中並不能實現完全的事務隔離，比如序列化。這種事務隔離方式雖然是比較理想的隔離措施，但是會對併發效能產生比較大的影響，所以在 MySQL 中事務的預設隔離級別是 REPEATABLE READS（可重複讀），下面我們展開討論一下 MySQL 對資料庫隔離性的實現。

MySQL 事務隔離性的實現

在 MySQL InnoDB （下稱 MySQL）中實現事務的隔離性是通過鎖實現的，大家知道在併發場景下我常用的隔離和一致性措施往往是通過鎖實現，所以鎖也是資料庫系統常用的一致性措施。

MySQL 鎖的分類

我們主要討論 InnoDB 鎖的實現，但是也有必要簡單瞭解 MySQL 中其他資料庫引擎對鎖的實現。整體來說 MySQL 中可以分為三種鎖的類型 表鎖、行鎖、頁鎖，其中使用表鎖的是 MyISAM 引擎，支援行鎖的是 InnoDB 引擎，同時 InnoDB 也支援表鎖，BDB 支援頁鎖（不是太瞭解）。

表鎖 table-level locking

表級別的鎖顧名思義就是加鎖的維度是表級別的，是給一個表上鎖，這種鎖的特點是開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，但是併發度也是最低的，表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用。

MySQL 表鎖的使用

在 MySQL 中使用表鎖比較簡單，可以通過 LOCK TABLE 語句對一張表進行加鎖，如下：

# 加鎖LOCK TABLE T_XXXXXXXXX;# 解鎖UNLOCK TABLES;

加鎖和解鎖的語法

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE} UNLOCK TABLES

需要注意的是 LOCK TABLE 是指當前會話的鎖，也就是通過 LOCK TABLE 顯示的為當前會話獲取表鎖，作用是防止其他會話在需要互斥訪問時修改表的資料，會話只能為其自身獲取或釋放鎖。一個會話無法獲取另一會話的鎖，也不能釋放另一會話持有的鎖。同時 LOCK TABLE 不單單可以獲取一個表的鎖，也可以是一個檢視，對於檢視鎖定，LOCKTABLES 將檢視中使用的所有基本表新增到要鎖定的表集合中，並自動鎖定它們。

LOCK TABLES 在獲取新鎖之前，隱式釋放當前會話持有的所有表鎖

UNLOCK TABLES 顯式釋放當前會話持有的所有表鎖

LOCKTABLE 語句有兩個比較重要的參數 lock_type 它可以容許你指定加鎖的模式，是讀鎖還是寫鎖，也就是 READLOCK 和 WRITE LOCK。

READ 鎖

讀鎖的特點是 持有鎖的會話可以讀取表但不能寫入表，多個會話可以同時獲取 READ 該表的鎖

WRITE 鎖

持有鎖的會話可以讀取和寫入表，只有持有鎖的會話才能訪問該表。在釋放鎖之前，沒有其他會話可以訪問它，保持鎖定狀態時，其他會話對錶的鎖定請求將阻塞

WRITE 鎖通常比 READ 鎖具有更高的優先順序，以確保儘快處理更新。這意味著，如果一個會話獲取了一個 READ 鎖，然後另一個會話請求了一個 WRITE 鎖，則隨後的 READ 鎖請求將一直等待，直到請求該 WRITE 鎖的會話已獲取並釋放了該鎖

通過上面對錶鎖的簡單介紹我們引出兩個比較重要的資訊，就是讀鎖和寫鎖，那麼答案就浮出水面，在表級別的鎖中其實 MySQL 是通過 共享讀鎖，和排他寫鎖來實現隔離性的，下面我們減少共享讀鎖和排他寫鎖。

共享讀鎖(Table ReadLock)

共享鎖又稱為讀鎖，簡稱 S 鎖，顧名思義，共享鎖就是多個事務對於同一資料可以共享一把鎖，都能訪問到資料，但是隻能讀不能修改

對 MyISAM 表的讀操作，不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫請求；也即當一個 session 給表加讀鎖，其他 session 也可以繼續讀取該表，但所有更新、刪除和插入將會阻塞，直到將表解鎖。MyISAM 引擎在執行 select 時會自動給相關表加讀鎖，在執行 update、delete 和 insert 時會自動給相關表加寫鎖

獨佔寫鎖（Table WriteLock）

排他鎖又稱為寫鎖，簡稱 X 鎖，顧名思義，排他鎖就是不能與其他所並存，如一個事務獲取了一個數據行的排他鎖，其他事務就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務是可以對資料就行讀取和修改

獨佔寫鎖也被稱之為排他寫鎖，MyISAM 表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作；MyISAM 表的讀操作與寫操作之間，以及寫操作之間是序列的。也即當一個 session 給表加寫鎖，其他 session 所有讀取、更新、刪除和插入將會阻塞，直到將表解鎖

共享鎖和獨佔鎖的相容性

行鎖 Row -level locking

在 MySQL 中 支援行鎖的引擎是 InnoDB，所以我們這裡我們指的行鎖主要是說 InnoDB 的行鎖。

InnoDB 鎖的實現和 Oracle 非常類似，提供一致性的非鎖定讀、行級鎖支援。行級鎖沒有相關額外的開銷，並可以同時得到併發性和一致性。

lock 與 latch

Latch 一般稱為閂鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的效能會非常差。在 InnoDB 中，latch 又可以分為 mutex（互斥量）和 rwlock（讀寫鎖）。其目的是用來保證併發執行緒操作臨界資源的正確性，並且通常沒有死鎖檢測的機制。

Lock 的物件是事務，用來鎖定的是資料庫中的物件，如表、頁、行。並且一般 lock 的物件僅在事務 commit 或 rollback 後進行釋放（不同事務隔離級別釋放的時間可能不同）。

lock 與 latch 的比較

latch 可以通過命令 SHOWENGINE INNODB MUTEX 檢視，Lock 可以通過命令 SHOW ENGINE INNODB STATUS 及 information_schema 架構下的表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 來檢視

和上面表鎖中講的一樣 MySQL 行鎖也是通過 共享鎖和獨佔鎖（排他鎖）實現的，所以關於這兩種鎖的概述就不過多簡紹。

InnoDB 還支援多粒度（granular）鎖定，允許事務同時存在行級鎖和表級鎖，這種種額外的鎖方式，稱為意向鎖（Intention Lock）。意向鎖是將鎖定的物件分為多個層次，意向鎖意味著事務希望在更細粒度（fine granularity）上進行加鎖

如果對最下層（最細粒度）的物件上鎖，那麼首先需要對粗粒度的物件上鎖，意向鎖為表級鎖，不會阻塞除全表掃描以外的任何請求。設計目的主要是為了在一個事務中揭示下一行將被請求的鎖類型。兩種意向鎖。

意向共享鎖（IS Lock），事務想要獲得一張表中某幾行的共享鎖意向排他鎖（IX Lock），事務想要獲得一張表中某幾行的排他鎖

表級意向鎖與行級鎖的相容性

下面命令或表都可以檢視當前鎖的請求

SHOW FULL PROCESSLIST;SHOW ENGINE INNODB STATUS;SELECT * FROM information_schema.INNODB_TRX;SELECT * FROM information_schema.INNODB_LOCKS;SELECT * FROM information_schema.INNODB_LOCK_WAITS;

一致性非鎖定讀

一致性的非鎖定讀（consistent nonlocking read）是指 InnoDB 通過行多版本控制（multi versioning）的方式來讀取當前執行時間資料庫中行的資料。如果讀取的行正在執行 DELETE 或 UPDATE 操作，這時不會去等待行上鎖的釋放。而是去讀取行的一個快照資料（之前版本的資料）。

一個行記錄多個快照資料，一般稱這種技術為行多版本技術。由此帶來的併發控制，稱之為多版本併發控制（Multi Version Concurrency Control，MVCC）。

之所以稱為非鎖定讀，因為不需要等待訪問的行上 X 鎖的釋放。實現方式是通過 undo 段來完成。而 undo 用來在事務中回滾資料，快照資料本身沒有額外的開銷，也不需要上鎖，因為沒有事務會對歷史資料進行修改操作。非鎖定讀機制極大地提高了資料庫的併發性。在不同事務隔離級別下，讀取的方式不同，並不是在每個事務隔離級別下都是採用非鎖定的一致性讀。此外，即使都是使用非鎖定的一致性讀，但是對於快照資料的定義也不相同。在事務隔離級別 READ COMMITTED 和 REPEATABLE READ 下，InnoDB 使用非鎖定的一致性讀。但對快照資料的定義不相同。在 READCOMMITTED 事務隔離級別下，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照資料。而在 REPEATABLEREAD 事務隔離級別下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。

自增長與鎖

自增長在資料庫中是非常常見的一種屬性，也是首選的主鍵方式。在 InnoDB 的記憶體結構中，對每個含有自增長值的表都有一個自增長計數器（auto-incrementcounter）。

插入操作會依據這個自增長的計數器值加 1 賦予自增長列。這個實現方式稱做 AUTO-INC Locking，採用了一種特殊的表鎖機制，為了提高插入的效能，鎖不是在一個事務完成後才釋放，而是在完成對自增長值插入的 SQL 語句後立即釋放。

因此 InnoDB 提供了一種輕量級互斥量的自增長實現機制，大大提高了自增長值插入的效能。同時提供了一個參數 innodb_autoinc_lock_mode 來控制自增長的模式，該參數的預設值為 1。瞭解其實現之前，先對自增長的插入進行分類，如下表：

參數 innodb_autoinc_lock_mode 的說明

InnoDB 中自增長的實現和 MyISAM 不同，MyISAM 儲存引擎是表鎖設計，自增長不用考慮併發插入的問題。如果主從分別使用 InnoDB 和 MyISAM 時，必須考慮這種情況。

另外，在 InnoDB 存中，自增長值的列必須是索引，同時必須是索引的第一個列。如果不是第一個列會拋出異常，而 MyISAM 沒有這個問題。

外來鍵和鎖

外來鍵主要用於引用完整性的約束檢查。InnoDB 對於一個外來鍵列，如果沒有顯式地對這個列加索引，會自動對其加一個索引，可以避免表鎖。而 Oracle 不會自動新增索引，需要手動新增，可能會產生死鎖問題。

對於外來鍵值的插入或更新，首先需要查詢（select）父表中的記錄。但是 select 父表操作不是使用一致性非鎖定讀，因為這會導致資料不一致的問題，因此這時使用的是 SELECT…LOCK IN SHARE MODE 方式，即主動對父表加一個 S 鎖。如果這時父表上已經加了 X 鎖，子表上的操作會被阻塞。如下表：

行鎖的 3 種演算法

InnoDB 有如下 3 種行鎖的演算法

Record Lock：單個行記錄上的鎖。總去鎖住索引記錄，如果表沒有設定任何索引，會使用隱式的主鍵來進行鎖定Gap Lock：間隙鎖，鎖定一個範圍，但不包含記錄本身Next-Key Lock：Gap Lock+Record Lock，鎖定一個範圍，並且鎖定記錄本身。行的查詢採用這種鎖定演算法

例如一個索引有 10，11，13 和 20 這四個值，那麼該索引可能被 Next-KeyLocking 的區間為

採用 Next-Key Lock 的鎖定技術稱為 Next-Key Locking。其設計的目的是為了解決幻讀問題（Phantom Problem）。Next-Key Lock 是謂詞鎖（predict lock）的一種改進。還有 previous-key locking 技術。同樣上述的索引 10、11、13 和 20，若採用 previous-key locking 技術，那麼鎖定的區間為

當查詢的索引含有唯一屬性時，會對 Next-Key Lock 進行優化。對聚集索引，將其降級為 Record Lock。對輔助索引，將對下一個鍵值加上 gap lock，即對下一個鍵值的範圍為加鎖

Gap Lock 的作用是為了阻止多個事務將記錄插入到同一範圍內，而這會產生導致幻讀問題,使用者可以通過以下兩種方式來顯式地關閉 Gap Lock

將事務的隔離級別設定為 READ COMMITTED將參數 innodb_locks_unsafe_for_binlog 設定為 1

上述設定破壞了事務的隔離性，並且對於 replication，可能會導致主從資料的不一致。此外，從效能上來看，READCOMMITTED 也不會優於預設的事務隔離級別 READ REPEATABLE。

解決幻讀問題

幻讀問題是指在同一事務下，連續執行兩次同樣的範圍查詢操作，得到的結果可能不同

Next-KeyLocking 的演算法就是為了避免幻讀問題。對於上述的 SQL 語句，其鎖住的不是單個值，而是對（2，+∞）這個範圍加了 X 鎖。因此任何對於這個範圍的插入不允許，從而避免了幻讀問題。Next-Key Locking 機制在應用層還可以實現唯一性的檢查。例如：

select * from table_name where col = xxx LOCK IN SHARE MODE;

如果使用者通過索引查詢一個值，並對該行加上一個 SLock，那麼即使查詢的值不在，其鎖定的也是一個範圍，因此若沒有返回任何行，那麼新插入的值一定是唯一的。如果此時有多個事務併發操作，那麼這種唯一性檢查機制也不會存在問題。因為這時會導致死鎖，只有一個事務的插入操作會成功，而其餘的事務會拋出死鎖的錯誤。

通過 Next-Key Locking 實現應用程式的唯一性檢查：

總結

以上我們簡單簡紹了 MySQL 如何通過鎖機制實現對事務的隔離，也簡紹了一些實現這些所的演算法，如果對細節比較感興趣的同學可以參考 官方文件 中對 InnoDB 的詳細簡紹。

作者：華為雲開發者社群連結：https://juejin.cn/post/6967555015967768583來源：掘金