一:背景
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 觀察到的加鎖動态過程,會讓大家有更深入的了解。