一：背景
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 觀察到的加鎖動態程序，會讓大家有更深入的理解，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/542920.html
標籤：SQL Server