天天看點
傳回

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

一、事務的特性簡介

1.1 原子性（Atomicity）

  要麼全做，要麼全不做，一系列操作都是不可分割的，如果在執行操作的過程發生了錯誤，那麼就把已經執行的操作恢複成沒執行之前的樣子。比如轉賬不能隻有一方扣錢另一方不增加餘額。

1.2 隔離性（Isolation）

  任何其他狀态操作不能影響本次狀态操作轉換，比如 A 幾乎同時向 B 轉 2 次賬，不同的事務讀取出的卡上餘額都是 12 元，在第一個事務 A-5 元後，第二個事務 A-5(那這裡是 12-5 還是 7-5 呢？)，是以 MySQL 需要一些措施保證這些操作的隔離。

1.3 一緻性（Consistency）

  如果資料庫的資料全部符合現實世界的限制，則這些資料就是一緻性的，或者說符合一緻性的。

比如餘額不能小于 0，有一些業務 id 不能為空。資料庫本身能為我們解決一部分一緻性需求，比如 NOT NULL 來拒絕 NULL 值的插入，但是更多的是需要靠寫業務代碼的程式員自己保證，比如在 Spring Boot 裡面，入參就可以 @NotNull 或者 @NotBlank 之類的來進行入參校驗。

資料庫檢查一緻性是一個耗費性能的工作，比如為表建立一個觸發器，每當插入或更新記錄的時候就會校驗是否滿足條件，如果涉及到某一些列的計算，就會嚴重影響插入或更新的速度。

盡量不要把校驗參數的判斷條件(一緻性檢查)寫在 MySQL 語句中，不僅影響插入更新的速度，而且資料庫連接配接也是很耗時的。能在業務層面解決就在業務層面判斷。

提示：建表時的 CHECK 子句對于一緻性檢查沒什麼用，在 MySQL 8.0.16 之前的版本中也不會去檢查 CHECK 子句中的限制是否成立。比如：

create table test (
  id unsigned int not null auto_increment comment ‘主鍵id’,
  name varchar(100) comment ‘姓名’,
  balance int comment ‘餘額’,
  primary key (id),
  check (balance >= 0)
);

1.4 持久性 （Durability）

資料庫修改的資料都應該在磁盤中保留下來，無論發生什麼事故，本次操作的影響都不應該丢失。比如轉賬成功後不可以又恢複到沒轉賬之前的樣子，那樣錢就沒了。

二、建立表 

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_hero_name (name)
) Engine=InnoDB CHARSET=utf8;

這裡把 hero 表的主鍵命名為 number 是為了與後面的事務 id 進行區分，為了簡單，就不寫限制條件和注釋了。

然後向這個表裡插入一條資料：

INSERT INTO hero VALUES(1, '劉備', '蜀');

現在表裡的資料就是這樣的：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

三、事務隔離級别

MySQL 是一個用戶端／伺服器架構的軟體，對于同一個伺服器來說，可以有若幹個用戶端與之連接配接，每個用戶端與伺服器連接配接後，就形成了一個會話（Session）。每個用戶端都可以在自己的會話中向伺服器送出請求語句，一個請求語句可能是某個事務的一部分。伺服器可以同時處理來自多個用戶端的多個事務。

3.1 事務并發執行時遇到的一緻性問題

在不同隔離級别中，對資料庫的操作可能會出現幾種現象。如下：

3.1.1 髒寫（Dirty Write）(用于熟悉和了解ACID特性，實際中不可能存在髒寫)

如果一個事務修改了另一個未送出事務修改過的資料，那就意味着發生了髒寫。如下：假設兩個會話各開啟了一個事務 TA 和 TB，原有 x=0， y=0，TA 先修改了 x=3，TB 修改了 x=1，y=1，然後 TB 送出，最後 TA 復原。如果 TA 復原導緻 x=0，那麼對于 TB 來說破壞了原子性，因為 x 被復原，y 還是正常修改。如果 TA 復原導緻 TB 所有的修改都復原，那麼對于TB 來說破壞了持久性，明明 TB 都送出了，怎麼能讓一個未送出的 TA 将 TB 的持久性破壞掉呢？

無論哪種隔離級别，都不允許髒寫的存在，是以髒寫也可以作為介紹事務特性的一個序言，了解即可。

3.1.2 髒讀（Dirty Read）

如果一個事務讀到了另一個未送出事務修改過的資料，那就意味着發生了髒讀，示意圖如下：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

Session A 和 Session B 各開啟了一個事務，Session B 中的事務先将 number 列為 1 的記錄的 name 列更新為’關羽’，然後 Session A 中的事務再去查詢這條 number 為 1 的記錄，如果讀到列 name 的值為’關羽’，而 Session B 中的事務稍後進行了復原，那麼 Session A 中的事務相當于讀到了一個不存在的資料，這種現象就稱之為髒讀。

這裡例子中 Session B 中的事務是 rollback，即使是 commit 了，雖然最終資料庫的狀态是一緻的，但是在 Session A 中的事務讀取 number=1 這條記錄的時候，這個事務卻得到了不一緻的狀态。資料庫不一緻的狀态是不應該暴露給使用者的。

嚴格一點的解釋：假設事務 T1、T2 并發執行，它們都要通路資料項 X，T1 先修改了 X 的值，然後 T2 又讀取了未送出事務 T1 修改後的 X 值，之後 T1 中止而 T2 送出。這就意味着 T2 讀到了一個根本不存在的值，這也是髒讀的嚴格解釋。

3.1.3 不可重複讀（Non-Repeatable Read）

如果一個事務修改了另一個未送出事務讀取的資料，就意味着發生了不可重複讀現象，或者叫模糊讀(Fuzzy Read)現象。

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

讀的’劉備’，被修改為’關羽’ ，讀的’關羽’ 又被修改為了’張飛’ 。

嚴格一點的解釋：假設事務 T1、T2 并發執行，它們都要通路資料項 X，T1 先讀取了 X 的值，然後T2 又修改了未送出事務 T1 讀取的 X 的值，之後 T2 送出，然後T1 再次讀取資料項 X 的值時會得到與第一次讀取時不同的值。

3.1.4 幻讀（Phantom）

如果一個事務先根據某些條件查詢出一些記錄，之後另一個事務又向表中插入了符合這些條件的記錄，原先的事務再次按照該條件查詢時，能把另一個事務插入的記錄也讀出來，那就意味着發生了幻讀，示意圖如下：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

嚴格一點的解釋：假設事務 T1、T2 并發執行，T1 先讀取符合搜尋條件P的記錄，然後 T2 寫入了符合搜尋條件P的記錄。之後 T1 再讀取符合搜尋條件 P 的記錄時，會發現兩次讀取的記錄時不一樣的。

如果 Session B 中是删除了一些符合 number > 0 的記錄而不是插入新記錄，那 Session A 中之後再根據 number > 0 的條件讀取的記錄變少了，這種現象算不算幻讀呢？明确說明下，這種現象不屬于幻讀，幻讀強調的是一個事務按照某個相同條件多次讀取記錄時，後讀取時讀到了之前沒有讀到的記錄。

我們這裡隻考慮 SQL 标準中提到的，不考慮其他論文的描述，對于 MySQL 來說，幻讀強調的是“一個事務在按照某個相同的搜尋條件多次讀取記錄時，在後續讀取到了之前沒讀到的記錄”，可能是别的事務 insert 操作引起的。那對于先前已經讀到的記錄，之後又讀取不到這種情況算啥呢？我們把這種現象認為是結果集中的每一條記錄分别發生了不可重複讀的現象。

比如：第一次讀到 abc 三條記錄，第二次讀到 abd，既多了 d 記錄，又少了 c 記錄，這怎麼分析?

對于記錄 c 來說，發生了不可重複讀，對于記錄 d 來說，發生了幻讀。一緻性問題針對每條記錄分析即可。

是否有可能發生一緻性問題的判斷依據是，在準備讀取的那一刻，想查詢的資料庫某些列的值與實際查詢出來的可能會有出入，則認為可能會發生一緻性問題。

綜上：髒讀、不可重複讀、幻讀都可能會發生一緻性問題。

既然會出現這些問題，那麼 SQL 也有一些标準來處理這些問題，接着看吧。

3.2 SQL 标準中的四種隔離級别

我們給可能導緻一緻性問題的嚴重性給這些現象排一下序：髒讀 > 不可重複讀 > 幻讀。

舍棄一部分隔離性來換取一部分性能在這裡就展現在：設立一些隔離級别，隔離級别越低，越可能發生嚴重的問題。有一幫人（并不是設計 MySQL 的大叔）制定了一個所謂的 SQL 标準，在标準中設立了 4 個隔離級别：

  • READ UNCOMMITTED：未送出讀。
  • READ COMMITTED：已送出讀 （又簡稱為 RC） 。
  • REPEATABLE READ：可重複讀 （又簡稱為 RR）。
  • SERIALIZABLE：可串行化。

SQL 标準中規定（是 SQL 标準中規定，不是 MySQL 中規定），針對不同的隔離級别，并發事務可以發生不同的現象，具體情況如下：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC
SQL92 标準中并沒有指出髒寫的現象，髒寫這個現象對于一緻性影響太嚴重了，無論哪種隔離級别都不允許髒寫的發生，這裡就不多提。

3.3 MySQL 中支援的四種隔離級别

不同的資料庫廠商對 SQL 标準中規定的 4 種隔離級别支援不一樣，比方說 Oracle 就隻支援 READ COMMITTED（Oracle的預設隔離級别）和 SERIALIZABLE 隔離級别。這裡所讨論的 MySQL 雖然支援 4 種隔離級别，但與 SQL 标準中所規定的各級隔離級别允許發生的問題卻有些出入，MySQL 在 REPEATABLE READ 隔離級别下，是可以很大程度上禁止幻讀問題的發生的（關于如何禁止之後會詳細說明的）。

MySQL 的預設隔離級别為 REPEATABLE READ，我自己手上的項目在生産環境的隔離級别是 READ COMMITTED，而且相關的一些接口可能同時操作同一張表的某一個賬号，并發性較高，我的操作是：每次進入事務之前都會用 Redis 分布式鎖去鎖住這個賬号再進入事務，操作同一個賬号同一時間隻能有一個成功，這樣就不會出現多個事務并發去操作這個賬号相關性的資料，也就不會有這條記錄出現不可重複讀和幻讀的機會。

3.3.1 如何設定事務的隔離級别

我們可以通過下邊的語句修改事務的隔離級别（實際開發中是不會讓開發人員随意有這種操作的，可以在自己電腦嘗試）：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

其中的level可選值有4個：

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

設定事務的隔離級别的語句中，在 SET 關鍵字後可以放置 GLOBAL 關鍵字、SESSION 關鍵字或者什麼都不放，這樣會對不同範圍的事務産生不同的影響，具體如下：

  • 使用 GLOBAL 關鍵字（在全局範圍産生影響）：

比如下面這樣：

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

則：

  1. 隻對執行完該語句之後新産生的會話起作用。
  2. 目前已經存在的會話無效。

所謂新産生的會話，如果你是 navicat 操作，得關閉連接配接之後再打開連接配接才算新的會話，如果僅僅是建立查詢還算同一個會話，是看不到設定前後隔離級别的變化的。

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC
  • 使用 SESSION 關鍵字（在會話範圍影響）：

比方說這樣：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

則：

  1. 對目前會話的所有後續的事務有效
  2. 該語句可以在已經開啟的事務中間執行，但不會影響目前正在執行的事務。
  3. 如果在事務之間執行，則對後續的事務有效。
  • 上述兩個關鍵字都不用（隻對執行這個 SET 語句後的下一個事務産生影響）：

比如下面這樣：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

則：

隻對目前會話中下一個即将開啟的事務有效。

下一個事務執行完後，後續事務将恢複到之前的隔離級别。

該語句不能在已經開啟的事務中間執行，否則會報錯。

如果我們在伺服器啟動時想改變事務的預設隔離級别，可以修改啟動參數 transaction-isolation 的值，比方說我們在啟動伺服器時指定了--transaction-isolation=SERIALIZABLE，那麼事務的預設隔離級别就從原來的 REPEATABLE READ 變成了 SERIALIZABLE。

可以通過檢視系統變量 transaction_isolation 的值來确定目前會話預設的隔離級别：

SHOW VARIABLES LIKE 'transaction_isolation';
「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC
注意：transaction_isolation 是在 MySQL 5.7.20 的版本中引入來替換 tx_isolation 的，如果你使用的是之前版本的 MySQL，請将上述用到系統變量 transaction_isolation的 地方替換為 tx_isolation。

或者使用更簡便的寫法：

SELECT @@transaction_isolation;
「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

我們之前使用 SET TRANSACTION 文法來設定事務的隔離級别時，其實就是在間接設定系統變量 transaction_isolation 的值，我們也可以直接修改系統變量 transaction_isolation 來設定事務的隔離級别。系統變量一般系統變量隻有 GLOBAL 和 SESSION 兩個作用範圍，而這個 transaction_isolation 卻有3個（GLOBAL、SESSION、僅作用于下一個事務），設定文法上有些特殊，更多詳情可以參見文檔：transaction_isolation。

這裡總結下：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

四、MVCC 原理

4.1 版本鍊

對于使用 InnoDB 存儲引擎的表來說，它的聚簇索引記錄中都包含兩個必要的隐藏列（row_id 并不是必要的，我們建立的表中有主鍵或者有 NOT NULL 限制的 UNIQUE 鍵時都不會包含 row_id 列）。

  • trx_id：每次一個事務對某條聚集索引記錄進行改動時，都會把該事務的事務 id 指派給 trx_id 隐藏列。
  • roll_pointer：每次對某條聚簇索引記錄進行改動時，都會把舊的版本寫入到 undo 日志中，然後這個隐藏列就相當于一個指針，可以通過它來找到該記錄修改前的資訊。

比方說我們的表 hero 現在隻包含一條記錄：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

假設插入該記錄的事務 id 為 80，那麼此刻該條記錄的示意圖如下所示：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

假設之後兩個事務 id 分别為 100、200 的事務對這條記錄進行 UPDATE 操作，操作流程如下：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC
是否可以在兩個事務中交叉更新同一條記錄呢？不可以！這不就是一個事務修改了另一個未送出事務修改過的資料，淪為了髒寫了麼？InnoDB 使用鎖來保證不會有髒寫情況的發生，也就是在第一個事務更新某條記錄前，就會給這條記錄加鎖，另一個事務再次更新該記錄時，就需要等待第一個事務送出，把鎖釋放之後才可以繼續更新。是以這裡 trx 200 在 ③④⑤ 步的時候因為鎖的原因是被阻塞的。

每次對記錄進行改動，都會記錄一條 undo 日志，每條 undo 日志也都有一個 roll_pointer 屬性（INSERT 操作對應的 undo 日志沒有該屬性，因為該記錄并沒有更早的版本），可以将這些 undo 日志都連起來，串成一個連結清單，是以現在的情況就像下圖一樣：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

在每次更新該記錄後，都會将舊值放到一條 undo 日志中（就算是該記錄的一個舊版本），随着更新次數的增多，所有的版本都會被 roll_pointer 屬性連接配接成一個連結清單，我們把這個連結清單稱之為版本鍊，版本鍊的頭節點就是目前記錄最新的值。另外，每個版本中還包含生成該版本時對應的事務 id（這很重要），我們之後會利用這個記錄的版本鍊來控制并發事務通路相同記錄的行為，我們把這種機制稱之為多版本并發控制（Multi-Version Concurrency Control，MVCC）。

從上圖可以看到，聚集索引記錄和 undo 日志中的 roll_pointer 屬性可以串連成一個記錄的版本鍊。同一條記錄在系統中可以存在多個版本，就是資料庫的多版本并發控制（MVCC）。

在 UPDATE 操作産生的 undo 日志中，隻會記錄一些索引列以及被更新的列的資訊，并不會記錄所有列的資訊，上一張圖展示的 undo 日志中，之是以将一條記錄的全部列的資訊都畫出來是為了友善了解（因為這樣很直覺的顯示了該版本中各個列的值是什麼）。比如對于 trx_id為 80 的那條 undo 日志來說，本身是沒有記錄 country 列的資訊的，那怎麼知道該版本中 country 列的值是多少呢？沒有更新該列則說明該列和上一個版本中的值相同。如果上一個版本的 undo 日志也沒有記錄該列的值，那麼就和上上個版本中該列的值相同。如果各個版本的 undo 日志都沒有記錄該列的值，說明該列從未被更新過，那麼 trx_id 為 80 的那個版本的 country 列的值就和資料頁中的聚集索引記錄的 country 列的值相同。

4.2 ReadView

一條記錄竟然更新了那麼多版本？版本鍊中的哪個版本的記錄是目前事務可見的？這在不同隔離級别中可見性是不相同的

  • 對于使用 READ UNCOMMITTED 隔離級别的事務來說，由于可以讀到未送出事務修改過的記錄，是以直接讀取記錄的最新版本就好了。（不生成 ReadView）。
  • 對于使用 SERIALIZABLE 隔離級别的事務來說，設計 InnoDB 的大叔規定使用加鎖的方式來通路記錄。（不生成 ReadView）。
  • 對于使用 READ COMMITTED 和 REPEATABLE READ 隔離級别的事務來說，都必須保證讀到已經送出的事務修改過的記錄，也就是說假如另一個事務已經修改了記錄但是尚未送出，則不能直接讀取最新版本的記錄。（隻有 RC、RR 這 2 個隔離級别在讀取資料的時候生成 ReadView）。
一定要注意，沒有事務就沒有 ReadView，ReadView 是事務産生的，而且是基于整個資料庫的。

對此，設計InnoDB的大叔提出了一個ReadView（有的翻譯為“一緻性視圖”）的概念。

注意！在 MySQL 裡有兩個“視圖”的概念：

一個 view 是一個用查詢語句定義的虛拟表，在調用的時候執行查詢語句并生成結果。建立視圖的文法是 create view ...，而它的查詢方法與表一樣。

另一個是 InnoDB 在實作 MVCC 時用到的一緻性讀視圖，即 consistent read view，用于支援 RC 和 RR 隔離級别的實作。 ReadView 它沒有實體結構，作用是事務執行期間用來定義“我能看到什麼資料”。

這個ReadView中主要包含4個比較重要的内容：

  1. m_ids：表示在生成 ReadView 時目前系統中活躍的讀寫事務的事務 id 清單。“活躍”指的就是，啟動了但還沒送出。
  2. min_trx_id：表示在生成 ReadView 時目前系統中活躍的讀寫事務中最小的事務 id，也就是m_ids中的最小值。
  3. max_trx_id：表示生成 ReadView 時系統中應該配置設定給下一個事務的事務 id 值。
注意 max_trx_id 并不是 m_ids 中的最大值，事務id是遞增配置設定的。比方說現在有事務 id 為 1，2，3 這三個事務，之後事務id為3的事務送出了。那麼一個新的讀事務在生成 ReadView時，m_ids 就包括 1 和 2，min_trx_id 的值就是 1，max_trx_id 的值就是 4。
  1. creator_trx_id：表示生成該 ReadView 的事務的事務 id。
隻有在對表中的記錄做改動時（執行INSERT、DELETE、UPDATE這些語句時）才會為事務配置設定 trx_id，否則在一個隻讀事務中的事務 id 的值 trx_id 都預設為 0，未配置設定 trx_id 前，creator_trx_id 的值為 0，配置設定 trx_id 後，creator_trx_id 就變化成了對應的事務的 trx_id。

在 MySQL 中，READ COMMITTED 和 REPEATABLE READ 隔離級别的的一個非常大的差別就是它們生成 ReadView 的時機不同。我們還是以表 hero 為例來，假設現在表 hero 中隻有一條由事務 id 為 80 的事務插入的一條記錄：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC
注意：當一個 ReadView 生成了，m_ids、min_trx_id、max_trx_id、creator_trx_id 等變量的值都是固定的，比如此時有事務送出，m_ids 活躍事務清單的值也不會變。ReadView 就像快照一樣，生成了就不再變，除非生成新的。

接下來看一下 READ COMMITTED 和 REPEATABLE READ 所謂的生成 ReadView 的時機不同到底不同在哪裡。

4.2.1 READ COMMITTED —— 一個事務中每次讀取資料前都生成一個 ReadView

比如，現在系統裡有兩個事務id分别為100、200的事務在執行：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC
再次強調，事務執行過程中，隻有在第一次真正修改記錄時（比如使用 INSERT、DELETE、UPDATE 語句），才會被配置設定一個唯一的事務id，這個事務 id 是遞增的。是以我們才在 Transaction 200 中更新一些别的表的記錄，目的是讓它配置設定事務 id。

此刻，表 hero 中 number 為 1 的記錄得到的版本連結清單如下所示：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

假設現在有一個使用 READ COMMITTED 隔離級别的事務開始執行：

# 使用READ COMMITTED隔離級别的事務
BEGIN;

# SELECT1：Transaction 100、200未送出
SELECT * FROM hero WHERE number = 1; # 得到的列name的值為'劉備'

這個 SELECT1 的執行過程如下：

  1. 在執行 SELECT 語句時會先生成一個 ReadView，ReadView 的 m_ids 清單的内容就是[100, 200]，min_trx_id 為 100，max_trx_id 為 201，creator_trx_id 為 0。
  2. 然後從版本鍊中挑選可見的記錄。從圖中可以看出，最新版本的列 name 的内容是’張飛’，該版本的 trx_id 值為 100，在 m_ids 清單内，說明 trx_id 為 100 的事務還沒送出，是以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 下一個版本的列 name 的内容是’關羽’，該版本的 trx_id 值也為 100，也在 m_ids 清單内，是以也不符合要求，繼續跳到下一個版本。
  4. 下一個版本的列 name 的内容是’劉備’，該版本的 trx_id 值為 80，小于 ReadView 中的 min_trx_id 值 100，說明 trx_id 為 80 的事務已經送出了，是以這個版本是符合要求的，最後傳回給使用者的版本就是這條列 name 為’劉備’的記錄。

之後，我們把事務 id 為 100 的事務送出一下，如下：

# Transaction 100
BEGIN;
UPDATE hero SET name = '關羽' WHERE number = 1;
UPDATE hero SET name = '張飛' WHERE number = 1;
COMMIT;

然後再到事務 id 為 200 的事務中更新一下表 hero 中 number 為 1 的記錄：

# Transaction 200
BEGIN;

# 更新了一些别的表的記錄
...
UPDATE hero SET name = '趙雲' WHERE number = 1;
UPDATE hero SET name = '諸葛亮' WHERE number = 1;

此刻，表 hero 中 number 為 1 的記錄的版本鍊就長這樣：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

然後再到剛才使用 READ COMMITTED 隔離級别的事務中繼續查找這個 number 為 1 的記錄，如下：

# 使用 READ COMMITTED 隔離級别的事務
BEGIN;

# SELECT1：Transaction 100、200均未送出（第一次查詢兩個事務均未送出）
SELECT * FROM hero WHERE number = 1; # 得到的列name的值為'劉備'

# SELECT2：Transaction 100送出，Transaction 200未送出（第二次查詢事務id為100的事務送出了）
SELECT * FROM hero WHERE number = 1; # 得到的列name的值為'張飛'

分析一下 SELECT2 的執行過程

  1. 在執行 SELECT 語句時會又會單獨生成一個 ReadView，該 ReadView 的 m_ids 清單的内容就是[200]（事務 id 為 100 的那個事務已經送出了，是以再次生成 ReadView 時就沒有它了），min_trx_id 為 200，max_trx_id 為 201，creator_trx_id 為 0。
  2. 然後從版本鍊中挑選可見的記錄，從圖中可以看出，最新版本的列 name 的内容是’諸葛亮’，該版本的 trx_id 值為 200，在m_ids 清單内，是以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 下一個版本的列 name 的内容是’趙雲’，該版本的 trx_id 值為200，也在 m_ids 清單内，是以也不符合要求，繼續跳到下一個版本。
  4. 下一個版本的列 name 的内容是’張飛’，該版本的 trx_id 值為100，小于 ReadView 中的 min_trx_id 值 200，是以這個版本是符合要求的，最後傳回給使用者的版本就是這條列 name 為’張飛’的記錄。

以此類推，如果之後事務 id 為 200 的記錄也送出了，再次在使用 READ COMMITTED 隔離級别的事務中查詢表 hero 中 number 值為 1 的記錄時，得到的結果就是’諸葛亮’了。總結一下就是：使用 READ COMMITTED 隔離級别的事務在每次查詢開始時都會生成一個獨立的 ReadView。

注意：RC 下，在一個事務中，一條查詢語句執行完，事務生成的 ReadView 就沒用了，下次查詢得重新生成 ReadView。

4.2.2 REPEATABLE READ —— 一個事務中第一次讀取資料時生成一個 ReadView

  按照可重複讀的定義，一個事務啟動的時候，能夠看到所有已經送出的事務結果。但是之後這個事務執行期間，其他事務的更新對它不可見。

  對于使用 REPEATABLE READ 隔離級别的事務來說，隻會在第一次執行查詢語句時生成一個 ReadView，之後的查詢就不會重複生成了。來用和之前相同的例子再次分析。

比如，現在系統裡有兩個事務 id 分别為 100、200 的事務在執行：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

此刻，表 hero 中 number 為 1 的記錄得到的版本連結清單如下所示：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

假設現在有一個使用 REPEATABLE READ 隔離級别的事務開始執行：

# 使用 REPEATABLE READ 隔離級别的事務
BEGIN;

# SELECT1：Transaction 100、200 未送出
SELECT * FROM hero WHERE number = 1; # 得到的列name的值為'劉備'

這裡和 4.2.1 節的 READ COMMITTED 隔離級别的 SELECT1 分析過程一模一樣，不贅述。查詢出來是 name 為’劉備’ 的記錄。

我們把事務 id 為 100 的事務送出一下，如下：

# Transaction 100
BEGIN;
UPDATE hero SET name = '關羽' WHERE number = 1;
UPDATE hero SET name = '張飛' WHERE number = 1;
COMMIT;

然後再到事務 id 為 200 的事務中更新一下表 hero 中 number 為 1 的記錄：

# Transaction 200
BEGIN;

# 更新了一些别的表的記錄
...
UPDATE hero SET name = '趙雲' WHERE number = 1;
UPDATE hero SET name = '諸葛亮' WHERE number = 1;

此刻，表 hero 中 number 為 1 的記錄的版本鍊就長這樣：

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

一直到這裡，例子分析和 4.2.1 節的 READ COMMITTED 隔離級别的分析過程一樣。接下來，不一樣的來了。

然後再到剛才使用 REPEATABLE READ 隔離級别的事務中繼續查找這個 number 為 1 的記錄，如下：

# 使用REPEATABLE READ 隔離級别的事務300
BEGIN;

# SELECT1：Transaction 100、200 均未送出
SELECT * FROM hero WHERE number = 1; # 得到的列name的值為'劉備'

# SELECT2：Transaction 100 送出，Transaction 200 未送出
SELECT * FROM hero WHERE number = 1; # 得到的列name的值仍為'劉備'

注意，這個 SELECT2 的執行過程如下：

  1. 因為目前事務的隔離級别為 REPEATABLE READ，而之前在執行 SELECT1 時已經生成過 ReadView 了，是以此時直接複用之前的 ReadView。 之前的 ReadView 的 m_ids清單的内容就是 [100, 200]，min_trx_id 為 100，max_trx_id 為 201，creator_trx_id 為 0。
  2. 然後從版本鍊中挑選可見的記錄，從圖中可以看出，最新版本的列 name 的内容是’諸葛亮’，該版本的 trx_id 值為 200，在 m_ids 清單内，是以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 下一個版本的列 name 的内容是’趙雲’，該版本的 trx_id 值為 200，也在 m_ids 清單内，是以也不符合要求，繼續跳到下一個版本。
  4. 下一個版本的列 name 的内容是’張飛’，該版本的 trx_id 值為 100，而 m_ids 清單中是包含值為 100 的事務 id 的，是以該版本也不符合要求，同理下一個列 name 的内容是’關羽’的版本也不符合要求。繼續跳到下一個版本。
  5. 下一個版本的列 name 的内容是’劉備’，該版本的 trx_id 值為 80，小于 ReadView 中的min_trx_id 值 100，是以這個版本是符合要求的，最後傳回給使用者的版本就是這條列 name 為’劉備’的記錄。

也就是說在 REPEATABLE READ 隔離級别下，事務的兩次查詢得到的結果是一樣的。記錄的 name 列值都是’劉備’，這就是為什麼在 RR 下，不會出現不可重複讀的理由。如果我們之後再把事務 id 為 200 的記錄送出了，然後再到剛才使用 REPEATABLE READ 隔離級别的事務中繼續查找這個 number 為 1 的記錄，得到的結果還是’劉備’。

要想讀到最新 name 值為’諸葛亮’該如何操作呢？

前提: 把事務 id 為 100、200 的事務送出。

  1. 此時把事務 id 為 300 的事務送出，ReadView 就沒用了，下次開啟新的事務查詢的時候會生成新的 ReadView，m_ids 清單中沒有 100、200，就可以查詢到 name 為’諸葛亮’了。
  2. 如果新的查詢沒有事務，那就沒有 ReadView 這一說了，直接 select 查詢就可以查到 name 為’諸葛亮’的記錄，因為事務 100、200 已送出。

注意對比：

RR 下，一個事務送出時，它生成的 ReadView 就沒用了。

RC 下，在一個事務中，一條查詢語句執行完，事務生成的 ReadView 就沒用了，下次查詢得重新生成 ReadView。

小提示：

  在 RR 下，如果使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 語句開啟事務，會在執行該語句後立即生成一個R eadView，而不是在執行第一條 SELECT 語句時才生成。

  使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 這個語句開始，建立一個持續整個事務的 ReadView。是以在 RC 隔離級别下（每次讀都建立 ReadView），這個用法就沒意義了，等效于普通的 start transaction。

4.2.3 ReadView 的可見性規則總結

在通路某條記錄時，隻需要按照下邊的步驟判斷記錄的某個版本是否可見：

  1. trx_id = creator_trx_id時，意味着目前事務在通路它自己修改過的記錄，是以該版本可以被目前事務通路。
  2. trx_id < min_trx_id時，表明生成該版本的事務在目前事務生成ReadView前已經送出，是以該版本可以被目前事務通路。
  3. trx_id ≥ max_trx_id時，表明生成該版本的事務在目前事務生成ReadView後才開啟，是以該版本不可以被目前事務通路。
  4. min_trx_id ≤ trx_id ≤ max_trx_id之間，那就需要判斷一下trx_id屬性值是不是在m_ids清單中，如果在，說明建立ReadView時生成該版本的事務還是活躍的，該版本不可以被通路；如果不在，說明建立ReadView時生成該版本的事務已經被送出，該版本可以被通路。

如果某個版本的資料對目前事務不可見的話，那就順着版本鍊找到下一個版本的資料，繼續按照上邊的步驟判斷可見性，依此類推，直到版本鍊中的最後一個版本。如果最後一個版本也不可見的話，那麼就意味着該條記錄對該事務完全不可見，查詢結果就不包含該記錄。

上面說了，ReadView 是基于整庫的。如果一個庫有 100G，那麼我啟動一個事務，MySQL 就要拷貝 100G 的資料出來嗎，這樣多慢啊，可是，我們平時的事務執行起來很快啊。

實際上，我們并不需要拷貝出這 100G 的資料。InnoDB 利用版本鍊和活躍的事務id清單，可以實作“秒級建立 ReadView”。

思考題：

RR 隔離級别下事務 T1 和 T2 并發執行，T1 先根據某個搜尋條件讀取到3條記錄，然後事務T2插入一條符合相應搜尋條件的記錄并送出，然後事務T1再根據相同搜尋條件執行查詢，結果如何？

分析：根據版本鍊和 ReadView 分析，T1 第一次搜尋3條記錄的時候生成了 ReadView，此時 T1、T2 都在 m_ids 清單，都是活躍的，那麼 T2 中插入的版本記錄 T1 是不可見的，是以事務 T1 第二次搜尋仍然是3條記錄。此時在 RR 下避免了幻讀的産生。

由于 MySQL 的具體實作問題，RR 隔離級别下并不能完全避免幻讀（隻能很大程度避免），隻有加鎖才可以完全避免。

4.3 為什麼不推薦使用長事務？

  前面講版本鍊的時候說過，每條記錄在更新的時候都會同時記錄一條復原的 undo 日志 （也稱為復原段）。通過復原操作，都可以得到前一個狀态的值。

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

目前 number 為 1 的記錄 name 為是'諸葛亮'，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的 ReadView。如圖，要得到 name 為'劉備'的記錄，就必須将目前值依次執行圖中所有的復原操作得到。

復原段非常占用記憶體，那復原段什麼時候删除呢？

從上圖可以看到復原段裡都是之前事務修改過的記錄，事務送出後該記錄的舊版本就不需要了，是以隻有當開啟復原段以來的所有事務都送出的時候，復原段就可以删除。

為什麼不推薦使用長事務？

長事務意味着系統裡面會存在很老的記錄，事務不送出，記錄的舊版本會一直存在。由于這些事務随時可能通路資料庫裡面的任何資料，是以這個事務送出之前，資料庫裡面它可能用到的復原記錄都必須保留，這就會導緻大量占用存儲空間。

在 MySQL 5.5 及以前的版本，復原日志是跟資料字典一起放在 ibdata 檔案裡的，即使長事務最終送出，復原段被清理，檔案也不會變小。有時候資料隻有 20GB，而復原段有 200GB的庫。最終隻好為了清理復原段，重建整個庫。

除了對復原段的影響，長事務還占用鎖資源，也可能拖垮整個庫。

如何查詢長事務？

在 information_schema 庫的 innodb_trx 這個表中查詢長事務，比如下面這個語句，用于查找持續時間超過 60s 的事務。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

4.4 非聚集索引與 MVCC

前面說過，隻有聚集索引記錄才有 trx_id 和 roll_pointer 隐藏列，如果某個查詢語句是使用二級索引來執行查詢時，該如何判斷可見性呢？

begin；
select * from hero where name = '劉備';

這裡判斷條件是 name了，這是一個普通的非聚集索引，沒有 trx_id 和 roll_pointer 該怎麼根據版本鍊和 ReadView 去判斷可見性呢？

注：trx_id 是記錄存放該事務的事務 id 的地方，沒有這一列隻能說明非聚集索引記錄沒存，并不代表執行事務時沒有事務 id 了。

過程如下：

步驟1：非聚集索引頁面的Page Header部分有一個名為 PAGE_MAX_TRX_ID 的屬性，每當對該頁面中的記錄執行增删改操作的時候，如下：

// 這裡用僞代碼說明更便捷
if(如果執行該事務的事務id > PAGE_MAX_TRX_ID) {
	PAGE_MAX_TRX_ID = 如果執行該事務的事務id;
}

是以 PAGE_MAX_TRX_ID 屬性值代表修改該非聚集索引頁面的最大的事務id。

當 SELECT 語句根據條件找到某個非聚集索引記錄時，如下：

if (對應ReadView的min_trx_id > PAGE_MAX_TRX_ID) {
	說明該頁面中的所有記錄都對該ReadView可見
} else {
	執行步驟2
}

步驟2： 根據主鍵回表後，得到滿足搜尋條件的聚集索引記錄後，根據版本鍊找到該 ReadView 可見的第一個版本，然後判斷該版本中相應的非聚集索引列的值是否與利用該非聚集索引查詢時的值相同。本例子就是判斷可見版本的 name 是不是’劉備’。如果是，就把這條記錄發送給用戶端（如果 where 子句中還有其他搜尋條件的話還需要繼續判斷篩選後再傳回），否則就跳過該記錄。

4.5 MVCC 小結

所謂的 MVCC（Multi-Version Concurrency Control ，多版本并發控制）指的就是在使用 READ COMMITTD、REPEATABLE READ 這兩種隔離級别的事務執行普通的 SELECT 操作時，通路記錄的版本鍊的過程。這樣可以使不同僚務的讀-寫、寫-讀操作并發執行，進而提升系統性能。READ COMMITTD、REPEATABLE READ 這兩個隔離級别的一個很大不同，就是生成 ReadView 的時機不同，READ COMMITTD 在一個事務中每一次進行普通 SELECT操作前都會生成一個 ReadView，而 REPEATABLE READ 在一個事務中隻在第一次進行普通 SELECT 操作前生成一個 ReadView，之後的查詢操作都重複使用這個 ReadView。

五、全篇的反思與小結，你需要弄懂這幾個問題

事務的概念是什麼?

MySQL的事務隔離級别讀未送出, 讀已送出, 可重複讀, 串行讀各是什麼意思?

讀已送出, 可重複讀是怎麼通過視圖建構實作的?

事務隔離是怎麼通過ReadView(讀視圖)實作的?

并發版本控制(MVCC)的概念是什麼, 是怎麼實作的?

使用長事務的弊病? 為什麼使用長事務可能拖垮整個庫?

怎麼查詢各個表中的長事務?

如何避免長事務的出現?

文章來源：磚業洋__https://blog.csdn.net/qq_34115899/article/details/127986548

事務隔離級别 隔離級别 圖文詳解 事務隔離 mysql
上一篇: 韓慶祥：領悟理論的偉力
下一篇: 情侶桌面更新喽～～網頁連結

繼續閱讀