Row versioning-based transaction isolation levels were introduced since SQL Server 2005. They use row versioning for concurrency controls instead of locks. Therefore they are very attractive because no more blocking issues with usage of row versioning. However, since row versioning-based isolation levels are related to both database-level settings and transaction-level settings, it is very easy to get confused about how to set it right. I hope you will get a clear idea after reading this.
Two types, three commands?
There are two types of transaction isolation levels based on row versioning. One is READ COMMITTED using row versioning. (It is also known as READ COMMITTED SNAPSHOT isolation or RCSI. ) The other is SNAPSHOT isolation. The difference between the two is the "cutoff" time for the latest row versions they will use. RCSI uses latest row versions when the statement starts while SNAPSHOT uses the latest committed row when the transaction starts.
On the other hand, there are three
commands involved in order to set RCSI and SNAPSHOT isolation levels:
- ALTER DATABASE <current_database> SET READ_COMMITTED_SNAPSHOT ON;
- ALTER DATABASE <current_database> SET ALLOW_SNAPSHOT_ISOLATION ON;
- SET TRANSACTION ISOLATION LEVEL SNAPSHOT
The first two commands are
database-level settings and can be turned on at the same time. So the question
is:
What is the right combination?
First of all, let us do some
experiments. Create a table with two rows:
create table tranTest (id int
primary key, price int)
insert tranTest values (1, 10), (2, 20)
Suppose both
ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set OFF. You can double
check the isolation levels by following the steps in How to Check Transaction Isolation Levels.
Experiment
1: ALLOW_SNAPSHOT_ISOLATION is OFF with READ_COMMITTED_SNAPSHOT is ON;
Run the following code in session 1:
ALTER DATABASE
DemoSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON;
Next, run the following code in
session 2:
SET TRANSACTION
ISOLATION LEVEL SNAPSHOT
Ok, it seems fine with no error
message appears.
However, when you begin a new
transaction in the same session that you have tried setting SNAPSHOT isolation,
it will give the following error:
Msg 3952, Level 16, State
1, Line 3
Snapshot isolation transaction failed accessing database 'xxx' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
Snapshot isolation transaction failed accessing database 'xxx' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
That means that in order to start a
SNAPSHOT transaction, you must have ALLOW_SNAPSHOT_ISOLATION to be turned on.
Only when ALLOW_SNAPSHOT_ISOLATION database option is set ON, the support for
snapshot isolation transaction is activated. Moreover, the database engine does
not generate row versions for modified data until all active transactions that
have data modified complete. Interestingly, it does not matter whether READ_COMMITTED_SNAPSHOT
is ON or OFF.
After you set
ALLOW_SNAPSHOT_ISOLATION option on, run the following codes:
---- EXPERIMENT for SNAPSHOT ISOLATION
-- Connection 1
BEGIN TRAN;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
UPDATE dbo.tranTest
SET price += 1.00
WHERE id = 2;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
-- Connection 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
-- 20
-- Connection 1
COMMIT TRAN;
-- Connection 2
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
--- 20
COMMIT TRAN;
BEGIN TRAN;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
--- 21
COMMIT TRAN;
-- Connection 1
-- Cleanup
UPDATE dbo.tranTest
SET price = 20.00
WHERE id = 2;
Notice that in session 2, we got
same results for reading in the first transaction but a different result for
the later transaction. That proves that SNAPSHOT isolation level always read
the last committed row version when the transaction starts. The cutoff for the
row to be read is when transaction starts.
Experiment 2: ALLOW_SNAPSHOT_ISOLATION is ON with READ_COMMITTED_SNAPSHOT is ON;
In
order to enable the database option for READ_COMMITTED_SNAPSHOT, please only
have one active session connected to the related database. Then run the
command:
ALTER DATABASE DemoSQL2012 SET READ_COMMITTED_SNAPSHOT
ON;
Then
test the isolation level by running these codes:
--- EXPERIMENT FOR
READ_COMMITTED_SNAPSHOT
-- Connection 1
BEGIN TRAN;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
UPDATE dbo.tranTest
SET price += 1.00
WHERE id = 2;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
-- Connection 2
BEGIN TRAN;
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
-- 20
-- Connection 1
COMMIT TRAN;
-- Connection 2
SELECT id, price
FROM dbo.tranTest
WHERE id = 2;
--- 21
COMMIT TRAN;
-- Connection 1
-- Cleanup
UPDATE dbo.tranTest
SET price = 20.00
WHERE id = 2;
If you get the same results in session 2 as shown above, congratulations! The difference in the results demonstrates that READ_COMMITTED_SNAPSHOT always get the last committed row version right before the statement starts.
Conclusion
In
order set SNAPSHOT transaction isolation, you need to first turn on the
ALLOW_SNAPSHOT_ISOLATION option at database level. Then at the related session,
run "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" to set isolation levels
at transaction level.
On
the other hand, for RCSI, you only need to have READ_COMMITTED_SNAPSHOT turned
on at database level.
