MySQL 可重複讀，差點就我背上了一個 P0 事故！

MySQL 可重複讀，差點就我背上了一個 P0 事故！

小黑黑的碎碎念

哎，最近有點忙，備考複習不利，明天還要搬家，好難啊！！

本想着這周鴿了，但是想想還是不行，爬起來，更新一下，周更可不能斷。偷懶一下，修改一下之前的一篇曆史文章，重新釋出一下。

先贊後看，微信搜尋「程式通事」，關注就完事了

P0 事故：餘額多扣！

這是一個真實的生産事件，事件起因如下：

現有一個交易系統，每次産生交易都會更新相應賬戶的餘額，出賬扣減餘額，入賬增加餘額。

為了保證資金安全，餘額發生扣減時，需要比較現有餘額與扣減金額大小，若扣減金額大于現有餘額，扣減餘額不足，扣減失敗。

賬戶表（省去其他字段）結構如下：

CREATE TABLE

account

(

`id`      bigint(20) NOT NULL,
`balance` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
           

) ENGINE = InnoDB

DEFAULT CHARSET = utf8mb4

COLLATE = utf8mb4_bin;

扣減餘額時，sql 語序如下所示：

ps:看到上面的語序，有沒有個小問号？為什麼相同查詢了這麼多次？

其實這些 SQL 語序并不在同個方法内，并且有些方法被抽出複用，是以導緻一些相同查詢結果沒辦法往下傳遞，是以隻得再次從資料庫中查詢。

為了防止并發更新餘額，在 t3 時刻，使用寫鎖鎖住該行記錄。若加鎖成功，其他線程的若也執行到 t3,将會被阻塞，直到前一個線程事務送出。

t5 時刻，進入到下一個方法，再次擷取賬戶餘額，然後在 Java 方法内比較餘額與扣減金額，若餘額充足，在 t7 時刻執行更新操作。

上面的 SQL 語序看起來沒有什麼問題吧，實際也是這樣的，賬戶系統已經在生産運作很久，沒出現什麼問題。但是這裡需要說一個前提，系統資料庫是 Oracle 。

但是從上面表結構，可以得知此次資料庫被切換成 MySQL，系統其他任何代碼以及配置都不修改（sql 存在小改動）。

就是這種情況下，并發執行發生餘額多扣，即實際餘額明明小于扣減金額，但是卻做了餘額更新操作，最後導緻餘額變成了負數。

下面我們來重制并發這種情況，假設有兩個事務正在發執行該語序，執行順序如圖所示。

注意點：資料庫使用的是 MySQL，預設事務隔離等級，即 RR。資料庫記錄為 id=1 balance=1000，假設隻有當時隻有這兩個事務在執行。

各位讀者可以先思考一下，t2,t3,t4,t5,t6,t11 時刻餘額多少。

下面貼一下事務隔離等級RR 下的答案。

事務1 的查詢結果為：

t2 (1,1000)

t4 (1,1000)

t6 (1,1000)

事務 2 的查詢結果為：

t3 (1,1000)

t5 (1,900)

t11 (1,1000)

有沒有跟你想的結果的一樣？

接着将事務隔離等級修改成 RC，同樣再來思考一下 t2,t3,t4,t5,t6,t11 時刻餘額。

再次貼下事務隔離等級RC 下的答案。

t11 (1,900)

事務 1 的查詢結果，大家應該會沒有什麼問題，主要疑問點應該在于事務 2，為什麼換了事務隔離等級結果卻不太一樣？

下面我們先帶着疑問，了解一下 MySQL 的相關原理 ，看完你就會明白這一切。

MVCC

一緻性視圖

快照讀與目前讀

我們先來看下一個簡單的例子，

事務隔離等級為 RR ， id=1 balance=1000

事務 1 将 id=1 記錄 balance 更新為 900，接着事務 2 在 t5 時刻查詢該行記錄結果，很顯然該行記錄應該為 id=1 balance=1000。

如果 t5 查詢最新結果 id=1 balance=900，這就讀取到事務 1 未送出的資料，顯然不符合目前事務隔離級别。

從上面例子可以看到 id=1 的記錄存在兩個版本，事務 1 版本記錄為 balance=1000 ，事務 2 版本記錄為 balance=900。

上述功能，MySQL 使用 MVCC 機制實作功能。

MVCC：Multiversion concurrency control，多版本并發控制。摘錄一段淘寶資料庫月報的解釋：

多版本控制: 指的是一種提高并發的技術。最早的資料庫系統，隻有讀讀之間可以并發，讀寫，寫讀，寫寫都要阻塞。引入多版本之後，隻有寫寫之間互相阻塞，其他三種操作都可以并行，這樣大幅度提高了InnoDB的并發度。在内部實作中，與Postgres在資料行上實作多版本不同，InnoDB是在undolog中實作的，通過undolog可以找回資料的曆史版本。找回的資料曆史版本可以提供給使用者讀(按照隔離級别的定義，有些讀請求隻能看到比較老的資料版本)，也可以在復原的時候覆寫資料頁上的資料。在InnoDB内部中，會記錄一個全局的活躍讀寫事務數組，其主要用來判斷事務的可見性。

可以看到 MVCC 主要用來提高并發，還可以用來讀取老版本資料。

在學習 MVCC 原理之前，首先我們需要了解 MySQL 記錄結構。

如上圖所示，account 表一行記錄，除了真實資料之外，還會存在三個隐藏字段，用來記錄額外資訊。

DB_TRX_ID:事務id。

DB_ROLL_PTR: 復原指針，指向 undolog。

ROW_ID：行 id,與此次無關。

MySQL InnoDB 裡面每個事務都會有一個唯一事務 ID，它在事務開始的時候會跟 InnoDB 的事務系統申請的，并且嚴格按照順序遞增的。

每次事務更新資料時，将會生成一個新的資料版本，然後會把目前的事務 id 指派給目前記錄的 DB_TRX_ID。并且資料更新記錄（1,1000---->1,900）将會記錄在 undo log（復原日志）中，然後使用目前記錄的 DB_ROLL_PTR 指向 und olog。

這樣 MySQL 就可以通過 DB_ROLL_PTR 找到 undolog 推導出之前版本記錄内容。

查找過程如下：

若需要知道 V1 版本記錄，首先根據目前版本 V3 的 DB_ROLL_PTR 找到 undolog，然後根據 undolog 内容，計算出上一個版本 V2。以此類推，最終找到 V1 這個版本記錄。

V1，V2 并不是實體記錄，沒有真實存在，僅僅具有邏輯意義。

一行資料記錄可能同時存在多個版本，但并不是所有記錄都能對目前事務可見。不然上面 t5 就可能查詢到最新的資料。是以查找資料版本時候 MySQL 必須判斷資料版本是否對目前事務可見。

MySQL 會在事務開始後建立一個一緻性視圖（并不是立刻建立），在這個視圖中，會儲存所有活躍的事務(還未送出的事務)。

假設目前事務儲存活躍事務數組為如下圖。

判斷版本對于目前事務是否可見時，基于以下規則判斷：

若版本事務 id 小于目前活躍事務 id 數組最小值,比如版本 id 為 40，小于活躍數組最小值 45。這就代表目前版本的事務已送出，目前版本對于目前事務可見。

若版本事務 id 大于目前活躍事務數組的最大值，如版本事務 id 為 100， 大于數組最大事務 id 90。說明了這個版本是目前事務建立之後生成，是以這個版本對于目前事務不可見。

若版本事務 id 是目前活躍數組事務之一，比如版本事務 id 為 56。代表記錄版本所屬事務還未送出，是以該版本對于目前事務不可見。

若版本事務 id 不是目前活躍數組事務之一，但是事務 id 位于活躍數組最小值與最大值之一，比如如事務 ID 57。代表目前記錄事務已送出，是以該版本對于目前事務可見。

若版本事務 id 為目前事務 id，代表該行資料是目前事務變更的，當然得可見。

4 這個規則可能比較繞，結合上面圖檔比較好了解。

以上判斷規則可能比較抽象，看不懂，沒事，我們再用大白話解釋一下：

未送出事務生成的記錄版本，不可見。

視圖生成前，已送出事務生成記錄版本可見。

視圖生成後，新事務生成記錄版本不可見。

自身事務更新永遠可見。

一緻性視圖隻會在 RR 與 RC 下才會生成，對于 RR 來說，一緻性視圖會在第一個查詢語句的時候生成。而對于 RC 來說，每個查詢語句都會重新生成視圖。

目前讀與快照讀

MySQL 使用 MVCC 機制，可以讀取之前版本資料。這些舊版本記錄不會且也無法再去修改，就像快照一樣。是以我們将這種查詢稱為快照讀。

當然并不是所有查詢都是快照讀，select .... for update/ in share mode 這類加鎖查詢隻會查詢目前記錄最新版本資料。我們将這種查詢稱為目前讀。

問題分析

講完原理之後，我們回過頭分析一下上面查詢結果的原因。

這裡我們将上面答案再貼過來。

事務隔離級别為 RR，t2，t3 時刻兩個事務由于查詢語句，分别建立了一緻性視圖。

t4 時刻，由于事務 1 使用 select.. for update 為 id=1 這一行上了一把鎖，然後擷取到最新結果。而 t5 時刻，由于該行已被上鎖，事務 2 必須等待事務 1 釋放鎖才能繼續執行。

t6 時刻根據一緻性視圖，不能讀取到其他事務送出的版本，是以資料沒變。t8 時刻餘額扣減 100，t9 時刻送出事務。

此時最新版本記錄為 id=1 balance=900。

由于事務 1 事務已送出，行鎖被釋放，t5 成功擷取到鎖。由于 t5 是目前讀，是以查詢的結果為最新版本資料（1,900）。

重點來了，目前這條記錄的最新版本資料為 （1,900），但是最新版本事務 id，卻是事務 2 建立之後未送出的事務，位于活躍事務數組中。是以最新記錄版本對于事務 2 是不可見的。

沒辦法隻能根據 undolog 去讀取上一版本記錄 (1,1000) ，這個版本記錄剛好對于事務 2 可見，是以 t11 的記錄為 (1,1000)。

而當我們将事務隔離等級修改成 RC，每次都會重新生成一緻性視圖。是以 t11 時刻重新生成了一緻性視圖，這時候事務 1 已送出，目前最新版本的記錄對于事務 2 可見，是以 t11 的結果将會變為 （1,900）。

總結

MySQL 預設事務隔離等級為 RR，每一行資料（InnoDB）的都可以有多個版本，而每個版本都有獨一的事務 id。

MySQL 通過一緻性視圖確定資料版本的可見性，相關規則總結如下：

對于 RR 事務隔離等級，普通查詢僅能查到事務啟動前就已經送出完成的版本資料。

對于 RC 事務隔離等級，普通查詢可以查到查詢語句啟動前就已經送出完成的版本資料。

目前讀總是讀取最新版本的資料。

幫助文檔

1: 

https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

2 

http://mysql.taobao.org/monthly/2017/12/01/

3 

http://mysql.taobao.org/monthly/2018/11/04/

4 

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

5 極客時間- MySQL 專欄--事務到底是隔離的還是不隔離的

原文位址

https://www.cnblogs.com/goodAndyxublog/p/13023144.html