Back to: SQL Server Tutorial For Beginners and Professionals
Snapshot Transaction Isolation Level in SQL Server
In this article, I am going to discuss the Snapshot Transaction Isolation Level in SQL Server with Examples. 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, let’s 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 the Serializable Isolation Level, you can also use the 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 concurrent transactions.
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
- ALLOW_SNAPSHOT_ISOLATION
- READ_COMMITTED_SNAPSHOT
In this article, I am going to discuss how to use the 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.
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: 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 the 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 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 Transactions 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 Transaction 2 is blocked until Transaction 1 completes its execution. When Transaction 1 completes, then Transaction 2 fails with the following error message to prevent the 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 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 Examples. Here, 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.
