Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

Snapshot Isolation Level

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
8 Feb 2018CPOL3 min read 15.3K   2  
Snapshot Isolation Level in SQL Server

In this article, we are going to see details regarding SNAPSHOT isolation level on SQL Server.

Default isolation level of SQL server is READ COMMITTED when rows have been updated in transaction and current transaction is not committed yet. READ COMMITTED locks that particular row for that current transaction. READ COMMITTED isolation level prevents other SQL connections to read those particular locked rows. READ COMMITTED makes read requests to wait until current transaction completes. READ COMMITTED prevents dirty read (reading uncommitted data).

There is isolation level named SNAPSHOT. SNAPSHOT maintains committed row versions in temdb. So that when rows have been updated in transaction and current transaction is not committed yet. Other SQL connections can read last committed version of those rows. No dirty reads (read uncommitted data). No wait.

Let’s create sample data to see SNAPSHOT isolation level in action:

SQL
--******************* sample data to play with ******************************
USE master
GO

DROP DATABASE SampleDatabase
GO

CREATE DATABASE SampleDatabase
GO

USE SampleDatabase
GO

CREATE TABLE Employee (
  Id int NOT NULL,
  FirstName varchar(100) NOT NULL,
  LastName varchar(100) not null
)
GO

INSERT INTO Employee
  VALUES (1,'Hardik','Oza')
INSERT INTO Employee
  VALUES (2, 'Kishan','Bhatt')
INSERT INTO Employee
  VALUES (3, 'Dhaval','Patel')
GO
--*********************************************************************************

Data inside Employee table:

SQL
SELECT
    *
FROM Employee

You can turn on SNAPSHOT isolation level by configuring database.

SQL
ALTER DATABASE SampleDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
GO

After configuring database for SNAPSHOT isolation level, here SQL connection A is updating employee FirstName whose id is 1, but as you can see here, transaction is not committed yet.

SQL
BEGIN TRANSACTION

  UPDATE Employee
  SET FirstName = 'Harshit'
  WHERE Id = 1

You have to set transaction isolation level first. Any SQL connection can read latest committed version of rows within transaction while those rows are being updated in another connection and in another transaction.

SQL connection B sets the transaction isolation level to SNAPSHOT from READ COMMITTED. Connection B reads row with id 1 within transaction. Same row which is being updated in connection A. Connection B can see the last committed version of row.

SQL
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

  SELECT
    *
  FROM Employee
  WHERE Id = 1

Now connection A commits the transaction which means changes regarding row with id 1 is committed now.

SQL
COMMIT TRANSACTION

Now in connection B, we are going to read row with id 1 again. OMG, still we got the old value of row. But connection A committed its changes regarding row with id 1. What happened? Actually, transaction uses one snapshot (committed version of rows) till its commit/rollback (whatever end of transaction), whether new snapshot (new version of committed rows) is available or not. Any committed changes occurred in the middle of transaction by another transaction do not reflect in read of that current transaction.

SQL
  SELECT
    *
  FROM Employee
  WHERE Id = 1

COMMIT TRANSACTION

As you can see in connection B, we have to explicitly specify the isolation level to snapshot for session in order to get access versioned rows. READ_COMMITTED_SNAPSHOT database option allows to get access versioned rows without specifying isolation level to snapshot explicitly for session.

You can turn on READ_COMMITTED_SNAPSHOT database option by configuring database and replace the default READ COMMITTED behavior with SNAPSHOT.

SQL
ALTER DATABASE SampleDatabase SET READ_COMMITTED_SNAPSHOT ON
GO

Here again, SQL connection A is updating employee FirstName whose id is 1, but as you can see here, transaction is not committed yet.

SQL
BEGIN TRANSACTION

  UPDATE Employee
  SET FirstName = 'Parth'
  WHERE Id = 1

Again SQL connection B reads row with id 1, same row which is being updated in connection A, without setting transaction isolation level to snapshot, connection B can see the last committed version of row as connection A's transaction is not committed yet.

SQL
SELECT
  *
FROM Employee
WHERE Id = 1

Don’t forget to commit the transaction of connection A.

SQL
COMMIT TRANSACTION

Thank you for reading!!!

Happy engineering!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
India India
Software Design Expert with 4+ years of experience working in variety of industries. Very creative problem solver with strong interpersonal skills, Believe in flawless and robust system design, Known for consistently coming up with original ideas and being able to clearly communicate them.

Email : suraniviral@live.com

Comments and Discussions

 
-- There are no messages in this forum --