MySQL 可重複讀,差點就讓我背上了一個 P0 事故!_台中搬家

台中搬家公司費用怎麼算?

擁有20年純熟搬遷經驗,提供免費估價且流程透明更是5星評價的搬家公司

小黑黑的碎碎念

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

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

先贊后看,微信搜索「程序通事」,關注就完事了

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 下的答案。

事務1 的查詢結果為:

  • t2 (1,1000)
  • t4 (1,1000)
  • t6 (1,1000)

事務 2 的查詢結果為:

  • t3 (1,1000)
  • t5 (1,900)
  • t11 (1,900)

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

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

  • MVCC
  • 一致性視圖
  • 快照讀與當前讀

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 記錄結構。

台中搬家遵守搬運三大原則,讓您的家具不再被破壞!

台中搬家公司推薦超過30年經驗,首選台中大展搬家

如上圖所示,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 會在事務開始后建立一個一致性視圖(並不是立刻建立),在這個視圖中,會保存所有活躍的事務(還未提交的事務)。

假設當前事務保存活躍事務數組為如下圖。

判斷版本對於當前事務是否可見時,基於以下規則判斷:

  1. 若版本事務 id 小於當前活躍事務 id 數組最小值,比如版本 id 為 40,小於活躍數組最小值 45。這就代表當前版本的事務已提交,當前版本對於當前事務可見。

  2. 若版本事務 id 大於當前活躍事務數組的最大值,如版本事務 id 為 100, 大於數組最大事務 id 90。說明了這個版本是當前事務創建之後生成,所以這個版本對於當前事務不可見。

  3. 若版本事務 id 是當前活躍數組事務之一,比如版本事務 id 為 56。代表記錄版本所屬事務還未提交,所以該版本對於當前事務不可見。

  4. 若版本事務 id 不是當前活躍數組事務之一,但是事務 id 位於活躍數組最小值與最大值之一,比如如事務 ID 57。代表當前記錄事務已提交,所以該版本對於當前事務可見。

  5. 若版本事務 id 為當前事務 id,代表該行數據是當前事務變更的,當然得可見。

4 這個規則可能比較繞,結合上面圖片比較好理解。

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

  1. 未提交事務生成的記錄版本,不可見。

  2. 視圖生成前,已提交事務生成記錄版本可見。

  3. 視圖生成后,新事務生成記錄版本不可見。

  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 專欄–事務到底是隔離的還是不隔離的

歡迎關注我的公眾號:程序通事,獲得日常乾貨推送。如果您對我的專題內容感興趣,也可以關注我的博客:studyidea.cn

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

台中搬家公司費用怎麼算?

擁有20年純熟搬遷經驗,提供免費估價且流程透明更是5星評價的搬家公司