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

摘要

本文旨在了解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 - 很老的標準了）

1. 未提交讀 - Read Uncommited
   • 風險挺高，但是如果只是存粹的讀操作可以推薦使用（MyISAM也挺香呀）
2. 已提交讀 - Read Commited(互聯網主流默認使用的隔離級別)
   • 事務無法看見其他未提交事務的修改
3. 可重復讀 - Repeatable Read (MySQL 默認)
   • 只讀事務開始時的快照資料
4. 可序列化 - 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)
  • 

參考文獻

• 15.7.2.1 Transaction Isolation Levels
• Phantom Rows
• glossary
• “A Critique of ANSI SQL Isolation Levels,” Proc. ACM SIGMOD 95, pp. 1-10, San Jose CA, June 1995, ? ACM.

標籤：MySQL

上一篇：mysql從一張表查出資料存到另一張表和inner join的用法

下一篇：mybaties 批量模糊查詢、foreach string[]用法