SNAPSHOT Isolation Level

So far we have saw following Isolation Levels and read phenomenon that exists with them

1. Read Uncommitted

2. Read Committed

3. Repeatable Read

4. Serializable

With the above series of post we saw that each isolation has different read phenomenon, the above isolation levels exists in SQL Server 2000 and higher version. From SQL Server 2005 onwards we have another isolation level which is “SNAPSHOT ISOLATION LEVEL”.

Since in our last blog over Serializable we saw that, with Serializable Isolation Level we can prevent Dirty, Non-Repeatable and Phantom Reads. So what other things Snapshot can do ?

Let see the behavior of  Snapshot isolation level.

Create Database From Following Script

IF Exists(SElect * From Sys.databases where name='DBCheckIsoLevel')
Begin
Drop Database DBCheckIsoLevel
End
Go

Create Database DBCheckIsoLevel
Go

Use DBCheckIsoLevel
Go

Before using Snapshot Isolation Level we need to allow database to use Snapshot isolation level.

Check whether database is allowed to use Snapshot Isolation level or Not,

Select snapshot_isolation_state_desc from sys.databases Where name='DBCheckIsoLevel'

Go

IF the Value returned by Above Query  is “OFF” means Snapshot Isolation Is Not Allowed, If Returned “ON”  Means  Allowed.

Since Query Above Returned value “OFF” Then we need to allow snapshot isolation, Use following query to allow snapshot isolation on a database.

Alter Database DBCheckIsoLevel Set Allow_Snapshot_Isolation On

Go

Check The Status Now

Select snapshot_isolation_state_desc from sys.databases Where name='DBCheckIsoLevel'

Go

Should return “ON”

---Script To Create Test Table

Use DBCheckIsoLevel

Go

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go

Id	Name	EncodedDate
1	Pankaj	2011-07-28 20:50:53.247
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

Now, Open a Connection At Query Window 1

Query Window 1

Set Transaction Isolation Level Snapshot
Begin Tran
Update CheckIsolationLevel Set Name='Pankaj Arya' Where Id=1

--Note: The Transaction is Still Pending.

 

Query Window 2

Set Transaction Isolation Level Snapshot
Select * From CheckIsolationLevel Where Id=1

As You Can See Below Record Is Returned At Query Window 2

Id	Name	EncodedDate
1	Pankaj	2011-07-28 20:50:53.247

Surprisingly, We saw that transaction at query window 2 was not locked  by Pending transaction at query window 1 ( which is working same as Read Uncommitted Isolation Level) but here the main thing is that it did not return the updated value rather it returned old value.

Lets Go To Query Window 1, We have one pending transaction at Query Window 1

Let Fire The Following Query At Query Window 1

Query Window 1

--Continue after pending transaction

Select * From CheckIsolationLevel Where Id=1

Id	Name	EncodedDate
1	Pankaj Arya	2011-07-28 20:50:53.247

 

We noted that Query Window 1 result has been changed from “Pankaj” To “Pankaj Arya” But Similar Query At Query Window 2 Returned Old result. So now the question arises is where the changes of Query Window 1 are taking place ?.

So This is The Special Feature provided by snapshot isolation where modification or data inserts within snapshot isolation level creates row version in tempdb.

So It is clear in snapshot isolation level reads are not locked or blocked by writes.

But What about Write with Write ??

Now Fire The Following Query At Query Window 2 , Remember That Query Window 1 has a pending transaction.

Update CheckIsolationLevel Set Name='Pankaj Kumar' Where Id=1

--You will see that the above Query is blocked by Query at Query Window 1

So It is clear that Write with Write in Snapshot Isolation Level is Blocked.

 

 

Ok Let See Another Case Under Snapshot Isolation

Rollback Transaction At Query Window 1 and Run the Table Script to Re-Create and Fill Data.

Query Window 1

Set Transaction Isolation Level Snapshot
Begin Tran
Select * From CheckIsolationLevel Where Id=2

--Output ":

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247

--Note Transaction is Still Pending

Query Window 2

Update CheckIsolationLevel Set Name='Satosh Panday' Where Id=2

--Note : Above Query Updates the Data. Also note that query runs without transaction and under Read_Committed Isolation Level.

Let Go Back to Query Window 1 where You have 1 pending transaction and run the following Query.

Update CheckIsolationLevel Set Name='Satosh Kumar Panday' Where Id=2

 

So you can see that above query at Query Window 1 raises error. Due to Update Conflict and rollback the entire transaction. These conflict error is a problem with snapshot isolation and one must handle there error while writing queries.

Hope Above Detail Might have given you basis info about Snapshot isolation.