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就知道了。
參考
[SQL Server][In-Memory OLTP]交易的原子性(Atomicity)與隔離(Isolation)
rico大之[SQL Server]SQL2014和SQL2016 In-Memory OLTP比較