READ_COMMITTED_SNAPSHOT and SNAPSHOT_ISOLATION levels in SQL Server

By: Sergey Gigoyan | Comments | Related: > TSQL

Problem

In a previous article, we discussed the READ_COMMITTED_SNAPSHOT SQL Server database option and its impact on the behavior of a transaction running under the READ COMMITTED transaction isolation level. There is a common misconception that READ_COMMITTED_SNAPSHOT is on isolation level and in some ways, it is similar to the SNAPSHOT isolation level in SQL Server.

In this article, we are going to compare transactions in the READ COMMITTED isolation level (when READ_COMMITTED_SNAPSHOT is ON) and transactions using SNAPSHOT isolation level in SQL Server. Using practical examples, we will illustrate their specifications and make clear their differences. The detailed discussion of transaction isolation levels, however, is out of the scope of this material. Therefore, I suggest reading the articles below will be essential in terms of understanding the topics in this article:

Solution

We are going to compare the SNAPSHOT transaction isolation level and the READ COMMITTED isolation level when the READ_COMMITTED_SNAPSHOT database option is set ON in SQL Server. As we have discussed the latter in a previous article in detail, we will just briefly explain it here.

SQL Server READ COMMITTED isolation level when READ_COMMITTED_SNAPSHOT option is ON

When the READ_COMMITTED_SNAPSHOT SQL Server database option is set ON (it is OFF by default), transactions running under the READ COMMITTED isolation level change their behavior. In order to prevent dirty reads, transactions use row versioning instead of locking. This is achieved by storing the version of the read row before the start of the statement in TempDB database. Even if that row is modified by other transactions, the current statement reads the initial version of it from the snapshot. However, if the same transaction reads the same row next time after having it updated by other transactions, it will see the updated version of the row.

This means that two similar SELECT statements can return different values for the same row in the same transaction. Thus, non-repeatable reads are possible in this case. This is definitely logical as the definition of the READ COMMITTED isolation level states that it only prevents dirty reads and phantom reads, as well as non-repeatable reads, are possible in this isolation level. Hence, the READ COMMITTED isolation level protects only from dirty reads and it does not matter whether the READ_COMMITTED_SNAPSHOT is ON or OFF.

Changing the state of this option only changes the methods of guaranteeing non-dirty reads. It is also important to note that READ_COMMITTED_SNAPSHOT is just a database option and not a separate isolation level.

SQL Server SNAPSHOT isolation level

The Snapshot isolation level eliminates all phantoms – dirty, non-repeatable, and phantom reads, like the SERIALIZABLE isolation level. While these two isolation levels guarantee the highest degree of isolation, the methods of achieving this are quite different.

Unlike the SERIALIZABLE isolation level, in the case of the SNAPSHOT isolation level, optimistic concurrency control is used to eliminate all the above-mentioned phantoms. This means that instead of locking, row versioning is used to access rows instead of placing locks. In case of updating a row by other transactions, the original version of that row (with the transaction sequence number) is stored in the tempdb database and the transaction in snapshot isolation level access that original data.

This process is similar to the case of the READ COMMITTED isolation level with READ_COMMITTED_SNAPSHOT set to ON. Unlike the latter case, when a transaction in SNAPSHOT isolation level tries to commit an update of a row changed (by other transactions) after the transaction started, rollback occurs and an error is raised. However, it is possible to change this behavior if the UPDLOCK hint is used in SELECT statements to retrieve data that will be modified in a transaction with the SNAPSHOT isolation level.

To be able to use the SNAPSHOT isolation level, the ALLOW_SNAPSHOT_ISOLATION database option must be enabled. Although it is mentioned above that tempdb is used to store the original versions of rows, in SQL Server 2019, there is an option to store these rows in the user database. In this latest version of SQL Server, there is a feature called Accelerated Database Recovery (ADR) and when this feature is enabled, version of rows are stored in its Persistent Version Store (PVS), which is in the user's database. All in all, changes made by other transactions are not visible for the statements in a transaction with the SNAPSHOT isolation level. They read the data from the "snapshot" avoiding locking. This non-locking mechanism, in turn, helps to avoid blocking and, therefore, improve the performance, especially when there are many concurrent transactions.

Comparing the SQL Server READ COMMITTED (when READ_COMMITTED_SNAPSHOT is ON) and SNAPSHOT isolation levels in practice

Turning to the practical part of the article, we will compare the specifications and behavior of these isolation levels.

To do so, we create a test environment – TestDB database with the TestTable that is filled with a sample data:

USE master 
GO 
  
CREATE DATABASE TestDB 
GO 
  
USE TestDB 
GO 
  
CREATE TABLE TestTable 
( 
 ID INT, 
 Val CHAR (1) 
) 
  
INSERT INTO TestTable(ID, Val) 
VALUES (1,'A'), (2,'B'),(3, 'C')

The next step is turning the READ_COMMITTED_SNAPSHOT to ON for the TestDB database:

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON

In SQL Server Management Studio (SSMS), we open two windows and on the first window, we copy the code below:

USE TestDB 
GO 
  
BEGIN TRANSACTION 
  
   UPDATE TestTable 
   SET Val='X' 
   WHERE Val='A' 
  
   WAITFOR DELAY '00:00:07' 
  
COMMIT

On the second window, we paste the following code:

USE TestDB
GO
 
BEGIN TRANSACTION
 
   SELECT * FROM TestTable 
 
   UPDATE TestTable
   SET Val='Y'
   WHERE Val='X'
 
   SELECT * FROM TestTable
 
COMMIT

If we execute the first query and immediately after that, the second one, we can see that the first SELECT statement in the second query returns the original value of the first row. The second SELECT returns the updated value:

Two transactions in Read Committed isolation level

The reason for this is that the first SELECT statement can only see the original version of the row. After the first transaction is committed, the UPDATE statement in the second query can see the updated version of the row by the first transaction. Therefore, it can find a row where the value has already been set to 'X' and change it to 'Y'. As a result, two identical SELECT statements return different results.

In order to avoid confusion in our tests, let's update the table and set initial values:

UPDATE TestTable
SET Val='A'
WHERE Val='Y'

Now, we are going to do a similar test for the transaction with the SNAPSHOT isolation level. To do so, firstly we need to set snapshot isolation for the database:

USE master 
GO 
  
ALTER DATABASE TestDB  SET ALLOW_SNAPSHOT_ISOLATION ON

We leave the code in the first window as it was in the previous example:

USE TestDB 
GO 
  
BEGIN TRANSACTION 
  
   UPDATE TestTable 
   SET Val='X' 
   WHERE Val='A' 
  
   WAITFOR DELAY '00:00:07' 
  
COMMIT

In the second window, we paste the following code:

USE TestDB
GO
 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
 
BEGIN TRANSACTION
 
   SELECT * FROM TestTable 
 
   UPDATE TestTable
   SET Val='Y'
   WHERE Val='X'
 
   SELECT * FROM TestTable
 
COMMIT
 
WAITFOR DELAY '00:00:08' 
 
SELECT * FROM TestTable

Like in the example above, we first execute the first transaction and immediately after that the second. As we can see, both queries in the second transaction return the same value because the snapshot is used and changes by the first transaction are not visible for the second one. Therefore, the UPDATE statement in the second transaction cannot find a row with the value 'X' and does not change anything. Nevertheless, when both transactions are committed, the third SELECT statement returns the value modified by the first transaction:

One transaction is in snapshot, other in read committed isolation level

To continue, we again initialize the data in our table:

UPDATE TestTable
SET Val='A'
WHERE Val='X'

Now, we will try to update the same row in two different transactions. First, we will test the behavior of the transaction in the READ COMMITTED isolation level (READ_COMMITTED_SNAPSHOT is ON).

The first query:

USE TestDB
GO
 
BEGIN TRANSACTION
 
   UPDATE TestTable
   SET Val='X'
   WHERE Val='A'
 
   WAITFOR DELAY '00:00:07' 
 
COMMIT

The second query – is executed immediately after the first one:

USE TestDB 
GO 
  
BEGIN TRANSACTION 
  
   SELECT * FROM TestTable 
  
   UPDATE TestTable 
   SET Val='Y' 
   WHERE Val='A' 
  
   SELECT * FROM TestTable 
  
COMMIT

Both transactions try to update the row with Value = 'A'. The result shows that the first SELECT statement of the second transaction returns the original value, the UPDATE statement waits for the first transaction to complete, which, in turn, changes the value from 'A' to 'X'. Therefore, the UPDATE statement in the second query changes nothing and consequently, the second SELECT statement returns the changes made by the first transaction:

Before testing the same for the SNAPSHOT isolation level, we again initialize the table:

UPDATE TestTable
SET Val='A'
WHERE Val='X'
 
The first query:
 
USE TestDB
GO
 
BEGIN TRANSACTION
 
   UPDATE TestTable
   SET Val='X'
   WHERE Val='A'
 
   WAITFOR DELAY '00:00:07' 
   
COMMIT

The second query – SERIALIZABLE isolation is used and is executed immediately after the first query:

USE TestDB
GO
 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
 
BEGIN TRANSACTION
 
   SELECT * FROM TestTable 
 
   UPDATE TestTable
   SET Val='Y'
   WHERE Val='A'
 
   SELECT * FROM TestTable
 
COMMIT

In this case, the second transaction is rolled back and an error is raised:

Transaction in snapshot isolation level fails

The changes made by the first transaction are committed:

USE TestDB 
GO 
  
SELECT * FROM TestTable

This is because in the SNAPSHOT isolation level, it is not possible to modify a row that is being modified by another transaction. This is one of the key differences between SNAPSHOT and READ COMMITTED (READ_COMMITTED_SNAPSHOT is ON) isolation levels and guarantees a higher level of isolation. As it is mentioned above in this article, this behavior can be changed using locking instead of row versioning even in the SNAPSHOT isolation level.

Let's illustrate this behavior as well.

Initializing the table:

UPDATE TestTable
SET Val='A'
WHERE Val='X'

The first query:

USE TestDB
GO
 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
 
BEGIN TRANSACTION
   
   SELECT * FROM TestTable WITH (UPDLOCK)
 
   WAITFOR DELAY '00:00:07' 
 
   UPDATE TestTable
   SET Val='Y'
   WHERE Val='A'
 
   SELECT * FROM TestTable
 
COMMIT

The second query – UPDLOCK hint is used with the SELCET statement – is executed immediately after executing the first one:

USE TestDB
GO
 
BEGIN TRANSACTION
 
   UPDATE TestTable
   SET Val='X'
   WHERE Val='A'
 
COMMIT

It is clear from the result that the row is updated by the first transaction, which is in SNAPSHOT isolation level, and the second one modifies 0 rows:

Behavior of transaction in snapshot isolation level

This occurs because we use the UPDLOCK hint to lock the data (instead of row versioning) that will be updated in the current transaction. Thus, the second transaction waits for the first one to complete and after that, there are no rows matching to the WHERE criteria in the UPDATE statement.

Conclusion

To sum up, both the SNAPSHOT and the READ COMMITTED isolation levels (with READ_COMMITTED_SNAPSHOT is ON) use row versioning to manage isolation degree in transactions. While the SNAPSHOT isolation level protects from all phenomena, READ COMMITTED isolation level with enabled READ_COMMITTED_SNAPSHOT option prevents only dirty reads. Whereas the SNAPSHOT is a transaction isolation level, the READ_COMMITTED_SNAPSHOT is just a database option that can change the behavior of the READ COMMITTED isolation level in preventing dirty reads.

Next Steps

To read more information about the discussed topic, please use the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips