Comparing Different Results with RCSI & Read Committed
Published Dec 20 2018 11:28 AM 693 Views
Microsoft
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





Version history
Last update:
‎Oct 28 2022 12:55 PM
Updated by: