Sunday, August 9, 2020

Snapshot Isolation Level in SQL Server with example


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


    DROP TABLE Product


create table Product

(ProdID int,

ProdName Varchar(50),

ProdPrice int


 insert into Product(ProdID,ProdName,ProdPrice)


( 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


 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

No comments:

Post a Comment