Snapshot Transaction Isolation Level in SQL Server

Snapshot Transaction Isolation Level in SQL Server

In this article, I am going to discuss the Snapshot Transaction Isolation Level in SQL Server with an example. Please read our previous article before proceeding to this article where we discussed the Phantom Read Concurrency Problem and how to solve that problem in SQL Server with an example. 

Before understanding the Snapshot Transaction Isolation Level in SQL Server, lets first have a look at the following table. As you can see in the below table, just like the serializable isolation level, the snapshot isolation level also does not have any concurrency side effects. So instead of Serializable Isolation Level, you can also use the Snapshot Isolation Level in SQL Server.

Snapshot Isolation Level in SQL Server

What is the difference between serializable and snapshot isolation levels in SQL Server?

The Serializable Isolation Level in SQL Server is implemented by acquiring a range lock on the data returned by the transaction. That means the resources are being locked for the duration of the current transaction. This isolation level does not have any concurrency problems but the problem is it decreases the number of the concurrent transaction. 

On the other hand, the Snapshot Isolation Level in SQL Server doesn’t acquire any lock on the data returned by the transaction instead it maintains versioning in the tempdb system database. As the snapshot isolation level does not lock the resources, it can significantly increase the number of concurrent transactions while providing the same level of data consistency as the serializable isolation level provides.

Types of Snapshot Isolation Level in SQL Server:

In SQL Server, we can use the Snapshot Isolation Level in two ways. They are as follows

  1. ALLOW_SNAPSHOT_ISOLATION
  2. READ_COMMITTED_SNAPSHOT

In this article, I am going to discuss how to use ALLOW_SNAPSHOT_ISOLATION option and in the next article, we will discuss the READ_COMMITTED_SNAPSHOT and the differences between them with an example.

ALLOW_SNAPSHOT_ISOLATION:

Let us understand the ALLOW_SNAPSHOT_ISOLATION with an example. We are going to use the following Employees table for this example. 

Snapshot isolation level in SQL Server

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)
Example: Using Serializable Isolation Level

Let us first use the Serializable Isolation Level. Open 2 instances of SQL Server Management Studio. From the first instance execute Transaction 1 code and from the second instance execute the Transaction 2 code. Notice that Transaction 2 is blocked until Transaction 1 is completes its execution. 

--Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

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

COMMIT TRANSACTION

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT * FROM Products WHERE Id = 1001
Example: Using the Snapshot Isolation Level:

Let’s first update the data as it was before

UPDATE Products SET Quantity = 10 WHERE Id = 1001

Now change the transaction isolation level of Transaction 2 to snapshot. To set snapshot transaction isolation level in SQL Server, it must first be enabled at the database level, and then set the transaction isolation level to snapshot as shown below.

-- Transaction 2
-- Enable the snapshot isolation for the database
-- Provide your database name
ALTER DATABASE TEST_DB 
SET ALLOW_SNAPSHOT_ISOLATION ON

-- Set the transaction isolation level to snapshot
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM Products WHERE Id = 1001

From the first instance execute the Transaction 1 code and from the second instance, execute the Transaction 2 code. And you will see that the Transaction 2 is not blocked and returns the data from the database as it was before Transaction 1 has started. 

Modifying the data with Snapshot Isolation Level: 

Let’s look at the below example and see what happens:

We have two transactions, let’s say Transaction1 and Transaction2, and both the transaction tries to update the same data at the same time but one of the Transaction uses Snapshot Isolation Level.

In the below example, Transaction1 starts first and it is updating the Quantity of the product Mobile to 5. At the same time, Transaction 2 is started which is using snapshot isolation level and also updating the same data.

Notice that the Transaction 2 is blocked until the Transaction 1 completes its execution. When Transaction 1 completes, then Transaction 2 fails with the following error message to prevent Lost Update Concurrency Problem.

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Products’ directly or indirectly in database ‘TEST_DB’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

If the Transaction 2 was allowed to continue its execution, then it would have changed the Quantity of the product Mobile to 8 and when Transaction 1 completes it overwrites Quantity to 5, which means we have lost an update. To complete the work that Transaction 2 is doing we will have to rerun the transaction. 

Below is the complete code.
-- Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
    WAITFOR DELAY '00:00:15'
  UPDATE Products SET Quantity = 5 WHERE Id=1001	
COMMIT TRANSACTION

-- Transaction 2
-- Enable the snapshot isolation for the database
-- Provide your database name
ALTER DATABASE MVC_DB 
SET ALLOW_SNAPSHOT_ISOLATION ON

-- Set the transaction isolation level to snapshot
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

UPDATE Products SET Quantity = 5 WHERE Id=1001

In the next article, I am going to discuss the Read Committed Snapshot Isolation Level in SQL Server with an example.

SUMMARY

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

Leave a Reply

Your email address will not be published. Required fields are marked *