Snapshot Transaction Isolation Level in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Snapshot Transaction Isolation Level in SQL Server

In this article, I will discuss the Snapshot Transaction Isolation Level in SQL Server with Examples. Please read our previous article discussing the Phantom Read Concurrency Problem and how to solve that problem in SQL Server with an example. 

What is Snapshot Transaction Isolation Level in SQL Server?

Snapshot Isolation in SQL Server allows transactions to work with a consistent snapshot of the database at the point the transaction started. This means that the transaction sees the data as it was at the beginning of the transaction, regardless of changes made by other transactions. This isolation level is particularly useful for applications that require high concurrency and read consistency without locking issues.

The Snapshot Transaction Isolation Level in SQL Server is a mechanism that provides version-based transaction isolation. When this isolation level is used, a transaction sees a consistent snapshot (picture) of the database at the point in time when the transaction started. This is achieved by storing a version of each row that a transaction modifies in a special structure called a version store located in the tempdb database.

Row Versioning: SQL Server implements Snapshot Isolation using row versioning. Whenever a row is updated under this isolation level, SQL Server keeps a copy of the original row before it was updated. This copy is stored in the tempdb database. Each version of a row is tagged with the transaction sequence number.

Sequence Number: A sequence number is a unique number given to your transaction that tells when the transaction is started. This Transaction Sequence Number is tagged with the row version in the tempdb. This helps SQL Server identify which version of the row should be visible to a transaction, based on when the transaction started.

How tempdb Stores Row Versions?

• Row Version Creation: When a row in a table is modified (e.g., updated, deleted) by a transaction, SQL Server creates a new version of this row before applying the modification. This version contains the state of the row as it was before the modification.
• Version Store in tempdb: This row version is then stored in a special structure within tempdb known as the version store. The version store is organized efficiently to manage and retrieve these row versions. Each versioned row in the version store is tagged with a unique version sequence number (XSN), which is important for identifying the correct version of a row when needed.
• Transaction ID and Sequence Number: Every transaction is assigned a unique transaction ID and a sequence number when it starts. This sequence number is tagged with the row version and is also used to determine which version of a row a transaction should see based on when the transaction started relative to when changes were made to the data.

Snapshot Isolation Real-Time Example:

Imagine you are reading a book but don’t want anyone to edit it while reading. Snapshot Isolation lets you do exactly that with a database. It’s like taking a picture of the data when you start your “reading” (or transaction) and ensures that you will see the database as it was in that picture, even if others are making changes. That means when your transaction reads some data if other transactions modify those data, then you will still see the original data.

• Row Versioning: Row versioning is like keeping a history of all changes made to each “sentence” (or row) in your book. If someone edits a sentence after you have taken your picture, the database keeps the original sentence for you to read, while the new version is available for others who take a picture later.
• Sequence Numbers Made Easy: A sequence number is a unique number given to your picture (or transaction) that tells the database exactly when you took it. This is how the database knows which version of each sentence to show you based on your specific point in time and the sequence number when you took the picture.

How They Work Together?

Starting a Transaction (Taking a Picture):

• When you start a transaction with Snapshot Isolation, SQL Server gives it a unique sequence number (like a timestamp on your picture).
• This sequence number is used to identify which version of the data you should see throughout your transaction.

Reading Data (Viewing the Picture):

• Whenever you read data, SQL Server looks at your sequence number and shows you the data as it was at that time. If the data has been changed after your transaction started, you’ll still see the old data, thanks to row versioning.
• This is like reading your book without seeing any of the edits made after you took your picture.

Concurrent Changes (Others Taking Their Pictures):

• While you are in your transaction, others might start their own transactions and make changes to the database. These changes might even involve the same data you are looking at.
• Thanks to row versioning, these changes don’t affect what you see. Each transaction gets its own “view” of the data, based on when it started. New changes are stored as new versions, which will only be visible to transactions that start after those changes are made.

Completing the Transaction:

• When you are done and commit your transaction, any changes you have made become part of the database’s history. Future transactions will see these changes if their sequence numbers come after yours.

Real-Time Example to Understand Snapshot Transaction:

Imagine you and a friend are looking at a list of books in a library database:

• You start your transaction at 10:00 AM (Snapshot taken with sequence number 1).
• Your friend starts a transaction, adds a new book at 10:01 AM, and then commits it (This change is stored with a sequence number 2).
• You look at the list again, and it is still within your transaction. You won’t see the new book your friend added because your snapshot was taken at 10:00 AM before the book was added.
• Only after you end your transaction and start a new one would you see the updated list, including the new book.

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 will discuss how to use the ALLOW_SNAPSHOT_ISOLATION option. In the next article, we will discuss the READ_COMMITTED_SNAPSHOT and the differences between them with an example.

ALLOW_SNAPSHOT_ISOLATION in SQL Server

ALLOW_SNAPSHOT_ISOLATION is a database-level setting in SQL Server that controls the availability of snapshot isolation within that database. When this option is set to ON, it allows transactions to use snapshot isolation to help prevent locking and blocking by providing a versioned view of data.

That means when a transaction reads data, it sees a consistent snapshot of the data as it existed at the start of the transaction, regardless of changes made by other transactions. This is particularly useful in high concurrency environments where transactions might otherwise be delayed by locks from other transactions.

Enabling snapshot isolation can help improve application performance by reducing locking and blocking, but it also requires additional tempdb resources because SQL Server needs to maintain versions of modified rows. Here’s how it works:

• When ALLOW_SNAPSHOT_ISOLATION is set to ON, SQL Server starts keeping a version of rows that were modified by transactions in a version store in the tempdb database.
• When a transaction runs under snapshot isolation, it sees the data as it was at the start of the transaction, not affected by other concurrent transactions. This is achieved by reading the row versions from the version store when necessary.
• Other transactions can modify the data concurrently without blocking the snapshot transaction, as the snapshot transaction is working with a consistent view of the data at a specific point in time.

To enable snapshot isolation in SQL Server, you can use the following SQL command:

ALTER DATABASE [YourDatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON;

However, turning on snapshot isolation does not automatically change the isolation level of existing transactions. Applications need to explicitly request snapshot isolation by setting the transaction isolation level using the SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before starting a transaction as follows:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
— Your transactional SQL operations here
COMMIT TRANSACTION;

Example to Understand Snapshot Isolation Level in SQL Server

Let us Understand the Snapshot Transaction Isolation Level in SQL Server with examples. First, execute the following SQL Script to create the ProductDB database and Products table with some dummy data.

CREATE DATABASE ProductDB;
GO

USE ProductDB;
GO

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Quantity INT
);

INSERT INTO Products (ProductID, ProductName, Quantity)
VALUES (1, 'Laptop', 10), (2, 'Tablet', 20);

To enable snapshot isolation in the above ProductDB database, please execute the following SQL command:

ALTER DATABASE ProductDB SET ALLOW_SNAPSHOT_ISOLATION ON;

Now, we will execute two transactions concurrently. Transaction 1 will start and read data, then Transaction 2 will modify and commit the data, and finally, Transaction 1 will read the data again under Snapshot Isolation.

• Transaction 1 (T1) – Snapshot Isolation: Starts and reads Quantity of ‘Laptop’ from the Products table.
• Transaction 2 (T2) – Default Isolation (for demonstration): Modifies Quantity of ‘Laptop’ and commits the change.
• Transaction 1 (T1) – Continues: Reads Quantity of ‘Laptop’ again under Snapshot Isolation.

Execution Step by Step:

Please execute the following transactions step by step:

Step:1 Enable Snapshot Isolation for Transaction 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- T1 reads the quantity of 'Laptop'
SELECT Quantity FROM Products WHERE ProductID = 1;

Assume it returns 10, which is the initial quantity.

Step2: Transaction 2 Modifies the Data

In another session or query window, use a different Isolation or Snapshot Isolation. Here, it we will use the default Read Committed Transaction Isolation.

BEGIN TRANSACTION;
-- T2 updates the quantity of 'Laptop'
UPDATE Products SET Quantity = 5 WHERE ProductID = 1;
COMMIT TRANSACTION;

Step3: Transaction 1 Reads the Data Again

Back in the first session where Transaction 1 is still open, execute the following code:

-- T1 reads the quantity of 'Laptop' again
SELECT Quantity FROM Products WHERE ProductID = 1;
COMMIT TRANSACTION;

Even after T2’s commit, T1 will still see the Quantity as 10 because it gets the data as it was at the start of the transaction, thanks to the row versioning in tempdb.

How Exactly It Works (Step-by-Step with the Example)

• Before Any Transaction: The Products table has a row for ‘Laptop’ with a Quantity of 10. No row versions exist in tempdb yet because no modifications have been made under Snapshot Isolation.
• T1 Starts (Snapshot Isolation): T1 begins with Snapshot Isolation. It reads the ‘Laptop’ quantity as 10. At this point, T1 is assigned with a unique sequence number, let us say 100. No data modifications have occurred yet, so no row versions are created.
• T2 Modifies the ‘Laptop’ Row: When Transaction T2 starts, it is also assigned with a unique sequence number, let say 101. Before Transaction T2 modifies the ‘Laptop’ row, SQL Server creates a copy of the original ‘Laptop’ row (with Quantity 10) and stores this row version in tempdb. This row version is tagged with the transaction sequence number, i.e., 101, indicating the point in time the row was modified.
• T1 Reads ‘Laptop’ Quantity Again: When T1 requests the ‘Laptop’ quantity again, SQL Server checks tempdb for any versioned rows of ‘Laptop’. It found one-row version with the sequence number 101, which is greater than 100. That means some other transaction updated the row after this transaction began. So, in this case, it will not read the data from the database. Rather than using the row version, it will construct the original data from the snapshot, and that original data (Quantity with 10) is returned.

Cleanup: The version store is periodically cleaned up by SQL Server. Versions of rows that are no longer needed by any transactions are removed to reclaim space and maintain efficiency.

Lost Update Example Using Snapshot Isolation Level:

Imagine a table named Accounts with two columns: AccountID and Balance. There are two transactions, T1 and T2, intending to update the same account balance based on its current balance.

Initial State: The products table has one product with ProductID = 1 and Quantity = 100.

Transaction T1 (Under Snapshot Isolation):

• Begins and reads the Quantity for ProductID = 1, seeing Quantity = 100.
• Plans to update the Quantity to 150.

Transaction T2 (Concurrently, also under Snapshot Isolation or another level):

• Begins and reads the Quantity for ProductID = 1, also seeing Quantity = 100.
• Plans to update the Quantity to 120.

How Snapshot Isolation Prevents Lost Update?

• T1 and T2 Read: Both transactions, T1 and T2, can read the initial Quantity without blocking each other, thanks to Snapshot Isolation providing each transaction with a consistent snapshot of the database as of the start of the transaction.
• T1 Updates the Quantity: When T1 updates the Quantity to 150, this change is not visible to T2, as T2 is working on a snapshot of the data that existed when it started. T1’s changes are written to the database, and the original version of the row (Quantity = 100) is stored in tempdb’s version store.

T2 Attempts to Update:

• If T2 is also under Snapshot Isolation when it tries to update the Quantity, the SQL Server checks if the row has been modified since T2’s snapshot was taken. Since T1 has modified the row, T2 will be informed of a conflict (a write conflict), preventing T2 from overwriting T1’s update. T2 must then handle this conflict, typically by retrying the transaction and applying its changes to the new Quantity of 150.
• If T2 is under a different isolation level (not using row versioning), the behavior would depend on the specific level and timing, but under Snapshot Isolation, the lost update is prevented by the mechanism described.

Example:

First, update the Product Quantity to 100 by executing the following statement:

UPDATE Products SET Quantity = 100 WHERE ProductID = 1;

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.

Transaction 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION

  SELECT Quantity FROM Products WHERE ProductID =1

  -- Delay the Transaction for 5 seconds
  WAITFOR DELAY '00:00:05'

  UPDATE Products SET Quantity = 150  WHERE ProductID =1
  SELECT Quantity FROM Products WHERE ProductID =1
COMMIT TRANSACTION

Transaction 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION

  SELECT Quantity FROM Products WHERE ProductID =1

  -- Delay the Transaction for 7 seconds
  WAITFOR DELAY '00:00:07'
  UPDATE Products SET Quantity = 120  WHERE ProductID =1

  SELECT Quantity FROM Products WHERE ProductID =1
COMMIT TRANSACTION

Here, you will see Transaction 1 will be completed successfully and Transaction 2 will be completed with the following error:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Products’ directly or indirectly in database ‘ProductDB’ 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.

Benefits of ALLOW_SNAPSHOT_ISOLATION

• Reduced Blocking: Read operations under snapshot isolation do not acquire shared locks, reducing blocking and deadlocks.
• Consistent Data View: Each transaction sees a consistent snapshot of the data as of the start of the transaction.
• Concurrency Improvements: Enhances system concurrency, especially in high-load environments where read-write conflicts are common.

Considerations of ALLOW_SNAPSHOT_ISOLATION

• TempDB Usage: Snapshot isolation uses TempDB to store row versions, which can increase the storage and performance overhead on TempDB.
• Application Compatibility: Ensure your application logic is compatible with snapshot isolation, as the behavior of transactions can differ from other isolation levels.

What are the Differences Between Serializable and Snapshot Isolation Levels in SQL Server?

Before understanding the difference between serializable and snapshot isolation levels in SQL servers, let’s first look at the following table. As you can see in the table below, both the Serializable Isolation Level and Snapshot Isolation Level have no concurrency side effects. 

Each Isolation Level has its unique approach to handling potential conflicts between transactions, impacting performance and consistency in different ways. Here’s a detailed comparison:

Serializable Isolation Level

• Lock-Based Concurrency Control: Serializable isolation uses a locking mechanism to ensure that any data read or written during a transaction cannot be modified until the transaction is completed. This means it locks the data rows involved in a transaction, preventing other transactions from reading, updating, or deleting these rows until the current transaction is committed or rolled back.
• Strictest Isolation Level: It is the highest isolation level defined by the SQL standard. It prevents dirty reads, lost updates, non-repeatable reads, and phantom reads, ensuring complete isolation from other transactions. The serializable isolation level guarantees that each transaction is completely isolated from others as if transactions were executed serially, one after the other. No other transaction can insert, update, or delete data that would affect the current transaction until it’s completed.
• Phantom Reads Prevention: It prevents all forms of concurrency issues, including dirty reads, non-repeatable reads, and phantom reads. A phantom read occurs when a transaction reads a set of rows that satisfy a WHERE condition and then, in a subsequent read, finds additional rows that satisfy the same condition due to another transaction’s insert operation.
• Performance Consideration: Due to its strict locking mechanism, the Serializable isolation level can lead to significant performance overhead, especially in systems with high levels of concurrent transactions. It can increase the likelihood of blocking and deadlocks, degrading system performance and responsiveness.

Snapshot Isolation Level

• Row-Versioning Concurrency Control: Unlike Serializable, Snapshot isolation employs a row-versioning mechanism that allows transactions to read the last committed version of data when the transaction started. This means it does not require locking data rows for reading, significantly reducing the likelihood of blocking and deadlocks.
• Non-Blocking Reads: Transactions under Snapshot isolation can read data without being blocked by other transactions. This isolation level allows a transaction to work with a consistent snapshot of the database, ensuring that the data seen by the transaction remains consistent throughout its execution.
• Phantom Reads Prevention: Snapshot isolation can prevent dirty reads and non-repeatable reads but does not inherently prevent phantom reads in all scenarios. However, because each transaction works with a snapshot of the data as it existed at the start of the transaction, the visibility of changes made by other transactions is controlled.
• Performance Consideration: Snapshot isolation can offer better performance and higher concurrency in many scenarios, especially in read-heavy workloads. Since it does not hold shared locks on read data, it allows more transactions to execute concurrently. However, it requires additional tempdb resources to store row versions, which can increase the storage and performance overhead.

Key Differences

• Concurrency Control Mechanism: Serializable uses locks and range locks to ensure isolation, while Snapshot uses row versioning.
• Performance and Scalability: Serializable can significantly impact performance and scalability due to locking and blocking. Snapshot can perform better in high-concurrency scenarios but requires more resources to maintain row versions.

In the next article, I will discuss the Read Committed Snapshot Isolation Level in SQL Server with Examples. 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, questions, or comments about this article.