SQLSERVER 的四個事務隔離級別到底怎麼理解?

来源:https://www.cnblogs.com/huangxincheng/archive/2023/02/02/17086865.html
-Advertisement-
Play Games

一:背景 1. 講故事 在有關SQLSERVER的各種參考資料中,經常會看到如下四種事務隔離級別。 READ UNCOMMITTED READ COMMITTED SERIALIZABLE REPEATABLE READ 隨之而來的是大量的文字解釋,還會附帶各種 臟讀, 幻讀, 不可重覆讀 常常會把 ...


一:背景

1. 講故事

在有關SQLSERVER的各種參考資料中,經常會看到如下四種事務隔離級別。

  • READ UNCOMMITTED
  • READ COMMITTED
  • SERIALIZABLE
  • REPEATABLE READ

隨之而來的是大量的文字解釋,還會附帶各種 臟讀, 幻讀, 不可重覆讀 常常會把初學者弄得暈頭轉向,其實事務的本質就是隔離,落地就需要鎖機制,理解這四種隔離方式的花式加鎖,應該就可以入門了,那如何可視化的觀察 過程呢?這裡藉助 SQL Profile 工具。

二:四種事務隔離方式

1. 測試數據準備

還是用上一篇創建的 post 表,腳本如下:


CREATE TABLE post(id INT IDENTITY,content char(4000))
GO

INSERT INTO dbo.post VALUES('aaa')
INSERT INTO dbo.post VALUES('bbb')
INSERT INTO dbo.post VALUES('ccc');
INSERT INTO dbo.post VALUES('ddd');
INSERT INTO dbo.post VALUES('eee');
INSERT INTO dbo.post VALUES('fff');

有了測試數據之後,我們按照隔離級別 高 -> 低 的順序來觀察吧。

2. SERIALIZABLE 事務

事務串列化 其實很好理解,如果要在 C# 中找對應那就是 ReaderWriterLock,讀寫事務是完全排斥的,接下來把 SQLSERVER 的隔離級別調整為 SERIALIZABLE


SET TRAN ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRAN 
SELECT * FROM dbo.post WHERE id=3
COMMIT

打開 profile,選擇 lock:Acquired, lock:Released,SQL:StmtStarting 選項,開啟觀察。

從圖中可以清楚的看到,SQLSERVER 直接對 post 附加了 S 鎖,在 COMMIT 之後才真正的釋放,在 S 鎖期間, Insert 和 Update 引發的 X 鎖是進不來的,所以就會存在相互阻塞的情況,也許這就是串列化的由來吧。

sqlserver 是一個支持多用戶併發的資料庫程式,如果鎖粒度這麼粗,必定給併發帶來非常大的負面影響,不過文章開頭的那三個指標 臟讀, 幻讀, 不可重覆讀 肯定都是不會出現的。

2. REPEATABLE READ 事務

什麼叫 可重覆讀 呢?簡而言之就是同一個 select 查詢執行二次,不會出現記錄修改的情況,在真實場景中兩次 select 查詢期間,可能會有其他事務修改了記錄,如果當前是 REPEATABLE READ 模式,這是被禁止的,接下來的問題是如何落地實現呢?我們來看看 SQLSERVER 是如何做到的,參考sql 如下:


SET TRAN ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRAN 
SELECT * FROM dbo.post WHERE id=3
COMMIT

這個圖可能有些朋友看不懂,我稍微解釋一下吧,資料庫由數據頁Page組成,數據頁由記錄RID 組成,有了這個基礎就好理解了, SQLSERVER 會在事務期間把 1:489:0 也就是 id=3 這個記錄全程附加 S 鎖,直到事務提交才釋放 S 鎖,在事務期間任何對它修改的 X 鎖都無法對其變更,從而實現事務期間的 可重覆讀 功能,如果大家不明白可以再琢磨琢磨。

這裡有一個細節需要大家註意一下,可重覆讀 的場景下會出現 幻讀 的情況,幻讀就是兩次查詢出的結果集可能會不一樣,比如第一次是 3 條記錄,第二次變成了 5 條記錄,為了方便理解我來簡單演示一下。

  • 會話1

SET TRAN ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRAN 
SELECT * FROM dbo.post WHERE id >3
WAITFOR DELAY '00:00:05'
SELECT * FROM dbo.post WHERE id >3
COMMIT

  • 會話2

會話1 執行的 5s 期間執行 會話2 語句。


BEGIN TRAN 
INSERT INTO dbo.post(content) VALUES ('gggggg')
COMMIT

稍等片刻之後,會發現多了一個 記錄7 ,截圖如下:

3. READ COMMITTED

提交讀 是目前 SQLSERVER 預設的隔離級別,它是以不會出現 臟讀 為唯一目標,何為臟讀,簡而言之就是讀取到了別的事務未提交的修改數據,這個數據有可能會被其他事務在後續回滾掉,如果真的被其他事務 回滾 了,那你讀到了這樣的數據就是 錯誤 的數據,可能會給你的系統帶來非常隱蔽的 bug,為了說明這個現象,我們用兩個會話來測試一下幫助大家理解。

  • 會話1

在這個會話中,將 id=3 的記錄修改成 zzzzz


BEGIN TRAN 
UPDATE dbo.post SET content='zzzzz' WHERE id=3
WAITFOR DELAY '00:00:05'
ROLLBACK

  • 會話2

這個會話中,重覆執行sql查詢。


BEGIN TRAN 
SELECT * FROM dbo.post WITH(NOLOCK) WHERE id =3   -- 臟讀啦
WAITFOR DELAY '00:00:05'
SELECT * FROM dbo.post WITH(NOLOCK) WHERE id =3   -- 正確的數據
COMMIT

為了實現臟讀這裡加了 nolock 關鍵詞,從圖中明顯的看到,獲取的 zzzzz 數據是錯誤的,在一些和錢打交道的系統中是被嚴厲禁止的。

有了這些基礎再理解 可提交讀 可能會容易些,是不是很好奇 SQLSERVER 是如何實現的呢? 參考 sql 如下:


SET TRAN ISOLATION LEVEL READ COMMITTED
GO

BEGIN TRAN 
SELECT * FROM dbo.post  WHERE id =3  
COMMIT

從加鎖流程看,SQLSERVER 會逐一掃描數據頁附加 IS 鎖,掃完馬上就釋放,不像前面那樣保持到 COMMIT 之後,如果找到記錄所在的 Page 時,會對下麵的所有記錄附加 S 鎖,這個時候 X 鎖就進不來了,這就是它的實現原理,大家可以把剛纔的 臟讀 的sql中的 nolock 去掉試試看,兩次讀取結果都是一樣的。

4. READ UNCOMMITTED

本質上來說 READ UNCOMMITTEDnolock 的效果是一樣的,會引發臟讀現象,主要是因為 READ UNCOMMITTED 根本就不會對錶記錄使用任何鎖,參考sql如下:


SET TRAN ISOLATION LEVEL READ UNCOMMITTED
GO

BEGIN TRAN 
SELECT * FROM dbo.post  WHERE id =3  
COMMIT

接下來觀察 sqlprofile 的輸出。

可以看到 READ UNCOMMITTED 只會對堆表結構這種架構附加鎖,不會對錶中記錄附加任何鎖,也就會引發 臟讀 現象。

三:總結

其實 SQLSERVER 還有帶版本的 SNAPSHOT 隔離級別,在真實場景中往往會給 TempDB 造成很大的壓力,這裡就不介紹了。

相信通過 Profile 觀察到的加鎖動態過程,會讓大家有更深入的理解。


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

-Advertisement-
Play Games
更多相關文章
  • 索引(index)是幫助MySQL高效獲取數據的數據結構(有序)。在數據之外,資料庫系統還維護著滿足 特定查找演算法的數據結構,這些數據結構以某種方式引用(指向)數據, 這樣就可以在這些數據結構 上實現高級查找演算法,這種數據結構就是索引。 優缺點: 優點: 提高數據檢索效率,降低資料庫的IO成本 通過 ...
  • 簡介 在文章《GraalVM和Spring Native嘗鮮,一步步讓Springboot啟動飛起來,66ms完成啟動》中,我們介紹瞭如何使用Spring Native和buildtools插件,打包出本地鏡像,也打包成Docker鏡像。本文探索一下,如果不通過這個插件來生成鏡像。這樣我們可以控制更 ...
  • 記錄一下Winform程式打包過程 參考文章:VS2017 WinFrom打包設置與教程 下載 Visual Studio Installer 拓展插件 從VS2017開始VS已預設不再集成Installer拓展,所以需要手動下載安裝。 可以在 工具 - 插件和更新 裡面的插件商店裡面搜索安裝。 制 ...
  • 前言 本文寫給想學C#的朋友,目的是以較快的速度入門 C#好學嗎? 對於這個問題,我以前的回答是:好學!但仔細想想,不是這麼回事,對於新手來說,C#沒有那麼好學。 如果你要入門Java,那學Java Web就行了,但是C#方向比較多,你是學控制台程式、WebAPI、ASP.NET、Winform還是 ...
  • 記錄一下過程. Arm Mbed 應該屬於Arm的機構或者是Arm資助的機構. 常用的 DAPLink 基本上都是從這個項目派生的. 倉庫主要是使用 Keil, 對 GCC 的支持是 2020 年才正式合併進來的. Ubuntu 下使用 GCC Arm 編譯 ...
  • ##一、進入系統引導界面進行配置 ###引導項說明: 安裝centos7系統(*) 測試光碟鏡像並安裝系統 排錯模式(修複系統 重置系統密碼) 補充:centos7系統網卡名稱 預設系統的網卡名稱 eth0 eth1 --centos6 預設系統的網卡名稱 ens33 ens34 --centos7 ...
  • 本教程說明如何在當Windows系統無法正常啟動時,採取重建活動分區的方式來嘗試修複,目的在於不使用第三方軟體和不重裝系統的前提下對系統啟動問題進行最小代價修複。 該教程來源為windows-10-bootrec-fixboot-access-is-denied,本文僅對其稍作修改。 如果系統啟動後 ...
  • 一、資源下載 Keil5下載鏈接: https://www.keil.com/download/product/ STM32 標準庫晶元包下載鏈接: https://www.keil.com/dd2/pack/ JDK下載鏈接: https://www.oracle.com/java/technol ...
一周排行
    -Advertisement-
    Play Games
  • 下麵是一個標準的IDistributedCache用例: public class SomeService(IDistributedCache cache) { public async Task<SomeInformation> GetSomeInformationAsync (string na ...
  • 這個庫提供了在啟動期間實例化已註冊的單例,而不是在首次使用它時實例化。 單例通常在首次使用時創建,這可能會導致響應傳入請求的延遲高於平時。在註冊時創建實例有助於防止第一次Request請求的SLA 以往我們要在註冊的時候實例單例可能會這樣寫: //註冊: services.AddSingleton< ...
  • 最近公司的很多項目都要改單點登錄了,不過大部分都還沒敲定,目前立刻要做的就只有一個比較老的項目 先改一個試試手,主要目標就是最短最快實現功能 首先因為要保留原登錄方式,所以頁面上的改動就是在原來登錄頁面下加一個SSO登錄入口 用超鏈接寫的入口,頁面改造後如下圖: 其中超鏈接的 href="Staff ...
  • Like運算符很好用,特別是它所提供的其中*、?這兩種通配符,在Windows文件系統和各類項目中運用非常廣泛。 但Like運算符僅在VB中支持,在C#中,如何實現呢? 以下是關於LikeString的四種實現方式,其中第四種為Regex正則表達式實現,且在.NET Standard 2.0及以上平... ...
  • 一:背景 1. 講故事 前些天有位朋友找到我,說他們的程式記憶體會偶發性暴漲,自己分析了下是非托管記憶體問題,讓我幫忙看下怎麼回事?哈哈,看到這個dump我還是非常有興趣的,居然還有這種游戲幣自助機類型的程式,下次去大玩家看看他們出幣的機器後端是不是C#寫的?由於dump是linux上的程式,剛好win ...
  • 前言 大家好,我是老馬。很高興遇到你。 我們為 java 開發者實現了 java 版本的 nginx https://github.com/houbb/nginx4j 如果你想知道 servlet 如何處理的,可以參考我的另一個項目: 手寫從零實現簡易版 tomcat minicat 手寫 ngin ...
  • 上一次的介紹,主要圍繞如何統一去捕獲異常,以及為每一種異常添加自己的Mapper實現,並且我們知道,當在ExceptionMapper中返回非200的Response,不支持application/json的響應類型,而是寫死的text/plain類型。 Filter為二方包異常手動捕獲 參考:ht ...
  • 大家好,我是R哥。 今天分享一個爽飛了的面試輔導 case: 這個杭州兄弟空窗期 1 個月+,面試了 6 家公司 0 Offer,不知道問題出在哪,難道是杭州的 IT 崩盤了麽? 報名面試輔導後,經過一個多月的輔導打磨,現在成功入職某上市公司,漲薪 30%+,955 工作制,不咋加班,還不捲。 其他 ...
  • 引入依賴 <!--Freemarker wls--> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.30</version> </dependency> ...
  • 你應如何運行程式 互動式命令模式 開始一個互動式會話 一般是在操作系統命令行下輸入python,且不帶任何參數 系統路徑 如果沒有設置系統的PATH環境變數來包括Python的安裝路徑,可能需要機器上Python可執行文件的完整路徑來代替python 運行的位置:代碼位置 不要輸入的內容:提示符和註 ...