Snapshot Isolation Level in SQL Server with example

Snapshot

Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.

 IF OBJECT_ID('Product') is not null

    begin

    DROP TABLE Product

    end

create table Product

(ProdID int,

ProdName Varchar(50),

ProdPrice int

)

 insert into Product(ProdID,ProdName,ProdPrice)

values

( 1,'Black Shirt',1000),

( 2,'White Suits',2000),

( 3,'Red Kurta',700),

( 4,'Half Shirt',1700)

Run both the sessions side by side

Example 1:

 Session 1:

set transaction isolation level snapshot

begin tran

select * from Product

waitfor delay '00:00:10'

select * from Product

rollback

 Session 2:

 insert into Product(ProdID,ProdName,ProdPrice)

values ( 8,'Red Pant',1500)

Output from session 1:

Session 2 will execute without any delay as Session 1 will not lock the table.

Click on Below links to see the Different types of isolation levels with example

• Read UnCommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot