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
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...