Author: Kun ChengReviewers: 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:
-- set up test database and tables t1 and t2
CREATE DATABASE testRCSI;
CREATE TABLE dbo.t1(id int NOT NULL PRIMARY KEY, bid int NOT NULL)
CREATE TABLE dbo.t2(id int NOT NULL PRIMARY KEY)
INSERT INTO dbo.t1(id,bid) VALUES(1,1)
INSERT INTO dbo.t2(id) VALUES(1)
BEGIN TRAN -- with RC isolation level
INSERT INTO t1(id,bid) VALUES(2,2)
INSERT INTO t2(id) VALUES(2)
DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)
Two rows deleted
The results were:
SELECT id FROM dbo.t1
SELECT id FROM dbo.t2
-- Set isolation level as read_committed_snapshot and reset tables
ALTER DATABASE testRCSI SET READ_COMMITTED_SNAPSHOT ON;
TRUNCATE TABLE t1
TRUNCATE TABLE t2
INSERT INTO t1(id,bid) VALUES(1,1)
INSERT INTO t2(id) VALUES(1)
BEGIN TRAN -- with RCSI level
One row deleted
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
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
-- this is not blocked by insert
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 see “Understanding Row Versioning-Based Isolation Levels” (http://technet.microsoft.com/en-us/library/ms189050.aspx).
Recommend reading of Sunil’s series of concurrency blogs: