How to: Set Row Versioning-based Transaction Isolation Levels

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:

1. ALTER DATABASE <current_database> SET READ_COMMITTED_SNAPSHOT ON;
2. ALTER DATABASE <current_database>  SET ALLOW_SNAPSHOT_ISOLATION ON;
3. 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.
 

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.