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 不單單可以獲取一個表的鎖，也可以是一個檢視，對於檢視鎖定，LOCK TABLES將檢視中使用的所有基本表新增到要鎖定的表集合中，並自動鎖定它們。

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

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

• READ 鎖
  讀鎖的特點是 持有鎖的會話可以讀取表但不能寫入表，多個會話可以同時獲取READ該表的鎖
• WRITE 鎖
  持有鎖的會話可以讀取和寫入表，只有持有鎖的會話才能訪問該表。在釋放鎖之前，沒有其他會話可以訪問它，保持鎖定狀態時，其他會話對錶的鎖定請求將阻塞
  WRITE鎖通常比READ鎖具有更高的優先順序，以確保儘快處理更新。這意味著，如果一個會話獲取了一個READ鎖，然後另一個會話請求了一個WRITE鎖，則隨後的 READ鎖請求將一直等待，直到請求該WRITE鎖的會話已獲取並釋放了該鎖

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

共享讀鎖(Table Read Lock)

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

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

獨佔寫鎖（Table Write Lock）

排他鎖又稱為寫鎖，簡稱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可以透過命令SHOW ENGINE 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使用非鎖定的一致性讀。但對快照資料的定義不相同。在READ COMMITTED事務隔離級別下，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照資料。而在REPEATABLE READ事務隔離級別下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。

自增長與鎖

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

插入操作會依據這個自增長的計數器值加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-Key Locking的區間為

採用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，可能會導致主從資料的不一致。此外，從效能上來看，READ COMMITTED也不會優於預設的事務隔離級別READ REPEATABLE。

解決幻讀問題

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

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

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

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

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

總結

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