SNAPSHOT isolation level in SQL server

SNAPSHOT Isolation is a row-versioning-based isolation level in SQL Server that provides a transactionally consistent view of data as it existed at the start of the transaction. Instead of using Shared Locks for reads, SQL Server stores previous row versions in TempDB and allows readers to access those versions. This eliminates reader-writer blocking while preventing Dirty Reads, Non-Repeatable Reads, and Phantom Reads. SNAPSHOT offers high concurrency and consistent results, making it useful for reporting and long-running transactions. However, it increases TempDB usage and can generate update conflicts when two transactions attempt to modify the same row.

Benefits of this isolation level

Ø  No Dirty Reads

Ø  No Non-Repeatable Reads

Ø  No Phantom Reads

Ø  Readers do not block writers

Ø  Writers do not block readers

SNAPSHOT uses row versioning. When a row is updated then Original Row copied to TempDB Version Store and Update applied to current row. Readers access TempDB version store instead of waiting for locks.

Enable SNAPSHOT Isolation

We need to Enable alter the database to support the SNAPSHOT support:

ALTER DATABASE SNAPSHOT_DEMO SET ALLOW_SNAPSHOT_ISOLATION ON;

It is enabled on database level.

To use it on the session level we use SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Let’s see the demo

Creating a table and inserting some records

CREATE TABLE Employee (     ID INT PRIMARY KEY,     Name VARCHAR(50),     Salary MONEY );  INSERT INTO Employee VALUES (1,'Bagesh',50000), (2,'Rajesh',50000), (3,'Ganesh',50000), (4,'Mahesh',50000);

Session 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN; SELECT * FROM Employee WHERE ID = 1; --commit

Transaction is still open

Session 2

BEGIN TRAN; UPDATE Employee SET Salary = 70000 WHERE ID = 1; --COMMIT;

Updating the record here also we have not yet committed the transaction.

Session 3

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SELECT * FROM Employee WHERE ID = 1;

It is not blocking the read. Also, we are not getting the uncommitted data. It prevents the dirty read.

If we run the session 1 still, we will get the same old value.

We are getting the old same value. It prevents the Non-Repeatable Read.

Now we are committed session 2 and after run the query of session 3.

Running the session 1.

Here still we are getting the old value because transaction is not yet completed for this session. Now committing this open transaction and running the same query.

Now we are getting the updated value.

This isolation level also prevents the phantom read.

Let’s see the below example.

Session 1

Running the below query

Transaction is not yet committed.

In session 2

Inserting a new record

INSERT INTO Employee VALUES (5,'Rahim',50000);

 

Record inserted successfully.

session 3

run the below query

Now we are running the session 1

Still here we are getting the 4 records. Until we commit this open transaction we will get the previous value only. This prevents the Phantom Read.

Now committing the open transaction and running the Same query.

Now getting the updated value.

Advantage using this isolation level

Ø  Eliminates Reader-Writer Blocking

Ø  Prevents Dirty Reads

Ø  Prevents Non-Repeatable Reads

Ø  Prevents Phantom Reads

Ø  Excellent for Reporting Systems

Ø  Higher Concurrency

Ø  Consistent Point-in-Time View

Disadvantage using this isolation level

Ø  TempDB Usage Increases : This is the biggest disadvantage. Every update stores old row versions in TempDB.

Ø  Version Store Cleanup Overhead

Ø  Update Conflict Errors

Ø  Additional TempDB I/O

Ø  Long Running Transactions Are Dangerous

Ø  More Memory and CPU Usage

Ø  Not Ideal for Extremely Write-Heavy Systems