SQL Server READ_COMMITTED_SNAPSHOT Database Option and READ COMMITED Transaction Isolation Level


By:   |   Updated: 2020-04-01   |   Comments (6)   |   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:

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:

query results

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:

query results

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:

query results

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:

query results

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:

query results

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:

query results

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:



Last Updated: 2020-04-01


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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





Comments For This Article




Wednesday, October 07, 2020 - 12:27:19 PM - Artem Elkin Back To Top (86621)
You are right. I thought it's initial value is A and you are changing it to X (as in the first example), sorry :)

Wednesday, October 07, 2020 - 10:36:04 AM - Sergey Gigoyan Back To Top (86619)
Artem, if you follow all steps in the article, I believe you also will be sure that it returns X.

Wednesday, October 07, 2020 - 6:28:11 AM - Artem Elkin Back To Top (86615)
Are you sure it returns X in the first row when READ_COMMITTED_SNAPSHOT is ON? I believe it must return A.

Monday, May 18, 2020 - 8:55:46 PM - Sergey Gigoyan Back To Top (85689)

The tip has been updated accordingly. Thank you, Cristian.


Wednesday, May 06, 2020 - 3:30:51 PM - Sergey Gigoyan Back To Top (85587)

Good comment, Cristian, thank you. Agreed.


Tuesday, May 05, 2020 - 4:27:45 PM - Cristian Satnic Back To Top (85576)

I think some corrections are needed due to how the term concurrency is used.

In the section - READ COMMITTED isolation level when READ_COMMITTED_SNAPSHOT is OFF:

While this locking mechanism eliminates dirty reads, it also increases concurrency.

*increases* should probably be changed to *decreases* in that sentence.

And towards the end of the article:

The benefits of using READ_COMMITTED_SNAPSHOT = ON option is that it reduces concurrency.

*reduces* should probably be changed to *increases*



download





Recommended Reading

DDL commands in Transactions in SQL Server versus Oracle

Long Running Transactions Cause SQL Server Transaction Log to Grow

Lesson on SQL Server Deadlocks and how to solve

Handling SQL Server transactions in nested stored procedures using Savepoints

Understanding SQL Server Transaction Savepoints














get free sql tips
agree to terms