Repeatable Read Isolation

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:Repeatable Read does not allow Phantom Reads.*/
--Session 1
Set Transaction Isolation Level Repeatable Read
Begin Tran
Select * from Foobar
Waitfor Delay '00:00:05'
Select * from Foobar --No Phantom Reads
Commit
--Session 2
Set Transaction Isolation Level Repeatable Read
Begin Tran
Update foobar set sname='Bobcats' where sno=3
Commit

/*CON:Repeatable Read allows Phantom Inserts.*/
--Session 1
Set Transaction Isolation Level Repeatable Read
Begin Tran
Select * from Foobar
Waitfor Delay '00:00:05'
Select * from Foobar --Phantom Inserts
commit
--Session 2
Set Transaction Isolation Level Repeatable Read
Begin Tran
Insert into foobar Values(4,'Suns')
Commit

Advertisement

Read Committed Isolation


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

Read UnCommitted Isolation

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')
–Session 1
Begin Tran
Update foobar set sname='Bobcats' where sno=3
Waitfor Delay '00:00:03'
rollback
–Session 2
Set Transaction Isolation Level Read Uncommitted
Select * from Foobar–Dirty Read here.
Waitfor Delay '00:00:05'
Select * from Foobar