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

一線碼農發表於2023-02-02
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 UNCOMMITTED 和 nolock 的效果是一樣的，會引發髒讀現象，主要是因為 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 觀察到的加鎖動態過程，會讓大家有更深入的理解。

相關文章