為了減輕查詢交易被大型更新交易封鎖(Blocked),也理解樂觀鎖定(Optimistic Locking)可能的交易衝突後,也許我們會在SQL Server上使用Oracle預設相同的隔離層級: Read Committed Snapshot Isolation(RCSI),她是一種Row Version Base的隔離層級,與snapshot isolation不太相同的是她是單一版本。當交易更新資料時,系統會自動將修改前的版本放到Tempdb提供給可能發生的查詢交易。因為也有搬分頁到其他資料庫的工作會執行,在效能上會不會有明顯的副作用?
SQL Server預設是Read Committed,Oracle則是Read Committed Snapshot Isolation(RCSI)
建立環境
建立資料庫
CREATE DATABASE [SnapshotDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'SnapshotDb', FILENAME = N'C:\SQL\SnapshotDb.mdf' , SIZE = 2GB , FILEGROWTH = 1GB )
LOG ON
( NAME = N'SnapshotDb_log', FILENAME = N'C:\SQL\SnapshotDb_log.ldf' , SIZE = 1GB , FILEGROWTH = 1GB)
GO
建立一個100萬筆的資料表
USE [SnapshotDb]
CREATE TABLE T1
(ID INT IDENTITY,C2 CHAR(2000),C3 uniqueidentifier, PRIMARY KEY(ID))
SET NOCOUNT ON
DECLARE @I INT = 0
BEGIN TRAN
WHILE @I < 1000000
BEGIN
INSERT INTO T1
VALUES (CONVERT(VARCHAR,@I),NEWID())
SET @I = @I + 1
END
COMMIT
對照組
1.尚未啟用Read Committed Snapshot Isolation之前,我們用批次交易更新100萬筆當中的10萬筆,但交易先不要結束(commit或是rollback)。
SET STATISTICS IO ON
SET STATISTICS TIME ON
BEGIN TRAN T1
UPDATE T1
SET C2 = NEWID()
WHERE ID >= 300000 AND ID < 400000
執行結果:
資料表 'T1'。掃描計數 1,邏輯讀取 33463,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
SQL Server 執行次數:
,CPU 時間 = 250 ms,經過時間 = 277 ms。
2.新開一個查詢視窗,查詢id = 300001的資料
SELECT * from t1 WITH(NOLOCK)
where id = 300001
SELECT * from t1
where id = 300001
查詢交易封鎖中! 不過更新交易的CPU 時間 = 250 ms,經過時間 = 277 ms
好,然後把交易Rollback..
實驗組
1.啟用Read Committed Snapshot Isolation
ALTER DATABASE [SnapshotDb] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
2.重新執行剛剛的批次更新交易
USE SnapshotDb
SET STATISTICS IO ON
SET STATISTICS TIME ON
BEGIN TRAN T1
UPDATE T1
SET C2 = NEWID()
WHERE ID >= 300000 AND ID < 400000
執行結果
資料表 'T1'。掃描計數 1,邏輯讀取 33463,實體讀取 3,讀取前讀取 33431,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
SQL Server 執行次數:
,CPU 時間 = 391 ms,經過時間 = 560 ms。
3.新開一個查詢視窗,查詢id = 300001的資料
交易不再被封鎖,我們可以有中途查詢(Dirty Read)或是修改前的查詢(Row Version)兩種選擇。
透過DMV查詢目前資料列版本
SELECT
sdt.session_id as '[連線id]'
,sdt.transaction_id as '[交易id]'
,CAST(DATEADD(second, sdt.elapsed_time_seconds, '19000101') as TIME) as '[資料列版本時間]'
,[host_name] as '[主機名稱]'
,last_request_start_time
,last_request_end_time
,st.[text] as '[SQL 語法]'
,s.row_count as '[資料筆數]'
FROM sys.dm_tran_active_snapshot_database_transactions sdt
INNER JOIN sys.dm_exec_sessions s
ON s.session_id = sdt.session_id
JOIN sys.dm_exec_connections c
ON s.[session_id] = c.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests R
ON s.[session_id] = R.[session_id]
CROSS APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan(R.[plan_handle]) AS qp
WHERE s.is_user_process = 1
ORDER BY elapsed_time_seconds DESC;
查詢結果:
小結:
效能上有影響,執行時間多1倍,cpu也增加。不過如果都只是0.3秒的差別,也許就還能接受。
隔離層級 | CPU | 執行時間 |
Read Committed | 250ms | 277ms |
Read Committed Snapshot Isolation | 391ms | 560ms |
- tempdb牛仔很忙,要管user object、Internal job,還要支援version store,tempdb最佳化。
- 大型交易分批做。
- 如果再加上Online rebuild index,會有什麼火花?(下次試試)
隔離的世界,瑞士-格林德瓦
參考
How Row versioning impact tempDB ?
Read Committed Snapshot Isolation– Two Considerations