[SQL Server]讀取認可快照隔離(RCSI) vs 交易快照隔離(Snapshot Isolation)的查詢一致性

2017-12-13

4771
0
Lock

SQL Server在磁碟資料表(Disk-Base)提供了兩種與快照有關的樂觀鎖定機制: RCSI(Read Committed Snapshot Isolation)及Snapshot Isolation，他們都是減少查詢交易被封鎖的武器之一，當資料被其他交易更新時，這兩種機制都可以透過Tempdb加上row version查詢到資料的前一版，讓交易免於被封鎖(blocked)的命運。明晚要參加SQL Pass，Rico大的主題是進擊的In-Memory OLTP，學習記憶體資料表交易前，先來預習傳統磁碟資料表在這兩種機制下的查詢一致性。

啟用方式

兩個機制剛好不是資料庫預設的屬性，需要使用Alter Database指令啟用。

RCSI開啟

啟動之後所有磁碟資料表的查詢都會適用。

ALTER DATABASE X SET READ_COMMITTED_SNAPSHOT ON

如果線上還有其他使用者，我們也同意踢出使用者，可以加上With Rollback Immediate

ALTER DATABASE X SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Snapshot Isolation開啟

啟動後還必須在交易初始時設定交易隔離層級SNAPSHOT啟用。

ALTER DATABASE X SET ALLOW_SNAPSHOT_ISOLATION ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

為了比較RCSI與Snapshot isolation在資料一致性的差異，待會我們會建立兩個資料庫並且用並行的交易作測試。

建立資料庫及測試資料表

分別建立SNAPSHOTDb及RCSIDb

CREATE DATABASE SNAPSHOTDb
ALTER DATABASE SNAPSHOTDb SET ALLOW_SNAPSHOT_ISOLATION ON

use SNAPSHOTDb
CREATE TABLE T1
(
           ID INT IDENTITY PRIMARY KEY,
           NAME VARCHAR(30)
)
INSERT INTO T1 VALUES('安娜')


CREATE DATABASE RCSIDb
ALTER DATABASE RCSIDb SET READ_COMMITTED_SNAPSHOT ON
use RCSIDb
CREATE TABLE T1
(
           ID INT IDENTITY PRIMARY KEY,
           NAME VARCHAR(30)
)
INSERT INTO T1 VALUES('安娜')

測試Snapshot isolation的一致性

打開SSMS，查詢視窗1先執行以下SQL指令(作交易資料更新)

USE SNAPSHOTDb

BEGIN TRAN 
UPDATE T1 
   SET NAME = '艾莎'
 WHERE ID = 1
SELECT *,GETDATE() FROM T1
 WAITFOR DELAY '00:00:10'
COMMIT
SELECT *,GETDATE() FROM T1

接著在10秒內，在查詢視窗2執行以下SQL指令

USE SNAPSHOTDb
SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

BEGIN TRAN 
SELECT *,GETDATE() FROM T1
WAITFOR DELAY '00:00:10'
SELECT *,GETDATE() FROM T1
COMMIT

SELECT *,GETDATE() FROM T1

整理Snapshot isolation執行結果

橘色框代表交易的範圍(Begin Tran到commit)，Session 1在19:50:24時將資料從安娜被更新成艾莎，交易持續了10秒，一直到19:50:34才commit，但Session2的交易則在Session1的交易中及交易後的兩個時間點作了查詢，可以發現2次查詢的資料都安娜，維持著交易的一致性。如果此時有Session 3加入查詢，也將會和Session2相同，也將會有自己交易開始時的版本。

測試RCSI的一致性

打開SSMS，查詢視窗1先執行以下SQL指令(作交易資料更新)

use RCSIDb
SELECT *,GETDATE() FROM T1
 WAITFOR DELAY '00:00:01'
BEGIN TRAN 
UPDATE T1 
   SET NAME = '艾莎'
 WHERE ID = 1
SELECT *,GETDATE() FROM T1
 WAITFOR DELAY '00:00:10'
COMMIT
SELECT *,GETDATE() FROM T1

接著在10秒內，在查詢視窗2執行以下SQL指令

USE RCSIDb

BEGIN TRAN 
SELECT *,GETDATE() FROM T1
WAITFOR DELAY '00:00:10'
SELECT *,GETDATE() FROM T1
COMMIT

SELECT *,GETDATE() FROM T1

整理RCSI執行結果

Session 1在20:06:43時將資料從安娜被更新成艾莎，交易持續了10秒，一直到20:06:53才commit，但Session2的交易則在Session1的交易中及交易後作了2次查詢，可以發現查詢的資料結果不一樣，45秒時，因為Session1還在交易，所以他查詢到前一版的資料，55秒之後，Session1 Commit了，所以即使Session2還在交易，他還是查詢到新的結果，交易中的2次查詢呈現不同的結果，看起來只能確保查詢的那一刻，能看到一樣已經commit資料的一致性。