[SQL Server]Read Committed Snapshot Isolation(RCSI)隔離層級的效能副作用

2017-11-26

4736
0
Lock
2018-05-27

為了減輕查詢交易被大型更新交易封鎖(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;

查詢結果: