Difference between Snapshot Isolation and Read Committed Snapshot

Back to: SQL Server Tutorial For Beginners and Professionals

Difference between Snapshot Isolation and Read Committed Snapshot

In this article, I will discuss the difference between Snapshot Isolation and Read Committed Snapshot in SQL Server with examples. Please read the following two articles before proceeding to this article.

https://dotnettutorials.net/lesson/snapshot-isolation-level-sql-server/ – Here, we discussed the Snapshot Isolation Level with an example in SQL Server.

https://dotnettutorials.net/lesson/read-committed-snapshot-isolation-level/ – Here, we discussed the Read Committed Snapshot Isolation Level in SQL Server with examples.

Please have a look at the below table.

Let’s understand the above points in detail.

We are going to use the following Products table in this demo.

Please use the SQL script below 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)

Update Conflicts: 

The Snapshot Isolation Level in SQL Server is vulnerable to updating the conflicts, whereas the Read Committed Snapshot Isolation Level is not. So, when a transaction is running under the snapshot isolation level and if that transaction is trying to update the same data, which is already updated by another transaction at the same time, then in such cases, an update conflict occurs. The transaction terminates and rolls back with an error. 

Let us understand this with an example.

First, Enable the Snapshot Isolation Level at the database level by executing the following SQL Command.

ALTER DATABASE TEST_DB SET ALLOW_SNAPSHOT_ISOLATION ON

Once you have enabled the Snapshot Isolation Level, Open 2 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 has completed its execution. When Transaction 1 completes its execution, then Transaction 2 raises an update conflict, which will terminate the transaction and roll back with the following error, as shown in the image below. 

Example code is given below:

--Transaction 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
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
  UPDATE Products SET Quantity = 8 WHERE Id = 1001
COMMIT TRANSACTION

Now, let’s do the same thing using the Read Committed Snapshot Isolation Level. We can do this by using the following steps and see what happens.

Step 1: First, disable Snapshot Isolation for the TEST_DB database using the following command

ALTER DATABASE TEST_DB SET ALLOW_SNAPSHOT_ISOLATION OFF

Step 2: In the Second step enable the Read Committed Snapshot Isolation Level at the database level by using the following SQL command. Again, to successfully execute the below command, you need to close all the existing connections.

ALTER DATABASE TEST_DB SET READ_COMMITTED_SNAPSHOT ON

Step 3: 

Once you set the Read Committed Snapshot Isolation Level, open 2 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. you will notice that Transaction 2 is blocked until Transaction 1 has completed its execution, and when Transaction 1 completes its execution, then Transaction 2 also completes its execution successfully without any update conflict. 

--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
UPDATE Products SET Quantity = 8 WHERE Id = 1001
COMMIT TRANSACTION

Impact on Existing Application: 

If your application is running under the default, Read Committed Isolation Level. You can easily make your application use the Read Committed Snapshot Isolation Level without requiring any change to the application. You need to turn on the READ_COMMITTED_SNAPSHOT option in the database, which will ultimately change the Read Committed Isolation Level to use the row versioning when reading the committed data.

Impact on Distributed Transactions: 

The Read Committed Snapshot Isolation Level also works with the distributed transactions in SQL Server, whereas the Snapshot Isolation Level does not support working with the distributed transactions.

Read Consistency: 

The Read Committed Snapshot Isolation Level in SQL Server provides the statement-level read consistency, whereas the Snapshot Isolation Level provides the transaction-level read consistency.

First, execute the following statement to update the quantity to 10 for the product whose ID is 1001.

UPDATE Products SET Quantity = 10 WHERE Id = 1001

Let us understand this with an example. Open two instances of SQL Server Management Studio and Run the Transaction1 code from instance 1 and run Transaction 2 codes from instance 2. The important point here is that you need to run the code step by step, i.e., Step 1 first, transaction 1 and then Step 2 from transaction 2, and so on. The steps are mentioned in the code itself.

Transaction1 Code:

--Transaction1
-- Step1 Start
SELECT Quantity FROM Products WHERE Id = 1001	
-- Result : 10
-- Step1 End

-- Step3 Start
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
UPDATE Products SET Quantity = 5 WHERE Id = 1001
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 5
-- Step3 End

-- Step 5 Start
COMMIT TRANSACTION
-- Step 5 End

-- Step7 Start
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 5	
-- Step7 End

Transaction2 Code:

--Transaction2
-- Step2 Start
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 10	
-- Step2 End

-- Step4 Start
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION	
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 10
-- Step4 End

-- Step6 Start
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 5
COMMIT TRANSACTION
-- Step6 End

-- Step8 Start
SELECT Quantity FROM Products WHERE Id = 1001	
-- Result : 5
-- Step8 End

As you can see, Transaction 2 has 2 select statements, and you will notice that both of these select statements return different data. This is because the Read Committed Snapshot Isolation Level returns the last committed data before the select statement began and not the last committed data before the transaction began. 

Now, let’s use the Snapshot Isolation Level in the same example and see what happens.

First, update the quantity as 10 by executing the following command.

UPDATE Products SET Quantity = 10 WHERE Id = 1001

Then, Enable the Snapshot Isolation Level at the database level by executing the following query.

ALTER DATABASE TEST_DB SET ALLOW_SNAPSHOT_ISOLATION ON

Then, modify the code as shown below to use the snapshot isolation level.

Transaction1 Code:

--Transaction 1
-- Step1 Start
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 10	
-- Step1 End

-- Step3 Start
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE Products SET Quantity = 5 WHERE Id = 1001
SELECT Quantity FROM Products WHERE Id = 1001
-- Result : 5
-- Step3 End

-- Step 5 Start
COMMIT TRANSACTION
-- Step 5 End

-- Step7 Start
SELECT Quantity FROM Products WHERE Id = 1001	
-- Result : 5
-- Step7 End

Transaction2 Code:

--Transaction 2
-- Step2 Start
SELECT Quantity FROM Products WHERE Id = 1001
-- Result: 10	
-- Step2 End

-- Step4 Start
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION	
SELECT Quantity FROM Products WHERE Id = 1001
-- Result: 10
-- Step4 End

-- Step6 Start
SELECT Quantity FROM Products WHERE Id = 1001
-- Result: 10
COMMIT TRANSACTION
-- Step6 End

-- Step8 Start
SELECT Quantity FROM Products WHERE Id = 1001	
-- Result: 5
-- Step8 End

In the above example, both the select statements of Transaction 2 return the same data. This is because the Snapshot Isolation Level in SQL Server returns the last committed data before the transaction began and not the last committed data before the select statement began. 

In the next article, I will discuss Deadlocks 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 needs. I would like to have your feedback. Please post your feedback, questions, or comments about this article.