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

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

目錄 "臟讀(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中的「幻讀」了


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

-Advertisement-
Play Games
更多相關文章
  • 一 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 命令,把本此添加過程記錄下 ...
一周排行
    -Advertisement-
    Play Games
  • 概述:這個WPF項目通過XAML繪製汽車動態速度表盤,實現了0-300的速度刻度,包括數字、指針,並通過定時器模擬速度變化,展示了動態效果。詳細實現包括界面設計、刻度繪製、指針角度計算等,通過C#代碼與XAML文件結合完成。 新建 WPF 項目: 在 Visual Studio 中創建一個新的 WP ...
  • 概述:在WPF中使用`WpfAnimatedGif`庫展示GIF動畫,首先確保全裝了該庫。通過XAML設置Image控制項,指定GIF路徑,然後在代碼中使用庫提供的方法實現動畫控制。這簡化了在WPF應用中處理GIF圖的過程,提供了方便的介面來管理動畫播放和暫停。 當使用 WpfAnimatedGif  ...
  • 您是否曾經訪問過一個網站,它需要很長時間載入,最終你敲擊 F5 重新載入頁面。 即使用戶刷新了瀏覽器取消了原始請求,而對於伺服器來說,API也不會知道它正在計算的值將在結束時被丟棄,刷新五次,伺服器將觸發 5 個請求。 為瞭解決這個問題,ASP.NET Core 為 Web 伺服器提供了一種機制,就 ...
  • 本章將和大家分享如何通過 Elasticsearch 實現自動補全查詢功能。 一、自動補全-安裝拼音分詞器 1、自動補全需求說明 當用戶在搜索框輸入字元時,我們應該提示出與該字元有關的搜索項,如圖: 2、使用拼音分詞 要實現根據字母做補全,就必須對文檔按照拼音分詞。在 GitHub 上恰好有 Ela ...
  • using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Windows.Forms; namespace OOP { pub ...
  • 概述:以上內容詳細介紹了在C#中如何從另一個線程更新GUI,包括基礎功能和高級功能。對於WinForms,使用`Control.Invoke`;對於WPF,使用`Dispatcher.Invoke`。高級功能使用`SynchronizationContext`實現線程間通信,確保清晰、可讀性高的代碼 ...
  • Nuget包 Microsoft.Extensions.Telemetry.Abstractions 包含的新的日誌記錄source generator,它支持使用[LogProperties]將整個對象作為State與日誌一起記錄。 我將展示一種方法來控制如何使用[LogProperties]對象 ...
  • 支持.Net/.Net Core/.Net Framework,可以部署在Docker, Windows, Linux, Mac。 常見的ORM技術(比如:Entity Framework,Dapper,SqlSugar,NHibernate,等…),它們不是在做Sql語句的程式化變種,就是在做Sq ...
  • 一、引言 在現代應用程式開發中,尤其是在涉及I/O操作(如網路請求、文件讀寫等)時,非同步編程成為了提高性能和用戶體驗的關鍵技術。C#作為.NET框架下的主流開發語言,提供了強大的非同步編程支持,通過async/await關鍵字,可以讓開發者以同步的方式編寫非同步代碼,極大地簡化了非同步編程的複雜性。本文將 ...
  • 一、引言 在.NET開發中,操作Office文檔(特別是Excel和Word)是一項常見的需求。然而,在伺服器端或無Microsoft Office環境的場景下,直接使用Office Interop可能會面臨挑戰。為瞭解決這個問題,開源庫NPOI應運而生,它提供了無需安裝Office即可創建、讀取和 ...