<optgroup id="6y7f6"><small id="6y7f6"></small></optgroup>

<code id="6y7f6"></code>

  • <p id="6y7f6"><tbody id="6y7f6"><ins id="6y7f6"></ins></tbody></p>
    <code id="6y7f6"><form id="6y7f6"></form></code>

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

      一:背景

      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 觀察到的加鎖動態過程,會讓大家有更深入的理解。

      posted @ 2023-02-02 17:50  一線碼農  閱讀(1618)  評論(0編輯  收藏  舉報
      欧洲黄色网页链接入口,免费A级毛片无码无遮挡久久影院,a免费黄色网址,国产一级黄色的网站
      <optgroup id="6y7f6"><small id="6y7f6"></small></optgroup>

      <code id="6y7f6"></code>

    1. <p id="6y7f6"><tbody id="6y7f6"><ins id="6y7f6"></ins></tbody></p>
      <code id="6y7f6"><form id="6y7f6"></form></code>