SQLSERVER 快照隔離級別 到底怎麼理解?

一線碼農發表於2023-02-05
SQLServer

一：背景

1. 講故事

上一篇寫完 SQLSERVER 的四個事務隔離級別到底怎麼理解？ 之後，有朋友留言問什麼時候可以把 snapshot 隔離級別給補上，這篇就來安排，快照隔離級別看起來很魔法，不過在修車之前，得先看下怎麼開車。

二：snapshot 隔離詳解

1. snapshot 之前的困境

在瞭解 snapshot 之前先看看沒有它會存在什麼樣的困境？還是用上一篇的 post 表做案例，參考sql 如下。


CREATE TABLE post(id INT IDENTITY,content char(3))
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');

大家都知道 SQLSERVER 的預設隔離級別是 READ COMMITTED，在下面的場景中 會話2 會被 會話1 阻塞。


---- 會話1 ----

BEGIN TRAN
UPDATE post SET content='zzz' WHERE id=1

---- 會話2 ----

BEGIN TRAN
SELECT * FROM post  WHERE id=1;

那如何緩解呢？有一個粗暴的方法就是加 nolock 可以解決這個問題。


BEGIN TRAN
SELECT * FROM post (NOLOCK) WHERE id=1;

但加上 nolock 也不是一種完美的解決方案，如果 會話1 在後續操作中 ROLLBACK 了，那對 會話2 來說就是髒讀，那如何解決 既要....又要.... 的問題呢？這就引入了 snapshot 隔離級別，接下來看下怎麼玩的。

2. snapshot 的簡單使用

要想使用 snapshot 隔離級別，需要開啟資料庫的 ALLOW_SNAPSHOT_ISOLATION 開關，為了方便測試，我們把資料庫 刪除重建。


DROP DATABASE MyTestDB
CREATE DATABASE MyTestDB
ALTER DATABASE MyTestDB  SET ALLOW_SNAPSHOT_ISOLATION ON
USE MyTestDB
CREATE TABLE post(id INT IDENTITY,content char(3))
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的執行中設定快照隔離級別，參考 sql 如下：


SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT * FROM post  WHERE id=1;

從圖中看果然解決了 既要 .... 又要 的問題，既沒有阻塞，也沒有髒讀，?哈。。。

3. snapshot 是什麼原理

要探究個明白得從底層的資料頁說起了，可以用 DBCC PAGE 和 DBCC PAGE 命令觀察。


DBCC TRACEON(3604)
DBCC IND(MyTestDB,post,-1)
DBCC PAGE(MyTestDB,1,240,3)

從圖中可以看到，資料頁上每一個 Slot 指向的表記錄尾部會有一些空間來儲存 Version Information 記錄的版本資訊，比如上面的 事務號時間戳，版本指標，目前看 Version Pointer: Null 指向的是 NULL，有了這些基礎之後，重新將剛才的兩個會話開啟再次觀察 240號 資料頁。

從圖中可以清晰的看到，會話1已經將記憶體頁修改成了 zzz，會話2 讀取的 aaa 肯定就是 3:8:0 指向的版本記錄了。

有些朋友可能就有疑問了，這個 3:8:0 是怎麼看出來的？其實就是記錄中的 00000800 00000300 這一段，看不習慣的話可以用 windbg 附加一下。

接下來的一個問題是 3:8:0 到底指向的是哪裡？如果看過 MSDN 上的說明應該知道它指向的是 TempDB 資料庫，接下來用 DBCC PAGE 去看下是不是我的 aaa 記錄。


DBCC PAGE(tempdb,3,8,2)

輸出結果如下：


PAGE: (3:8)
Memory Dump @0x000000203ABF8000

000000203ABF8000:   01020000 2000fe00 00000000 00000100 00000000  .... ...............
000000203ABF8014:   00000100 07000080 451fb900 08000000 03000000  ........E...........
000000203ABF8028:   25000000 78010000 50000000 00000000 00000000  %...x...P...........
000000203ABF803C:   00000000 01000000 00000000 00000000 00000000  ....................
000000203ABF8050:   00000000 00000000 00000000 00000000 26010059  ................&..Y
000000203ABF8064:   0000008b 03000000 00010000 00000000 00050000  ....................
000000203ABF8078:   00000000 00000050 00000000 010b0000 00220000  .......P........."..
000000203ABF808C:   00815c00 00000000 00000000 00140000 0050000b  ..\..............P..
000000203ABF80A0:   00010000 00616161 02000000 00000000 00000080  .....aaa............
000000203ABF80B4:   03000000 00000000 00000000 381f0000 00000000  ............8.......
...
000000203ABF9FF4:   00000000 0b01d000 be006000                    ..........`.

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)

從右邊的asc碼看果然就是我的 aaa，如果大家對整個流程有點懵的話，畫個圖大概就像下面這樣。

對 快照級別事務 的儲存原理有了一定的認識之後，接下來從鎖的角度觀察下為什麼能避開阻塞，將二個會話重新執行下，用 SQL Profile 觀察下加鎖過程。

從圖中可以看的非常清楚， 會話1在1:240:0 記錄上獲取到了 X 鎖，會話2 壓根就沒在表記錄上附加任何鎖，直接提取表記錄的 Version Pointer 指向的 Slot，完美避開 X 鎖，也就不存在鎖互斥啦。。。

三：總結

從儲存機制和加鎖過程可以看到如下特點：

  • 開啟 ALLOW_SNAPSHOT_ISOLATION 之後，每條記錄都會有一個 版本資訊，浪費了大量的資料頁空間。

  • tempdb 是一個極其寶貴的伺服器級別共享空間，被所有的資料庫共享，遇到高併發的情況下可能會引發大量的 閂鎖 等待造成的語句阻塞，所以一定要慎用，儘可能的減輕 tempdb 的負擔。

相關文章