mysql中RR與幻讀的問題怎么解決

這篇“mysql中RR與幻讀的問題怎么解決”文章的知識(shí)點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價(jià)值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“mysql中RR與幻讀的問題怎么解決”文章吧。

一、前言

• MVCC 原理

• 實(shí)驗(yàn)：RR 與 幻讀

• 案例：死鎖

先來回顧下 MySQL中 InnoDB 支持的四種事務(wù)隔離 和 并發(fā)事務(wù)所帶來的一些問題：

• 讀未提交：能讀到一個(gè)事務(wù)的中間過程，違背了 ACID 特性，存在臟讀的問題，基本不會(huì)用到。

• 讀提交：表示如果其他事務(wù)已經(jīng)提交，那么就可以看到。在生產(chǎn)環(huán)境中用的并不多。

• 可重復(fù)讀：默認(rèn)級(jí)別，使用最多的一種。其特點(diǎn)是有 Gap 鎖（間隙鎖）。

• 可串行化：所有的實(shí)現(xiàn)都是通過鎖來實(shí)現(xiàn)的。

并發(fā)事務(wù)處理也會(huì)帶來一些問題：臟讀、不可重復(fù)讀、幻讀

• 臟讀：一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)。

• 不可重復(fù)讀：一個(gè)事務(wù)按相同查詢條件前后兩次讀取，讀出的數(shù)據(jù)不一致（修改、刪除）。

• 幻讀：一個(gè)事務(wù)內(nèi)按相同的查詢條件重新查詢數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)。

本文脈絡(luò)梳理： RR 為了更快并發(fā)，引入 MVCC，但有幻讀的可能，為解決幻讀，引入 Gap 鎖，Gap 可能造成死鎖。

二、MVCC 原理

MVCC（多版本控制）： 指數(shù)據(jù)庫(kù)中為了實(shí)現(xiàn)高并發(fā)的數(shù)據(jù)訪問，對(duì)數(shù)據(jù)進(jìn)行多版本處理，并通過事務(wù)的可見性來保證事務(wù)能看到自己應(yīng)該看到的數(shù)據(jù)版本。

MVCC 最大的好處是讀不加鎖，讀寫不沖突。

在 OLTP (On-Line Transaction Processing)應(yīng)用中，讀寫不沖突很重要，幾乎所有 RDBMS 都支持 MVCC。

注意：MVCC 只在 讀提交RC 和 可重復(fù)讀RR 兩種隔離級(jí)別下工作。

注意：MVCC 只在 讀提交RC 和 可重復(fù)讀RR 兩種隔離級(jí)別下工作。

注意：MVCC 只在 讀提交RC 和 可重復(fù)讀RR 兩種隔離級(jí)別下工作。

（1）MVCC 多版本實(shí)現(xiàn)

MySQL 實(shí)現(xiàn) MVCC 機(jī)制的時(shí)候，是基于 undo log 多版本鏈條 + ReadView 機(jī)制。

• undo log 多版本鏈： 每一次對(duì)數(shù)據(jù)庫(kù)的修改，都會(huì)在 undo log 日志中記錄當(dāng)前修改記錄的事務(wù)號(hào)及修改前數(shù)據(jù)狀態(tài)的存儲(chǔ)地址（即 ROLL_PTR），以便在必要的時(shí)候可以回滾到老的數(shù)據(jù)版本。

• ReadView 機(jī)制： 在多版鏈的基礎(chǔ)上，控制事務(wù)讀取的可見性。（主要區(qū)別是：RC 和 RR）

這里不著重探究原理，但要有大概的概念：undo log 多版本鏈 和 ReadView 機(jī)制。

針對(duì) undo log 多版本鏈，舉個(gè)栗子：

• 一個(gè)讀事務(wù)查詢到當(dāng)前記錄，而最新的事務(wù)還未提交。

• 根據(jù)原子性，讀事務(wù)看不到最新數(shù)據(jù)，但可以去回滾段中找到老版本的數(shù)據(jù)，這樣就生成了多個(gè)版本。

針對(duì) ReadView 機(jī)制： 基于 undo log 多版本鏈實(shí)現(xiàn)，不同事務(wù)隔離有不同處理 ：

• RC 級(jí)別的事務(wù)： 可見性比較高，它可以看到已提交的事務(wù)的所有修改。

• RR 級(jí)別的事務(wù)： 一個(gè)讀事務(wù)中，不管其他事務(wù)對(duì)這些數(shù)據(jù)做了什么修改，以及是否提交，只要自己不提交，查詢的數(shù)據(jù)結(jié)果就不會(huì)變。

這是如何做到的呢？

RC讀提交： 每一條讀操作語句都會(huì)獲取一次 ReadView，每次更新之后，都會(huì)獲取數(shù)據(jù)庫(kù)中最新的事務(wù)提交狀態(tài)，也就可以看到最新提交的事務(wù)了，即每條語句執(zhí)行都會(huì)更新其可見性視圖。

RR可重復(fù)讀： 開啟事務(wù)時(shí)不會(huì)獲取 ReadView，只有發(fā)起第一個(gè)快照讀時(shí)才會(huì)獲取 ReadView。

如果使用當(dāng)前讀，都會(huì)獲取新的 ReadView，也能看到更新的數(shù)據(jù)。

（2）快照讀與當(dāng)前讀

在 MVCC 并發(fā)控制中，讀操作 可以分為兩類：

快照讀：讀取的是記錄的可見版本（有可能是歷史版本）， 不用加鎖 。

操作：簡(jiǎn)單的 SELECT 操作。

當(dāng)前讀：讀取的是記錄的最新版本，并且當(dāng)前讀返回的記錄，都會(huì)加鎖，保證其他事務(wù)不會(huì)再并發(fā)修改這條記錄。

操作：特殊讀操作、新增/更新/刪除操作。

-- 對(duì)應(yīng) SQL 如下：
-- 1. 特殊讀操作
SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE  -- 共享鎖
-- 2. 新增：INSERT 
-- 3. 更新：UPDATE
-- 4. 刪除：DELETE

結(jié)合 ReadView 機(jī)制來區(qū)分：快照讀 和 當(dāng)前讀：

快照讀： 在一個(gè)事務(wù)里，只有發(fā)起第一個(gè)快照讀時(shí)才會(huì)獲取 ReadView，之后的讀操作不會(huì)再獲取。

當(dāng)前讀： 每次讀操作都會(huì)獲取 ReadView。

三、實(shí)驗(yàn)：RR 與幻讀

面試題：在 RR 事務(wù)隔離級(jí)別下，事務(wù)A查詢一條數(shù)據(jù)，事務(wù)B新增一條數(shù)據(jù)，事務(wù)A能看到事務(wù)B的數(shù)據(jù)嘛？

這個(gè)問題比較模糊，但大致考察點(diǎn)我們知曉是 RR 與 幻讀，可以將問題分為兩類：

什么情況下，RR 產(chǎn)生幻讀？（能看到數(shù)據(jù)）

答案：當(dāng)前讀（SELECT..FOR UDPDATE、SELECT ... LOCK IN SHARE MODE）

什么情況下，RR 解決幻讀？（不能看到數(shù)據(jù)）

答案：加鎖、快照讀

注意： 不可重復(fù)讀 重點(diǎn)在于 UPDATA 和 DELETE，而幻讀的重點(diǎn)在于 INSERT。

它們之間最大的區(qū)別：是如何通過鎖機(jī)制來解決它們產(chǎn)生的問題。

這里說的鎖只是使用悲觀鎖機(jī)制。

再來回顧下：幻讀

-- 舉個(gè)栗子：有這樣一個(gè)查詢 SQL
SELECT * FROM user WHERE id < 10;

在同一個(gè)事務(wù)下，T1時(shí)刻查詢出來 4 條數(shù)據(jù)，T2時(shí)刻查詢出來 8 條數(shù)據(jù)。這就產(chǎn)生了幻讀。

在同一個(gè)事務(wù)下，T1時(shí)刻查詢出來 8 條數(shù)據(jù)，T2時(shí)刻查詢出來 4 條數(shù)據(jù)。這就產(chǎn)生了幻讀。

實(shí)驗(yàn)準(zhǔn)備如下： 動(dòng)手實(shí)踐起來

show variables like 'transaction_isolation'; -- 事務(wù)隔離級(jí)別 RR
select version();                            -- 版本 8.0.16
show variables like '%storage_engine%';      -- 引擎 InnoDB
-- 1. 手動(dòng)開啟事務(wù)提交
begin;  -- 開始事務(wù)
commit; -- 提交事務(wù)
-- 2. 創(chuàng)建表
CREATE TABLE IF NOT EXISTS `student` (
`id` INT NOT NULL COMMENT '主鍵 id',
`name` VARCHAR(50) NOT NULL COMMENT '名字',
`age` TINYINT NOT NULL COMMENT '年齡',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '學(xué)生表';
-- 3. 新增數(shù)據(jù)用于實(shí)驗(yàn)
INSERT INTO student (id, name, age) VALUES (5, 'kunkun', 14);
INSERT INTO student (id, name, age) VALUES (30, 'ikun', 18);

（1）RR 產(chǎn)生幻讀

實(shí)驗(yàn)如下： 測(cè)試當(dāng)前讀

實(shí)驗(yàn)一：先 SELECT，再 SELECT ... FOR UPDATE

實(shí)驗(yàn)二：先 SELECT，再 UPDATE （不會(huì)產(chǎn)生幻讀）

實(shí)驗(yàn)一：先 SELECT，再 SELECT ... FOR UPDATE

-- 事務(wù)A：
BEGIN;
SELECT * FROM student WHERE id < 30;
SELECT * FROM student WHERE id < 30 FOR UPDATE;  -- 等待事務(wù)B commit 后再執(zhí)行
-- SELECT * FROM student WHERE id < 30 LOCK IN SHARE MODE;
COMMIT;
-- 事務(wù)B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;

發(fā)生情況如下圖所示：

實(shí)驗(yàn)記錄如下圖所示：

現(xiàn)象結(jié)論： 當(dāng)使用當(dāng)前讀（SELECT ... FOR UPDATE）會(huì)產(chǎn)生幻讀。

同樣使用 SELECT ... LOCK IN SHARE MODE; 會(huì)產(chǎn)生幻讀。

實(shí)驗(yàn)二：先 SELECT，再 UPDATE

-- 事務(wù)A：
BEGIN;
SELECT * FROM student WHERE id < 30;
UPDATE student SET name = 'zhiyin' WHERE id = 5;  -- 等待事務(wù)B commit 后再執(zhí)行
SELECT * FROM student WHERE id < 30;
COMMIT;
-- 事務(wù)B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;

發(fā)生情況如下圖所示：

實(shí)驗(yàn)記錄如下圖所示：

現(xiàn)象結(jié)論： 當(dāng)前讀（UPDATE）不會(huì)產(chǎn)生幻讀。同樣 INSERT / DELETE 均不會(huì)。

（2）RR 解決幻讀

實(shí)驗(yàn)如下：

• 實(shí)驗(yàn)一：快照讀

• 實(shí)驗(yàn)二：加鎖（更新不存在的記錄）

• 實(shí)驗(yàn)三：加鎖（SELECT ... FOR UPDATE）

實(shí)驗(yàn)一：快照讀，普通 SELECT

-- 事務(wù)A：
BEGIN;
SELECT * FROM student;
SELECT * FROM student;  -- 等待事務(wù)B commit 后再執(zhí)行
COMMIT;
-- 事務(wù)B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;

發(fā)生情況如下圖所示：

實(shí)驗(yàn)記錄如下圖所示：

現(xiàn)象結(jié)論： 在 RR 事務(wù)隔離級(jí)別下，只有快照讀（SELECT）不會(huì)出現(xiàn)幻讀。沒有當(dāng)前讀。

實(shí)驗(yàn)二：加鎖 ，(更新不存在的記錄)

在 RR 隔離級(jí)別下，事務(wù) A 使用 UPDATE 加鎖，事務(wù) B 無法在這之間插入新數(shù)據(jù)，這樣事務(wù) A在 UPDATE 前后讀的數(shù)據(jù)保持一致，避免了幻讀。

-- 事務(wù)A：
BEGIN;
SELECT * FROM student;
UPDATE student SET name = 'wulikunkun' WHERE id = 18; -- 記錄不存在，產(chǎn)生間隙鎖 (5, 30)。
COMMIT;
-- 事務(wù)B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (10, 'zhiyin', 16); -- 需要等待事務(wù)A結(jié)束。
COMMIT;
-- 事務(wù)C：
BEGIN;
INSERT INTO student (id, name, age) VALUES (40, 'zhiyin你太美', 32);
COMMIT;
-- 查詢數(shù)據(jù)庫(kù)中當(dāng)前有哪些鎖
SELECT INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

發(fā)生情況如下圖所示：

實(shí)驗(yàn)記錄如下圖所示：

現(xiàn)象結(jié)論：

一開始先加 臨鍵鎖Next-key lock，鎖范圍為 (5,30]。

因?yàn)槭俏ㄒ凰饕腋碌挠涗洸淮嬖冢R鍵鎖退化成 間隙鎖Gap，最終鎖范圍為 (5,30)。其余的記錄不受影響。

實(shí)驗(yàn)三：加鎖（SELECT ... FOR UPDATE）

-- 事務(wù)A：
BEGIN;
SELECT * FROM student;
SELECT * FROM student WHERE id < 5 FOR UPDATE;
COMMIT;
-- 事務(wù)B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (4, 'zhiyin', 4); -- 需要等待事務(wù)A結(jié)束。
COMMIT;
-- 事務(wù)C：
BEGIN;
INSERT INTO student (id, name, age) VALUES (5, 'zhiyin你太美', 32); -- 插入成功
COMMIT;
-- 查詢數(shù)據(jù)庫(kù)中當(dāng)前有哪些鎖
SELECT INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

發(fā)生情況如下圖所示：

實(shí)驗(yàn)記錄如下圖所示：

現(xiàn)象結(jié)論：

先加 臨鍵鎖Next-key lock，鎖范圍為 (-∞,5]。

所以，id < 5 和 id = 5 的數(shù)據(jù)都插入不進(jìn)去。

拓展：Gap 鎖（間隙鎖）

根據(jù) 官方文檔 可知：

• 鎖是加在索引上的。

• 記錄鎖： 行鎖，只會(huì)鎖定一條記錄。

• 間隙鎖 ：是在索引記錄之間的間隙上的鎖，區(qū)間為前開后開 (,)。

• 臨鍵鎖（Next-Key Lock）： 由 記錄鎖 和 間隙鎖Gap 組合起來。

• 加鎖的基本單位是 臨鍵鎖，其加鎖區(qū)間為前開后閉 (,]。

• 索引上的等值查詢，給唯一索引加鎖的時(shí)候，如果滿足條件，臨鍵鎖 退化為 行鎖。

• 索引上的等值查詢，給唯一索引加鎖的時(shí)候，如果不滿足條件，臨鍵鎖 退化為 間隙鎖。注意，非等值查詢是不會(huì)優(yōu)化的。

以上就是關(guān)于“mysql中RR與幻讀的問題怎么解決”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對(duì)大家有幫助，若想了解更多相關(guān)的知識(shí)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。