First published on MSDN on Mar 03, 2011

Authored by Kun Cheng

Author: Kun Cheng
Reviewers: Sunil Agarwal, Steve Howard, Shaun Tinline-Jones, Prem Mehra, Sanjay Mishra, Michael Thomassy, Mike Ruthruff, Howard Yin, Jimmy May

During a recent ISV partner engagement, we decided to enable RCSI (Read Committed Snapshot Isolation) on a SQL Server 2008 R2 instance with the objective of minimizing blocking. As expected, we did not encounter any significant blocking. However, we did find different behavior when we compared the results using RCSI to those we got using SQL Server’s default RC (Read Committed) isolation level. In a nutshell, blocking in the SQL Server database may have different data results when a query reads and changes multiple tables at the same time using RCSI compared to using RC isolation level.

The remainder of this blog describes this behavior in greater detail, along with a basic code for illustrative purposes:

Let’s start with the default RC isolation level:

-- set up test database and tables t1 and t2

CREATE DATABASE testRCSI;

GO

USE testRCSI

GO

CREATE TABLE dbo.t1(id int NOT NULL PRIMARY KEY, bid int NOT NULL)

GO

CREATE TABLE dbo.t2(id int NOT NULL PRIMARY KEY)

GO

INSERT INTO dbo.t1(id,bid) VALUES(1,1)

INSERT INTO dbo.t2(id) VALUES(1)

GO

Time	Connection 1	Connection 2
T1	BEGIN TRAN  -- with RC isolation level INSERT INTO t1(id,bid) VALUES(2,2) INSERT INTO t2(id) VALUES(2)
T2		DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)
T3		Blocked
T4	COMMIT
T5		Two rows deleted

The results were:

SELECT id FROM dbo.t1

(none)

SELECT id FROM dbo.t2

1

2

After we enabled RCSI, the result were very different:

-- Set isolation level as read_committed_snapshot and reset tables

ALTER DATABASE testRCSI SET READ_COMMITTED_SNAPSHOT ON;

GO

USE testRCSI

GO

TRUNCATE TABLE t1

TRUNCATE TABLE t2

GO

INSERT INTO t1(id,bid) VALUES(1,1)

INSERT INTO t2(id) VALUES(1)

GO

Time	Connection 1	Connection 2
T1	BEGIN TRAN  -- with RCSI level INSERT INTO t1(id,bid) VALUES(2,2) INSERT INTO t2(id) VALUES(2)
T2		DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)
T3