Snapshot Isolation Level

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:

--******************* 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:

SELECT
    *
FROM Employee

You can turn on SNAPSHOT isolation level by configuring database.

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.

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.

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.

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.

  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.

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.

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.

SELECT
  *
FROM Employee
WHERE Id = 1

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

COMMIT TRANSACTION

Thank you for reading!!!

Happy engineering!!!