Sunday, August 9, 2020

Read Uncommitted isolation level in SQL Server with example

Read Uncommitted

Transactions running at this level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Also, transactions are not blocked by exclusive locks at the time of data modification, thus allowing other transactions to read the modified data which is not yet committed.

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

 IF OBJECT_ID('Product') is not null




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)

Open two sessions

Run both the sessions side by side

Session 1:

begin tran

update Product set ProdPrice=999 where ProdID=1

waitfor delay '00:00:15'


Session 2:

set transaction isolation level read uncommitted

select ProdPrice from Product where ProdID=1



Session 2 is executed after Session 1 DML operation before transaction rollback.

So 999 is populated instead of 1000.

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

No comments:

Post a Comment