MySQL是如何實現事務的隔離級別

摘要本文旨在瞭解MySQL InnoDB引擎如何支持事務的隔離級別。文章主要內容分兩個部分。第一部分闡述資料庫的併發問題以及為之產生的ANSI SQL 標準隔離級別。第二部分根據 MySQL 官方文檔解釋 InnoDB 是如何支持這些隔離級別的。資料庫事務的併發問題 ANSI SQL 隔離 ...

摘要

本文旨在瞭解MySQL InnoDB引擎如何支持事務的隔離級別。
文章主要內容分兩個部分。
第一部分闡述資料庫的併發問題以及為之產生的ANSI SQL 標準隔離級別。
第二部分根據 MySQL 官方文檔解釋 InnoDB 是如何支持這些隔離級別的。

資料庫事務的併發問題

ANSI SQL 隔離級別的定義是來源於三個異象問題，ANSI SQL在權衡系統的可靠性和性能之間定義了不同的級別。所以這裡先介紹主流的三個併發問題是什麼。

讀異象 (read phenomena)

臟讀 (dirty read)
- 一個事務讀取到了另一個事務更新但未提交的數據。當另一個事務回滾或者再次修改，就會讀取到臟數據
不可重覆讀 (non-repeatable read / Fuzzy Read)
- 現象為一個事務多次同樣讀取數據的操作其結果不一致。例如讀取時有其他事務提交了修改
幻讀 (phantom)
- 在多次同樣的查詢操作下，後面的查詢出現了新行的數據。
- 例如在執行多次查詢的時候，其他事務插入了一個新行或者修改了某行數據使得能匹配上Where條件，那麼後一次查詢必然將查詢到這個新數據（也就感覺出現了幻覺，莫名其妙多出了一行）
- 不可重覆讀和幻讀其實類似，但是幻讀偏向於查詢新增數據（所以專門弄了間隙鎖來防止幻讀），不可重覆讀則是修改數據。
Def: The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
- 註：幻象 (Phantom) ，幻讀 (Phantom Read) ，幻象問題 (Phantom Problem) 都是一個表述。
總結：上面介紹的三個讀併發問題，其本質都是一個事務在讀其他事務修改過的數據。

標準事務隔離級別

因為事務在併發執行的過程中會存在相互干擾，需要有隔離性的保障，故引入事務隔離級別規範，以平衡操作的性能、可靠和一致

ANSI SQL下規定的隔離級別（1992 - 很老的標準了）

未提交讀 - Read Uncommited

風險挺高，但是如果只是存粹的讀操作可以推薦使用（MyISAM也挺香呀）

已提交讀 - Read Commited(互聯網主流預設使用的隔離級別)

事務無法看見其他未提交事務的修改

可重覆讀 - Repeatable Read (MySQL 預設)

只讀事務開始時的快照數據

可序列化 - Serializable

ANSI SQL規定的隔離級別其實還是根據主流存在的資料庫併發問題來定義的，每個隔離級別來解決不同的問題。
如果細分繼續細分不同的併發問題，隔離級別還能更加細化。

MySQL是如何支持不同隔離級別的

知識準備(術語解釋)

一致性讀取 (consistent read)
- 事務在進行讀操作時，使用的是事務開始時的行快照數據，這樣就不用擔心讀到其他其他事務修改的數據。
- 在[可重覆讀]下，事務快照是基於第一次讀操作的快照(通過undo log 回溯)
- 在[可提交讀]下，每一次一致性讀操作都會重置快照
- 優點：不上鎖，允許其他事務進行修改
半一致性讀取 (semi-consistent read)
- 即UPDATE語句中的讀/匹配操作，當UPDATE語句執行的時候，InnoDB會取最後一次提交到MySQL的數據來進行 Where 子句中的匹配。
  - 如果匹配上了（也就是要更新），那就重讀該行並加鎖（或等待加鎖）
    - todo 為什麼要重讀該行不是很理解，直接用那條數據不就好了
- 僅用於[可提交讀]隔離級別
- 個人理解其實就是一致性讀取在[可提交讀]隔離級別下 UPDATE的表現
鎖定讀 (locking read)
- 即加鎖的查詢語句
- E.g SELECT ... FOR UPDATE | SELECT ... FOR SHARE
行鎖 (record lock) : 即鎖定索引記錄的鎖，即使沒有索引也會找到對應行記錄鎖主鍵哦
間隙鎖 (gap lock) : 鎖在了兩條索引記錄之間的鎖，或者(無窮小,某索引)/(某索引,無窮大)，他們鎖住的是一個範圍，且不同的間隙鎖不互斥，他們排斥的只是在鎖範圍內的插入操作
- mark: R.C 隔離級別下是被禁用的
next-key lock : 行鎖和間隙鎖的組合實現

未提交讀 - Read Uncommited

SELECT 語句採用的無鎖策略，也就更容易產生臟讀

已提交讀 - Read Commited

該隔離級別下的一致性讀取，讀的都是最新版的快照，每次讀快照都會被重置成最新

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
對於Locking Reads、UPDATE、DELETE 語句，InnoDB的鎖策略是只鎖索引記錄，並沒有用間隙鎖來鎖範圍，所以容易產生幻讀問題

特別的對於 R.C 隔離級別有以下變動

對於UPDATE語句，如果行已經被加鎖了，InnoDB會使用 “semi-consistent”read，來讀取快照中最新的值(而不是最原始的快照)來進行 WHERE 匹配更新。（也就是說 UPDATE 也採用 R.C 特供版一致性讀取）
對於UPDATE或 DELETE語句，InnoDB僅對其更新或刪除的行持有鎖。MySQL評估WHERE條件後，將釋放不匹配行的記錄鎖。這大大降低了死鎖的可能性。
- 比如掃表過程中，掃到這行不是要修改的，那麼就釋放鎖，要改的就一直加鎖直到事務結束
- R.R 級別下則不會釋放

關於這個“semi-consistent” read

他讀取的是最後一次提交到MySQL的版本，而不是快照中最早讀的那個版本
優點：MySQL可以判斷當前的更新操作是否符合Where條件，如果不匹配就不用更新了，也就節省了一次寫操作，如果匹配就重新讀取嘗試加鎖
缺點：如果都採用一致性讀consistent read，讀取都是最早事務的快照，就不會產生幻象問題了(Phantom Problem)；但是採用了半一致性，兩次查詢的結果可能會不一致。

可重覆讀 - Repeatable Read

採用一致性讀取，同事務中的每次讀取都取第一次讀的快照。（R.R 版一致性讀取）

Consistent reads within the same transaction read the snapshot established by the first read.
對於 locking reads，UPDATE，DELETE ，其加鎖策略取決於是否是唯一索引唯一條件查詢
- 唯一索引配合唯一查詢條件，引擎只鎖定那條索引記錄，不鎖間隙
- 其他場景，引擎使用Gap Lock 或 Next-Record Lock來鎖定掃描的索引範圍，以阻止其他事務插入新行到該間隙

串列化 - Serializable

InnoDB 默默的把所有純 SELECT 語句都轉成了 SELECT ... FOR SHARE ，也就預設都加讀鎖

小結

InnoDB 是依賴於不同的鎖策略實現了不同隔離級別的要求
R.C 隔離級別使用了當前讀 (R.C 下的一致性讀取) 且禁用了間隙鎖
理論上 R.R 隔離級別下因為使用了一致性讀和間隙鎖是不會產生的幻讀問題的，所以標準可能有點老了
- todo MySQL在介紹幻讀的時候說 R.R 下麵會有幻讀，但又沒有實際的例子，很難讓人信服。（除非這個也算：兩次查詢前一次是普通讀，後一次是鎖定讀）

擴展

在介紹 ANSI 隔離級別標準的時候，提到了ANSI標準是根據主流的三個併發問題來對症下藥的，但其實仍然有一些併發問題被 ANSI 標準給預設忽略或者說就不關心了，比如典型的臟讀和丟失修改。更詳細的可以參考下圖(1995)