SQL Server Isolation Level with Example

SQL Server isolation levels are used to define the degree to which one transaction must be isolated from resource or data modifications made by other concurrent transactions. The different Isolation Levels are:

1. ReadUncommitted

2. ReadCommitted

3. RepeatableRead

4. Serializable

5. Snapshot

Read Committed is the default isolation level. However, it can be changed from Query Window as well as Management Studio Tools.

Execute below scripts to create table and insert some records to use in examples for each isolation.

 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)

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

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

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