Read Committed Isolation

General 1 Minute


IF Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar
Values(1,'Spurs'),(2,'Rockets'),(3,'Pelicans')

/*PRO: Read Committed does not allow Dirty Reads.*/
--Session 1
Begin Tran
Update foobar set sname='Bobcats' where sno=3
Waitfor Delay '00:00:05'
Commit
--Session 2
Set Transaction Isolation Level Read Committed
Begin Tran
--Data is not read until the transaction either committed or Rolled back on Session 1.
Select * from Foobar
Commit
/*CON: Read Committed allows Phantom Reads.*/
--Session 1
Set Transaction Isolation Level Read Committed
Begin Tran
Select * from Foobar
Waitfor Delay '00:00:05'
--Phantom Reads(Data read in the same transaction changes)
Select * from Foobar
commit
--Session 2
Set Transaction Isolation Level Read Committed
Begin Tran
Update foobar set sname='Celtics' where sno=3
Commit

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published