SQL Server READ_COMMITTED_SNAPSHOT Database Option and READ COMMITED Transaction Isolation Level

By: Sergey Gigoyan   |   Updated: 2020-04-01   |   Comments (7)   |   Related: More > Transactions

Problem

While working with transactions in SQL Server, it is essential to understand transaction isolation levels. As the name suggests, an isolation level defines how transactions are isolated from each other in SQL Server. In other words, an isolation level defines how the transaction can access or use the data and resources which are being modified by other transactions. The higher the isolation level, the higher the degree of isolation. In previous articles, we have discussed and compared all isolation levels and their specifications. Therefore, explaining and discussing the isolation levels is out of the scope of this article. Thus, before reading this article, it is recommended to explore the materials below and get more information about transaction isolation levels:

• Comparison of SQL Server Serializable and Snapshot isolation levels
• Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels
• Compare SQL Server NOLOCK and READPAST Table Hints

In this article, we will discuss the READ_COMMITTED_SNAPSHOT database option and how it can change the behavior of transactions running under the READ COMMITTED transaction isolation level.

Solution

The READ COMMITTED transaction isolation level is the default isolation level in Microsoft SQL Server databases. This level prevents dirty reads, which means that the transaction is not allowed to read dirty, uncommitted data. Non-repeatable and phantom reads, however, are possible in transactions running under this level. A non-repeatable read means that the same transaction can receive different results while reading the same row more than once and a phantom read means that in the same transaction, the same query can return different rows due to the insertion of new rows.

READ COMMITTED isolation level when READ_COMMITTED_SNAPSHOT is OFF

Now, let’s move on to how the READ COMMITED isolation level achieves the prevention of dirty reads.

In this isolation level, the statements are not allowed to read data that has been modified, but not yet committed by other transactions. By default, shared locks are used to guarantee reading only committed data. Shared locks prevent other transactions to modify rows that are being read by the current transaction. Additionally, shared locks prevent the current statement to read rows which have been changed, but not committed by other transactions.

While this locking mechanism eliminates dirty reads, it also decreases concurrency. This is because other transactions must wait while the current transaction completes its read operation and only then modify the data and vice versa – the current transaction must wait for reading the data which is being modified. In the READ COMMITED isolation level, the locking mechanism is used by default because the READ_COMMITTED_SNAPSHOT database option is set OFF by default.

READ COMMITTED isolation level when READ_COMMITTED_SNAPSHOT is ON

When this option is set to ON, the mechanism preventing dirty reads changes.

SQL Server Engine uses row versioning instead of locking. To do so, a transitionally consistent snapshot of the data (as it was before the start of the statement) is created for each statement. Thus, using the snapshots, there is no necessity to place locks for reading the data. Well, this is from the theoretical part. Now, it is time to understand these behaviors in practice, by examples.

First, let’s create a test environment:

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') 

Practice Behavior of the Transaction READ_COMMITTED_SNAPSHOT is OFF

Hence, we have the TestDB database and the TestTable with sample data. It is assumed, that the database isolation level is the default – READ COMMITTED and the READ_COMMITTED_SNAPSHOT database option is OFF.

Now, we open two query windows in the SQL Server Management Studio (SSMS) and on the first window, copy the following code:

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

On the second, we have copied the code below:

--1.2 
USE TestDB 
GO 
  
  
SELECT * FROM TestTable 

First, we executed the first query and immediately after that, we executed the second query:

As we can see, the second query waits while the first transaction is completed and only after that, returns the modified results.

Practice Behavior of the Transaction READ_COMMITTED_SNAPSHOT is ON

In the next example, we will turn on the READ_COMMITTED_SNAPSHOT and monitor the changes in the query’s behavior.

In order to turn on the READ_COMMITTED_SNAPSHOT, we can use the statement below. To be able to successfully execute this statement, there must be no other open connection in the database.

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON 

Again, we open two query windows and paste the statement below in the first window:

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

On the second window, we paste the following:

--2.2 
USE TestDB 
GO 
  
SELECT * FROM TestTable 

Like in the previous example, we first execute the first query and immediately after that, the second one.

The result shows that in this case, the second query does not wait for the first one to complete and immediately returns the result. The latter shows the data in the table before the changes made by the first query. This is because the snapshot is used and no locks prevent the second statement to read the snapshot data:

Even if the READ_COMMITTED_SNAPSHOT is ON, it is possible to use shared locks instead of a row versioning by using the READCOMMITTEDLOCK table hint. This behavior is illustrated in the next example.

The first query is copied to the first window:

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

The second query to the next window:

--2.4 
USE TestDB 
GO 
  
SELECT * FROM TestTable WITH (READCOMMITTEDLOCK) 

The result is similar like in the case when READ_COMMITTED_SNAPSHOT is OFF. The second query waits until the first finishes and then returns the result:

It is important to mention, that the READ_COMMITTED_SNAPSHOT option changes only the behavior of transactions running under the READ COMMITTED isolation level. If we use the second (reader) statement under the SERIALIZABLE or REPEATABLE READ isolation level, it will wait until the first query completes and only then returns data.

Thus, in this case, the first statement will be the following:

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

In the second one, the SERIALIZABLE isolation level will be used:

--3.2 
USE TestDB 
GO 
  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
  
SELECT * FROM TestTable 

As mentioned above, despite the fact that READ_COMMITTED_SNAPSHOT is ON, the second query will wait until the first transaction completes:

To check whether the READ_COMMITTED_SNAPSHOT is on for the database or not, we can query the sys.databases table:

SELECT name AS DatabaseName,is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name = 'TestDB' 

In our case it is ON, therefore the value of the is_read_committed_snapshot_on column is 1:

After finishing our tests, we return the database READ_COMMITTED_SNAPSHOT to its default state – OFF:

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT OFF 

After doing this, if we run the previous query again, we can see that the value of the read_committed_snapshot_on is 0:

Finally, it is important to note, that often the READ_COMMITTED_SNAPSHOT option is confusingly considered as a transaction isolation level. However, as we have already discussed, this is not an isolation level, but a database option that changes the behavior of the READ COMMITTED transaction isolation level. The benefits of using READ_COMMITTED_SNAPSHOT = ON option is that it increases concurrency. However, it is also important to consider that the snapshots are stored in the tempdb database and, definitely, use space. There are no special rules of using this option and database developers should decide whether it is beneficial to use this option or not based on their particular environment.

Conclusion

In conclusion, the READ_COMMITTED_SNAPSHOT is a database option that changes the behavior of the transactions running under the READ COMMITTED isolation level. By default, it is set OFF in SQL Server databases. In this case, locking is used to eliminate dirty reads in the READ COMMITTED transaction isolation level. When it is turned to ON, row versioning is used instead of locking.

Next Steps

To find more information about the discussed topic, please follow the links below:

• Comparison of SQL Server Serializable and Snapshot isolation levels
• Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels
• Compare SQL Server NOLOCK and READPAST Table Hints
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

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.

View all my tips