MySQL事務隔離級別與相關示例(臟讀、不可重覆讀、幻讀)

来源:https://www.cnblogs.com/sxpujs/archive/2020/03/24/12556246.html

目錄 "臟讀(Dirty reads)" "不可重覆讀(Non repeatable reads)" "幻影讀(Phantom reads)" "可重覆讀級別下防止幻讀" "可串列化級別杜絕幻讀" "總結" MySQL8中隔離級別的變數跟之前的版本不一樣,之前是tx_isolation,MySQL8 ...


目錄

MySQL8中隔離級別的變數跟之前的版本不一樣,之前是tx_isolation,MySQL8改成了transaction_isolation。查看當前隔離級別的命令是

mysql> select @@global.transaction_isolation,@@transaction_isolation;

+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

其它參考:MySQL 四種事務隔離級的說明

  • 未提交讀(Read Uncommitted):允許臟讀,也就是可能讀取到其他會話中未提交事務修改的數據
  • 提交讀(Read Committed):只能讀取到已經提交的數據。Oracle等多數資料庫預設都是該級別 (不重覆讀)
  • 可重覆讀(Repeated Read):可重覆讀。在同一個事務內的查詢都是事務開始時刻一致的,InnoDB預設級別。在SQL標準中,該隔離級別消除了不可重覆讀,但是還存在幻象讀
  • 串列讀(Serializable):完全串列化的讀,每次讀都需要獲得表級共用鎖,讀寫相互都會阻塞

以下內容參考了維基百科:事務隔離

創建測試表users並插入測試數據

mysql> CREATE TABLE users (id int(11) NOT NULL, name varchar(20), age int(11), PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO users values (1, 'Joe', 20), (2, 'Jill', 25);

mysql> select * from users;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Joe  |   20 |
|  2 | Jill |   25 |
+----+------+------+

臟讀(Dirty reads)

示例1:隔離級別是未提交讀(READ UNCOMMITTED),導致臟讀(dirty read)。在我們的例子中,事務2修改了一行,但是沒有提交,事務1讀了這個沒有提交的數據。現在如果事務2回滾了剛纔的修改或者做了另外的修改的話,事務1中查到的數據就是不正確的了。在這個例子中,事務2回滾後就沒有id是1,age是21的數據行了。

-- 設置隔離級別為未提交讀
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

             Session A                              Session B

           START TRANSACTION;                       START TRANSACTION;
time
|          /* Query 1 */
|          SELECT age FROM users WHERE id = 1;
|          /* will read 20 */
|                                                   /* Query 2 */
v                                                   UPDATE users SET age = 21 WHERE id = 1;
                                                    /* No commit here */

           /* Query 1 */
           SELECT age FROM users WHERE id = 1;
           /* will read 21 */
                                                    ROLLBACK; /* lock-based DIRTY READ */

不可重覆讀(Non-repeatable reads)

示例2:隔離級別是讀已提交(READ COMMITTED),導致不可重覆讀。在這個例子中,事務2提交成功,因此他對id為1的行的修改就對其他事務可見了。但是事務1在此前已經從這行讀到了另外一個“age”的值。在可串列化(SERIALIZABLE)和可重覆讀的隔離級別,資料庫在第二次SELECT請求的時候應該返回事務2更新之前的值。在提交讀和未提交讀,返回的是更新之後的值,這個現象就是不可重覆讀。

-- 設置隔離級別為提交讀
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

             Session A                              Session B

           START TRANSACTION;                       START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 1;
|          /* will read age=20 */
|                                                   /* Query 2 */
v                                                   UPDATE users SET age = 21 WHERE id = 1;
                                                    COMMIT; /* in multiversion concurrency 
                                                        control, or lock-based READ COMMITTED */

           /* Query 1 */
           SELECT * FROM users WHERE id = 1;
           /* will read age=21 */
           COMMIT; /* lock-based REPEATABLE READ */

有兩種策略可以避免不可重覆讀。一個是要求事務2延遲到事務1提交或者回滾之後再執行。這種方式實現了T1, T2 的串列化調度。串列化調度可以支持可重覆讀。

另一種策略是多版本併發控制。為了得到更好的併發性能,允許事務2先提交。但因為事務1在事務2之前開始,事務1必須在其開始執行時間點的資料庫的快照上面操作。當事務1最終提交時候,資料庫會檢查其結果是否等價於T1, T2串列調度。如果等價,則允許事務1提交,如果不等價,事務1需要回滾並拋出個串列化失敗的錯誤。

使用基於鎖的併發控制,在可重覆讀的隔離級別中,ID=1的行會被鎖住,在事務1提交或回滾前一直阻塞語句2的執行。在提交讀的級別,語句1第二次執行,age已經被修改了。

在多版本併發控制機制下,可序列化(SERIALIZABLE)級別,兩次SELECT語句讀到的數據都是事務1開始的快照,因此返回同樣的數據。但是,如果事務1試圖UPDATE這行數據,事務1會被要求回滾並拋出一個串列化失敗的錯誤。

在提交讀隔離級別,每個語句讀到的是語句執行前的快照,因此讀到更新前後不同的值。在這種級別不會有串列化的錯誤(因為這種級別不要求串列化),事務1也不要求重試。

幻影讀(Phantom reads)

以下內容參考了:mysql 幻讀的詳解、實例及解決辦法

幻讀錯誤的理解:說幻讀是 事務A 執行兩次 select 操作得到不同的數據集,即 select 1 得到 10 條記錄,select 2 得到 11 條記錄。這其實並不是幻讀,這是不可重覆讀的一種,只會在 R-U R-C 級別下出現,而在 mysql 預設的 RR 隔離級別是不會出現的。

幻讀,並不是說兩次讀取獲取的結果集不同,幻讀側重的方面是某一次的 select 操作得到的結果所表徵的數據狀態無法支撐後續的業務操作。更為具體一些:select 某記錄是否存在,不存在,準備插入此記錄,但執行 insert 時發現此記錄已存在,無法插入,此時就發生了幻讀。

-- 設置隔離級別為可重覆讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 3;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (3, 'Woody', 28);
                                                        COMMIT;

           /* Query 3 */
           INSERT INTO users values (3, 'Woody', 28);
           /* ERROR 1062 (23000): Duplicate entry '3' 
              for key 'PRIMARY' */
           
           /* Query 4 */
           SELECT * FROM users WHERE id = 3;
           /* Empty set */
           COMMIT;

會話A :主事務,檢測表中是否有 id 為 3 的記錄,沒有則插入,這是我們期望的正常業務邏輯。

會話B :干擾事務,目的在於擾亂 會話A 的正常的事務執行。

在 RR 隔離級別下,Query 1、Query 2 是會正常執行的,Query 3 則會報錯主鍵衝突,對於 會話A 的業務來說是執行失敗的,這裡 會話A 就是發生了幻讀,因為 會話A 在 Query 1 中讀取的數據狀態並不能支撐後續的業務操作,會話A:“見鬼了,我剛纔讀到的結果應該可以支持我這樣操作才對啊,為什麼現在不可以”。會話A 不敢相信的又執行了 Query 4,發現和 Query 1 讀取的結果是一樣的(RR下的 MMVC機制)。此時,幻讀無疑已經發生,T1 無論讀取多少次,都查不到 id = 3 的記錄,但它的確無法插入這條他通過讀取來認定不存在的記錄(此數據已被會話B插入),對於 會話A 來說,它幻讀了。

其實 RR 也是可以避免幻讀的,通過對 select 操作手動加 行X鎖(SELECT ... FOR UPDATE 這也正是 SERIALIZABLE 隔離級別下會隱式為你做的事情),同時還需要知道,即便當前記錄不存在,比如 id = 3 是不存在的,當前事務也會獲得一把記錄鎖(因為InnoDB的行鎖鎖定的是索引,故記錄實體存在與否沒關係,存在就加 行X鎖,不存在就加 next-key lock間隙X鎖),其他事務則無法插入此索引的記錄,故杜絕了幻讀。

在 SERIALIZABLE 隔離級別下,step1 執行時是會隱式的添加 行(X)鎖 / gap(X)鎖的,從而 Query2 會被阻塞,Query3 會正常執行,待 T1 提交後,T2 才能繼續執行(主鍵衝突執行失敗),對於 T1 來說業務是正確的,成功的阻塞扼殺了擾亂業務的T2,對於T1來說他前期讀取的結果是可以支撐其後續業務的。

所以 mysql 的幻讀並非什麼讀取兩次返回結果集不同,而是事務在插入事先檢測不存在的記錄時,驚奇的發現這些數據已經存在了,之前的檢測讀獲取到的數據如同鬼影一般。

這裡要靈活的理解讀取的意思,第一次select是讀取,第二次的 insert 其實也屬於隱式的讀取,只不過是在 mysql 的機制中讀取的,插入數據也是要先讀取一下有沒有主鍵衝突才能決定是否執行插入。

不可重覆讀側重表達 讀-讀,幻讀則是說 讀-寫,用寫來證實讀的是鬼影。

可重覆讀級別下防止幻讀

RR級別下只要對 SELECT 操作也手動加行(X)鎖即可類似 SERIALIZABLE 級別(它會對 SELECT 隱式加鎖),即大家熟知的:

# 這裡需要用 X鎖, 用 FOR SHARE 拿到 S鎖 後我們沒辦法做 寫操作
SELECT `id` FROM `users` WHERE `id` = 3 FOR UPDATE;

如果 id = 3 的記錄存在則會被加行(X)鎖,如果不存在,則會加 next-lock key / gap 鎖(範圍行鎖),即記錄存在與否,mysql 都會對記錄應該對應的索引加鎖,其他事務是無法再獲得做操作的。

這裡我們就展示下 id = 3 的記錄不存在的場景,FOR UPDATE 也會對此 “記錄” 加鎖,要明白,InnoDB 的行鎖(gap鎖是範圍行鎖,一樣的)鎖定的是記錄所對應的索引,且聚簇索引同記錄是直接關係在一起的。

-- 設置隔離級別為可重覆讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 3 FOR UPDATE;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (3, 'Woody', 28);
                                                        /* 被阻塞,ERROR 1205 (HY000): Lock wait timeout exceeded; 
                                                           try restarting transaction */

           /* Query 3 */
           INSERT INTO users values (3, 'Woody', 28);
           /* Query OK, 1 row affected */
           
           COMMIT;
           /* Query OK, 0 rows affected */

可串列化級別杜絕幻讀

在此級別下,我們便不需要對 SELECT 操作顯式加鎖,InnoDB會自動加鎖,事務安全,但性能很低。

-- 設置隔離級別為可串列化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          select * from users where id = 4;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (4, 'Bill', 29);
                                                        /* 被阻塞,ERROR 1205 (HY000): Lock wait 
                                                           timeout exceeded; try restarting transaction */

           /* Query 3 */
           INSERT INTO users values (4, 'Bill', 29);
           /* Query OK, 1 row affected */
           
           COMMIT;
           /* Query OK, 0 rows affected */

step1: 會話A 查詢 id = 4 的記錄,InnoDB 會隱式的對齊加 X鎖
step2: 會話B 插入 id = 4 的記錄,被阻塞
step3: 會話A 插入 id = 4 的記錄,成功執行(會話B 依然被阻塞中)
step4: 會話A 成功提交(會話B 此時喚醒但主鍵衝突執行錯誤)
會話A事務符合業務需求成功執行,會話B干擾會話A失敗。

總結

RR 級別作為 mysql 事務預設隔離級別,是事務安全與性能的折中,可能也符合二八定律(20%的事務存在幻讀的可能,80%的事務沒有幻讀的風險),我們在正確認識幻讀後,便可以根據場景靈活的防止幻讀的發生。

SERIALIZABLE 級別則是悲觀的認為幻讀時刻都會發生,故會自動的隱式的對事務所需資源加排它鎖,其他事務訪問此資源會被阻塞等待,故事務是安全的,但需要認真考慮性能。

InnoDB的行鎖鎖定的是索引,而不是記錄本身,這一點也需要有清晰的認識,故某索引相同的記錄都會被加鎖,會造成索引競爭,這就需要我們嚴格設計業務sql,儘可能的使用主鍵或唯一索引對記錄加鎖。索引映射的記錄如果存在,加行鎖,如果不存在,則會加 next-key lock / gap 鎖 / 間隙鎖,故InnoDB可以實現事務對某記錄的預先占用,如果記錄存在,它就是本事務的,如果記錄不存在,那它也將是本是無的,只要本是無還在,其他事務就別想占有它。

另外可以參考:以後別再說你不懂MySQL中的「幻讀」了


您的分享是我們最大的動力!

更多相關文章
  • 一 CNM網路模型 1.1 網路模型 生產環境中,跨主機容器間的網路互通已經成為基本要求,更高的要求包括容器固定IP地址、一個容器多個IP地址、多個子網隔離、ACL控制策略、與SDN集成等。目前主流的容器網路模型主要有Docker公司提出的Container Network Model(CNM)模型 ...
  • 不知道各位第一次接觸linux系統是什麼時候,我是高中的時候在一篇技術推文中接觸到linux系統的,不同於windows系統的顯示界面和命令操作讓我很是感興趣。於是在當時還未熟悉電腦操作的情況下,手忙腳亂地亂鼓弄了一番。當時記得是準備在筆記本上裝雙系統的,結果不知道為什麼(可能是當時分區的時候不懂, ...
  • MySQL整理 這幾天整理了MySQL的知識點,還有一部分需要在梳理一下,圖上先寫這麼多吧。 未完待續…… ...
  • 從監控工具DPA中發現一個資料庫(SQL Server 2008 R2)的等待事件突然彪增,下鑽分析發現資料庫執行存儲過程sp_MailItemResultSets時,引起了非常嚴重的等待(High Wait),而主要的等待事件為PREEMPTIVE_OS_WAITFORSINGLEOBJEC。 如... ...
  • 2.3 NiFi Processor應用介紹對於NiFi的使用者來說,如果想要創建一個高效的數據流,那麼就需要瞭解什麼樣的單元處理器才最適合這個數據流。NiFi擁有大量的可以用於各種業務場景的單元處理器可供使用者挑選和使用,這些單元處理器主要提供例如系統之間數據的傳輸,數據的路由,數據的轉換、處理、... ...
  • 面試階段大家基本都會問一些mysql的題,具體的高深理論以後再慢慢補充,但是刷題是不可避免的,下麵直接上貨 創建/刪除表和索引系列 創建表 sql CREATE TABLE if not exists ( int(11) NOT NULL AUTO_INCREMENT, date DEFAULT N ...
  • 首先,SQL語句應該考慮哪些安全性? 第一,防止SQL註入,對特殊字元進行過濾、轉義或者使用預編譯的SQL語句綁定變數。 第二,當SQL語句運行出錯時,不要把資料庫返回的錯誤信息全部顯示給用戶,以防止泄露伺服器和資料庫相關信息。 其次,什麼叫做SQL註入呢,如何防止呢? 舉個例子: 你後臺寫的Jav ...
  • 今天上課要用管理員系統驗證登錄 Oracle,提示我許可權不足,上網搜了下,問題應該是當前用戶未在 ora_dba 組下,得勒,把它添加進去不就行了 找了半天,坑爹地發現 win10 家庭版它就沒有 本地用戶和組 的圖形界面,這氣人玩意兒,再去找管理本地用戶和組的 Dos 命令,把本此添加過程記錄下 ...
一周排行
  • 前幾天發佈了 "抄抄《CSS 故障藝術》的動畫" 這篇文章,在這篇文章里介紹瞭如何使用Win2D繪製文字然後配合BlendEffect製作故障藝術的動畫。本來打算就這樣收手不玩這個動畫了,但後來又發現性能不符合理想。明明只是做做Resize動畫和用BlendEffect混合,為什麼性能會這麼差呢? ...
  • 控制條控制項: progressBar 不能按照你程式的進程自動變化,需認為計算,調整變化量 private void progressBar1_Click(object sender, EventArgs e) { this.progressBar1.Maximum = 100;//設置進度條最大長 ...
  • 首先創建一個asp.net core web應用程式 第二步 目前官方預置了7種模板項目供我們選擇。從中我們可以看出,既有我們熟悉的MVC、WebAPI,又新添加了Razor Page,以及結合比較流行的Angular、React前端框架的模板項目。 空項目模板 Program.cs using S ...
  • 對閉包的理解 1.對於成員變數和局部變數:成員變數就是方法外部,類的內部定義的變數;局部變數就是方法或語句塊內部定義的變數。局部變數必須初始化。 形式參數是局部變數,局部變數的數據存在於棧記憶體中。棧記憶體中的局部變數隨著方法的消失而消失。成員變數存儲在堆中的對象裡面,由垃圾回收器負責回收。 成員變數它 ...
  • Xamarin.Forms讀取並展示Android和iOS通訊錄 TerminalMACS客戶端 本文同步更新地址: https://dotnet9.com/11520.html https://terminalmacs.com/861.html 閱讀導航: 一、功能說明 二、代碼實現 三、源碼獲取 ...
  • 做下對文件複製操作相關的筆記: /// <summary> /// 文件幫助類 /// </summary> public class FileHelper { /// <summary> /// 複製一個目錄下所有文件到一個新目錄下 /// </summary> /// <param name=" ...
  • 前言 有一個東西叫做鴨子類型,所謂鴨子類型就是,只要一個東西表現得像鴨子那麼就能推出這玩意就是鴨子。 C 裡面其實也暗藏了很多類似鴨子類型的東西,但是很多開發者並不知道,因此也就沒法好好利用這些東西,那麼今天我細數一下這些藏在編譯器中的細節。 不是只有 和 才能 在 C 中編寫非同步代碼的時候,我們經 ...
  • [toc] 1.應用背景 底端設備有大量網路報文(位元組數組):心跳報文,數據採集報文,告警報文上報。需要有對應的報文結構去解析這些位元組流數據。 2.結構體解析 由此,我第一點就想到了用結構體去解析。原因有以下兩點: 2.1.結構體存在棧中 類屬於引用類型,存在堆中;結構體屬於值類型,存在棧中,在一個 ...
  • 《深入淺出 C#》 (第3版) [作者] (美) Andrew Stellman (美) Jennifer Greene[譯者] (中) 徐陽 丁小峰 等譯[出版] 中國電力出版社[版次] 2016年08月 第1版[印次] 2018年04月 第4次 印刷[定價] 148.00元 【引子】 要學習編程 ...
  • 記錄使用對象初始值設定項初始化對象。 using System; using System.Collections.Generic; namespace ConsoleApp2 { class Program { static void Main(string[] args) { // 使用構造函數 ...
x