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

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資料的一致性。

 


更新衝突

因為session 2交易不會被封鎖(blocking),如果session2在交易中也更新了t1這1筆資料,兩個機制也有截然不同結果。

Snapshot isolation出現交易衝突 

RCSI則會更新成功,根本不把session1更新的結果看成既定事實,也就是樂觀鎖定(Optimistic lock)會碰到的問題了。

 


小結:

  • RCSI的一致性只在statement層級(那一瞬間的單版本),snapshot isolation的一致性則在交易層級(多版本)
  • RCSI的副作用而且沒有警示的狀況需要考慮。
  • 兩者也能混搭。
  • 究竟in-memory table使用的snapshot是哪一種?明晚來SQL PASS就知道了。

 


參考

Docs SQL Server 中的快照隔離

[SQL Server][In-Memory OLTP]交易的原子性(Atomicity)與隔離(Isolation)

rico大之[SQL Server]SQL2014和SQL2016 In-Memory OLTP比較