MySQL 中怎么實(shí)現(xiàn)可重復(fù)讀操作

這篇文章將為大家詳細(xì)講解有關(guān)MySQL 中怎么實(shí)現(xiàn)可重復(fù)讀操作，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

賬戶表(省去其他字段)結(jié)構(gòu)如下：

CREATE TABLE `account` (     `id`      bigint(20) NOT NULL,     `balance` bigint(20) DEFAULT NULL,     PRIMARY KEY (`id`)  ) ENGINE = InnoDB   DEFAULT CHARSET = utf8mb4   COLLATE = utf8mb4_bin;

扣減余額時(shí)，sql 語序如下所示：

更新余額 sql 語序

ps:看到上面的語序，有沒有個(gè)小問號(hào)?為什么相同查詢了這么多次?

其實(shí)這些 SQL 語序并不在同個(gè)方法內(nèi)，并且有些方法被抽出復(fù)用，所以導(dǎo)致一些相同查詢結(jié)果沒辦法往下傳遞，所以只得再次從數(shù)據(jù)庫中查詢。

為了防止并發(fā)更新余額，在 t3 時(shí)刻，使用寫鎖鎖住該行記錄。若加鎖成功，其他線程的若也執(zhí)行到 t3,將會(huì)被阻塞，直到前一個(gè)線程事務(wù)提交。

t5 時(shí)刻，進(jìn)入到下一個(gè)方法，再次獲取賬戶余額，然后在 Java 方法內(nèi)比較余額與扣減金額，若余額充足，在 t7 時(shí)刻執(zhí)行更新操作。

上面的 SQL 語序看起來沒有什么問題吧，實(shí)際也是這樣的，賬戶系統(tǒng)已經(jīng)在生產(chǎn)運(yùn)行很久，沒出現(xiàn)什么問題。但是這里需要說一個(gè)前提，系統(tǒng)數(shù)據(jù)庫是 Oracle  。

但是從上面表結(jié)構(gòu)，可以得知此次數(shù)據(jù)庫被切換成 MySQL，系統(tǒng)其他任何代碼以及配置都不修改(sql 存在小改動(dòng))。

就是這種情況下，并發(fā)執(zhí)行發(fā)生余額多扣，即實(shí)際余額明明小于扣減金額，但是卻做了余額更新操作，最后導(dǎo)致余額變成了負(fù)數(shù)。

下面我們來重現(xiàn)并發(fā)這種情況，假設(shè)有兩個(gè)事務(wù)正在發(fā)執(zhí)行該語序，執(zhí)行順序如圖所示。

注意點(diǎn)：數(shù)據(jù)庫使用的是 MySQL，默認(rèn)事務(wù)隔離等級(jí)，即 RR。數(shù)據(jù)庫記錄為 id=1  balance=1000，假設(shè)只有當(dāng)時(shí)只有這兩個(gè)事務(wù)在執(zhí)行。

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

下面貼一下事務(wù)隔離等級(jí)RR 下的答案。

事務(wù) 1 的查詢結(jié)果為：

• t2 (1,1000)

• t4 (1,1000)

• t6 (1,1000)

事務(wù) 2 的查詢結(jié)果為：

• t3 (1,1000)

• t5 (1,900)

• t11 (1,1000)

有沒有跟你想的結(jié)果的一樣?

接著將事務(wù)隔離等級(jí)修改成 RC，同樣再來思考一下 t2,t3,t4,t5,t6,t11 時(shí)刻余額。

再次貼下事務(wù)隔離等級(jí)RC 下的答案。

事務(wù) 1 的查詢結(jié)果為：

• t2 (1,1000)

• t4 (1,1000)

• t6 (1,1000)

事務(wù) 2 的查詢結(jié)果為：

• t3 (1,1000)

• t5 (1,900)

• t11 (1,900)

事務(wù) 1 的查詢結(jié)果，大家應(yīng)該會(huì)沒有什么問題，主要疑問點(diǎn)應(yīng)該在于事務(wù) 2，為什么換了事務(wù)隔離等級(jí)結(jié)果卻不太一樣?

下面我們先帶著疑問，了解一下 MySQL 的相關(guān)原理 ，看完你就會(huì)明白這一切。

• MVCC

• 一致性視圖

• 快照讀與當(dāng)前讀

MVCC

我們先來看下一個(gè)簡(jiǎn)單的例子，

事務(wù)隔離等級(jí)為 RR ， id=1 balance=1000

更新時(shí)序

事務(wù) 1 將 id=1 記錄 balance 更新為 900，接著事務(wù) 2 在 t5 時(shí)刻查詢?cè)撔杏涗浗Y(jié)果，很顯然該行記錄應(yīng)該為 id=1  balance=1000。

如果 t5 查詢最新結(jié)果 id=1 balance=900，這就讀取到事務(wù) 1 未提交的數(shù)據(jù)，顯然不符合當(dāng)前事務(wù)隔離級(jí)別。

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

上述功能，MySQL 使用 MVCC 機(jī)制實(shí)現(xiàn)功能。

MVCC：Multiversion concurrency control，多版本并發(fā)控制。摘錄一段淘寶數(shù)據(jù)庫月報(bào)的解釋：

多版本控制:  指的是一種提高并發(fā)的技術(shù)。最早的數(shù)據(jù)庫系統(tǒng)，只有讀讀之間可以并發(fā)，讀寫，寫讀，寫寫都要阻塞。引入多版本之后，只有寫寫之間相互阻塞，其他三種操作都可以并行，這樣大幅度提高了  InnoDB 的并發(fā)度。在內(nèi)部實(shí)現(xiàn)中，與 Postgres 在數(shù)據(jù)行上實(shí)現(xiàn)多版本不同，InnoDB 是在 undolog 中實(shí)現(xiàn)的，通過 undolog  可以找回?cái)?shù)據(jù)的歷史版本。找回的數(shù)據(jù)歷史版本可以提供給用戶讀(按照隔離級(jí)別的定義，有些讀請(qǐng)求只能看到比較老的數(shù)據(jù)版本)，也可以在回滾的時(shí)候覆蓋數(shù)據(jù)頁上的數(shù)據(jù)。在  InnoDB 內(nèi)部中，會(huì)記錄一個(gè)全局的活躍讀寫事務(wù)數(shù)組，其主要用來判斷事務(wù)的可見性。

可以看到 MVCC 主要用來提高并發(fā)，還可以用來讀取老版本數(shù)據(jù)。

在學(xué)習(xí) MVCC 原理之前，首先我們需要了解 MySQL 記錄結(jié)構(gòu)。

行記錄

如上圖所示，account 表一行記錄，除了真實(shí)數(shù)據(jù)之外，還會(huì)存在三個(gè)隱藏字段，用來記錄額外信息。

• DB_TRX_ID:事務(wù) id。

• DB_ROLL_PTR: 回滾指針，指向 undolog。

• ROW_ID：行 id,與此次無關(guān)。

MySQL InnoDB 里面每個(gè)事務(wù)都會(huì)有一個(gè)唯一事務(wù) ID，它在事務(wù)開始的時(shí)候會(huì)跟 InnoDB 的事務(wù)系統(tǒng)申請(qǐng)的，并且嚴(yán)格按照順序遞增的。

每次事務(wù)更新數(shù)據(jù)時(shí)，將會(huì)生成一個(gè)新的數(shù)據(jù)版本，然后會(huì)把當(dāng)前的事務(wù) id 賦值給當(dāng)前記錄的  DB_TRX_ID。并且數(shù)據(jù)更新記錄(1,1000---->1,900)將會(huì)記錄在 undo log(回滾日志)中，然后使用當(dāng)前記錄的  DB_ROLL_PTR 指向 und olog。

這樣 MySQL 就可以通過 DB_ROLL_PTR 找到 undolog 推導(dǎo)出之前版本記錄內(nèi)容。

查找過程如下：

查找過程

若需要知道 V1 版本記錄，首先根據(jù)當(dāng)前版本 V3 的 DB_ROLL_PTR 找到 undolog，然后根據(jù) undolog 內(nèi)容，計(jì)算出上一個(gè)版本  V2。以此類推，最終找到 V1 這個(gè)版本記錄。

V1，V2 并不是物理記錄，沒有真實(shí)存在，僅僅具有邏輯意義。

一行數(shù)據(jù)記錄可能同時(shí)存在多個(gè)版本，但并不是所有記錄都能對(duì)當(dāng)前事務(wù)可見。不然上面 t5 就可能查詢到最新的數(shù)據(jù)。所以查找數(shù)據(jù)版本時(shí)候 MySQL  必須判斷數(shù)據(jù)版本是否對(duì)當(dāng)前事務(wù)可見。

一致性視圖

MySQL 會(huì)在事務(wù)開始后建立一個(gè)一致性視圖(并不是立刻建立)，在這個(gè)視圖中，會(huì)保存所有活躍的事務(wù)(還未提交的事務(wù))。

假設(shè)當(dāng)前事務(wù)保存活躍事務(wù)數(shù)組為如下圖。

視圖數(shù)組

判斷版本對(duì)于當(dāng)前事務(wù)是否可見時(shí)，基于以下規(guī)則判斷：

• 若版本事務(wù) id 小于當(dāng)前活躍事務(wù) id 數(shù)組最小值,比如版本 id 為 40，小于活躍數(shù)組最小值  45。這就代表當(dāng)前版本的事務(wù)已提交，當(dāng)前版本對(duì)于當(dāng)前事務(wù)可見。

• 若版本事務(wù) id 大于當(dāng)前活躍事務(wù)數(shù)組的最大值，如版本事務(wù) id 為 100， 大于數(shù)組最大事務(wù) id  90。說明了這個(gè)版本是當(dāng)前事務(wù)創(chuàng)建之后生成，所以這個(gè)版本對(duì)于當(dāng)前事務(wù)不可見。

• 若版本事務(wù) id 是當(dāng)前活躍數(shù)組事務(wù)之一，比如版本事務(wù) id 為 56。代表記錄版本所屬事務(wù)還未提交，所以該版本對(duì)于當(dāng)前事務(wù)不可見。

• 若版本事務(wù) id 不是當(dāng)前活躍數(shù)組事務(wù)之一，但是事務(wù) id 位于活躍數(shù)組最小值與最大值之一，比如如事務(wù) ID  57。代表當(dāng)前記錄事務(wù)已提交，所以該版本對(duì)于當(dāng)前事務(wù)可見。

• 若版本事務(wù) id 為當(dāng)前事務(wù) id，代表該行數(shù)據(jù)是當(dāng)前事務(wù)變更的，當(dāng)然得可見。

4 這個(gè)規(guī)則可能比較繞，結(jié)合上面圖片比較好理解。

以上判斷規(guī)則可能比較抽象，看不懂，沒事，我們?cè)儆么蟀自捊忉屢幌拢?/p>

• 未提交事務(wù)生成的記錄版本，不可見。

• 視圖生成前，已提交事務(wù)生成記錄版本可見。

• 視圖生成后，新事務(wù)生成記錄版本不可見。

• 自身事務(wù)更新永遠(yuǎn)可見。

一致性視圖只會(huì)在 RR 與 RC 下才會(huì)生成，對(duì)于 RR 來說，一致性視圖會(huì)在第一個(gè)查詢語句的時(shí)候生成。而對(duì)于 RC  來說，每個(gè)查詢語句都會(huì)重新生成視圖。

當(dāng)前讀與快照讀

MySQL 使用 MVCC 機(jī)制，可以讀取之前版本數(shù)據(jù)。這些舊版本記錄不會(huì)且也無法再去修改，就像快照一樣。所以我們將這種查詢稱為快照讀。

當(dāng)然并不是所有查詢都是快照讀，select .... for update/ in share mode  這類加鎖查詢只會(huì)查詢當(dāng)前記錄最新版本數(shù)據(jù)。我們將這種查詢稱為當(dāng)前讀。

問題分析

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

這里我們將上面答案再貼過來。

事務(wù)隔離級(jí)別為 RR，t2，t3 時(shí)刻兩個(gè)事務(wù)由于查詢語句，分別建立了一致性視圖。

t4 時(shí)刻，由于事務(wù) 1 使用 select.. for update 為 id=1 這一行上了一把鎖，然后獲取到最新結(jié)果。而 t5  時(shí)刻，由于該行已被上鎖，事務(wù) 2 必須等待事務(wù) 1 釋放鎖才能繼續(xù)執(zhí)行。

t6 時(shí)刻根據(jù)一致性視圖，不能讀取到其他事務(wù)提交的版本，所以數(shù)據(jù)沒變。t8 時(shí)刻余額扣減 100，t9 時(shí)刻提交事務(wù)。

此時(shí)最新版本記錄為 id=1 balance=900。

由于事務(wù) 1 事務(wù)已提交，行鎖被釋放，t5 成功獲取到鎖。由于 t5 是當(dāng)前讀，所以查詢的結(jié)果為最新版本數(shù)據(jù)(1,900)。

重點(diǎn)來了，當(dāng)前這條記錄的最新版本數(shù)據(jù)為 (1,900)，但是最新版本事務(wù) id，卻是事務(wù) 2  創(chuàng)建之后未提交的事務(wù)，位于活躍事務(wù)數(shù)組中。所以最新記錄版本對(duì)于事務(wù) 2 是不可見的。

沒辦法只能根據(jù) undolog 去讀取上一版本記錄 (1,1000) ，這個(gè)版本記錄剛好對(duì)于事務(wù) 2 可見，所以 t11 的記錄為  (1,1000)。

而當(dāng)我們將事務(wù)隔離等級(jí)修改成 RC，每次都會(huì)重新生成一致性視圖。所以 t11 時(shí)刻重新生成了一致性視圖，這時(shí)候事務(wù) 1 已提交，當(dāng)前最新版本的記錄對(duì)于事務(wù)  2 可見，所以 t11 的結(jié)果將會(huì)變?yōu)?(1,900)。

關(guān)于MySQL 中怎么實(shí)現(xiàn)可重復(fù)讀操作就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。