Read Committed Snapshot Isolation Level

Read Committed Snapshot Isolation Level in SQL Server

In this article, I am going to discuss Read Committed Snapshot Isolation Level in SQL Server with Examples. Please read our ALLOW_SNAPSHOT_ISOLATION in SQL Server following article before proceeding to this article. The Read Committed Snapshot Isolation Level in SQL Server is not at all a different Kind of isolation level. It is just another way of implementing the Read Committed Isolation Level in SQL Server. The problem that we have with the Read Committed Isolation level is that it blocks the transaction if it is trying to read the data which is updating by another transaction at the same time.

Let us understand Read Committed Snapshot Isolation Level with an example.

We are going to use the following Products table.

Read Committed Snapshot Isolation Level in SQL Server

Please use the below SQL Script to create and populate the Products table with the required data.

-- Create Products table
CREATE TABLE Products
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Quantity INT
)
Go

-- Insert test data into Products table
INSERT INTO Products values (1001, 'Mobile', 10)
INSERT INTO Products values (1002, 'Tablet', 20)
INSERT INTO Products values (1003, 'Laptop', 30)

Example:

First, open two instances of SQL Server Management Studio. From the first instance execute the Transaction 1 code and from the second instance execute the Transaction 2 code and you will notice that Transaction 2 is blocked until Transaction 1 completed its execution.

--Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

  UPDATE Products SET Quantity = 5 WHERE Id = 1001
  WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION

--Transaction 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
  SELECT * FROM Products WHERE Id = 1001 
COMMIT TRANSACTION

As you can see here we are using Read Committed Transaction Isolation Level and also you can observe that the Transaction2 is blocked until the Transaction1 completed its execution.

Let’s see how to enable Transaction 2 to make use of the row versioning technique provided by SQL Server instead of the locking mechanism that we have seen in the previous example by using Read Committed Transaction Isolation Level. To use the Row Versioning Technique we need to enable the Read Committed Snapshot Isolation at the database level.

Please use the following code to enable the READ_COMMITTED_SNAPSHOT Isolation Level in SQL Server.

Note: Please close all the opened SQL Server Management Studio Instances or all the database connections except the one where you want to execute the above code otherwise it will not complete its execution.

ALTER DATABASE TEST_DB SET READ_COMMITTED_SNAPSHOT ON

Once you enable the READ_COMMITTED_SNAPSHOT Isolation Level then open another instance of SQL Server Management Studio. From the first instance of SSMS execute the Transaction 1 code and then from the second instance of SSMS execute the Transaction 2 code and you will notice that Transaction 2 is not blocked. It executed immediately and returns the committed data that is present in the database before Transaction 1 started. This is because Transaction 2 is now using the Read committed snapshot Isolation Level.

Let’s see if how to achieve the same thing using Snapshot Isolation Level instead of using the Read Committed Snapshot Isolation Level.

Please the following steps

Step1: Turn off the READ_COMMITTED_SNAPSHOT

ALTER DATABASE TEST_DB SET READ_COMMITTED_SNAPSHOT OFF

Here also you need to close all the opened database connections and also all the windows except the one where you want to execute the above code.

Step2: Enable the Snapshot Isolation Level at the database level

ALTER DATABASE TEST_DB SET ALLOW_SNAPSHOT_ISOLATION ON

Step3: Open two instances of SQL Server Management Studio.

From instance 1 execute the Transaction 1 code and from instance 2 execute the Transaction 2 code immediately and you will notice that Transaction 2 is not blocked. It executed immediately and returns the committed data that is present in the database before Transaction 1 started.

--Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

  UPDATE Products SET Quantity = 5 WHERE Id = 1001
  WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION

--Transaction 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
  SELECT * FROM Products WHERE Id = 1001 
COMMIT TRANSACTION

Here we achieve the same thing using both the read committed snapshot isolation level and snapshot isolation level. In the next article, we will discuss the differences between Snapshot Isolation and Read Committed Snapshot in SQL Server with examples.

In this article, I try to explain the Read Committed Snapshot Isolation Level in SQL Server with an example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.