Back to: SQL Server Tutorial For Beginners and Professionals
Difference between Snapshot Isolation and Read Committed Snapshot
In this article, I am going to 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 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)
Update Conflicts:
The Snapshot Isolation Level in SQL Server is vulnerable to update 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 and 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 enabled the Snapshot Isolation Level, then 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 the Transaction 2 is blocked until the Transaction 1 is completed its execution. And when Transaction 1 completes its execution, then the Transaction 2 raises an update conflict which will terminate the transaction and rolls back with the following error as shown in the below image.
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 Read Committed Snapshot Isolation Level by using the following steps and see what happens.
Step1: First, disable Snapshot Isolation for the TEST_DB database using the following command
ALTER DATABASE TEST_DB SET ALLOW_SNAPSHOT_ISOLATION OFF
Step2: 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
Step3:
Once you set the Read Committed Snapshot Isolation Level then 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 the Transaction 2 is blocked until the Transaction 1 is completed its execution and when the Transaction 1 completed 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, then you can very easily make your application to use the Read Committed Snapshot Isolation Level without requiring any change to the application at all. What you need to do is just 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 as 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. Step1 first one transaction1 and then step2 from transaction2 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 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 am going to discuss Deadlocks in SQL Server with examples. Here, 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.