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

 

Blocked

T4

COMMIT

 

T5

 

One row deleted

 

The results were:

SELECT id FROM dbo.t1

2

SELECT id FROM dbo.t2

1

2

How do we explain the difference in the results? At time T3, when DELETE was blocked by INSERT, sys.dm_tran_locks showed that the DELETE was waiting for a key lock on the newly added row 2. Once the INSERT was committed, the DELETE was unblocked on row 2. So why wasn’t the row deleted?

 

Explaining Results when RCSI Is Enabled

To understand this behavior, it is important to note that even when RCSI is enabled on the entire database, the snapshot effect is only applicable to the read operations on a table. The snapshot effect does not apply to DML (data manipulation language) operations.

In the query above, the DELETE operation occurred on table t1, so SQL Server took a blocking lock. Table t2, however, was accessed by the read part of the DELETE query, so a snapshot of the data in t2 was obtained and it did not block. It retrieved one row (value 1) because it was the only row committed at the beginning of the transaction. Once the DELETE operation was unblocked, it found only one qualifying row based on the join and it deleted that row.

So how do you resolve the differences between the two tables and achieve the same result as achieved using the default RC isolation level? You can use READCOMMITTEDLOCK query hint to make the read operation (against table t2) comply with the rules for the RC isolation level. In fact the query hint would make queries behave the same as under RC isolation level. This is exactly what my ISV partner wanted for the scenario and implemented in their application.

DELETE t1 FROM t1 JOIN t2 (READCOMMITTEDLOCK) ON (t1.bid=t2.id)

-- this would delete both rows from t1

 

Results:

SELECT id FROM dbo.t1

(none)

SELECT id FROM dbo.t2

1

2

 

Alternately, if you just want to obtain the same results when RSCI was enabled, you can use snapshot transaction isolation for the DELETE query:

ALTER DATABASE testRCSI SET ALLOW_SNAPSHOT_ISOLATION ON;

SET transaction ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)

-- this is not blocked by insert

COMMIT

 

Results:

SELECT id FROM dbo.t1

2

SELECT id FROM dbo.t2

1

2

In summary, blocking in the SQL Server database might have different results when a query reads and changes multiple tables at the same time using RCSI than it does when using the default RC isolation levels. However, you can take the actions this post describes to get the results you expect.

For more information about row versioning-based isolation levels, please seeUnderstanding Row Versioning-Based Isolation Levels” (http://technet.microsoft.com/en-us/library/ms189050.aspx).

 

Reference:

Recommend reading of Sunil’s series of concurrency blogs:

Concurrency Series: My application was running fine yesterday buy why is it blocking today?”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-my-application-was-running-fine-yesterday-buy-why-is-it-blocking-today.aspx

Concurrency Series: Minimizing blocking between updaters”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-minimizing-blocking-between-updaters.aspx

“Concurrency Series: Why do I get block when no one has locked the row(s) being queried?”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-why-do-i-get-block-when-no-one-has-locked-the-row-s-being-queried.aspx

“Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-why-do-i-get-blocking-when-i-use-read-uncommitted-isolation-level-or-use-nolock-hint.aspx

“Concurrency Series: Basics of Transaction Isolation Levels”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx