Snapshot Isolation

To run transactions in snapshot isolation mode, first, enable snapshot isolation level on the database and then set the isolation level on the transaction. To enable option snapshot isolation on the database:
Alter Database <<DatabaseName>>
Set allow_snapshot_isolation on

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='Lakers' where sno=2
waitfor delay '00:00:05'
commit

/*Retrieves only committed data from the table.Writers do not block readers.So, as long as the txn in session 1 is committed before the select statement in session 2 runs,the updated value is returned. If the session 1 txn is not committed by the time select in session 2 runs,it will return the initial values. But data read will always be consistent inside the transaction,even if it is changed another transaction.*/
--Session 2
Set Transaction Isolation Level Snapshot
Begin Tran
Select * from foobar
waitfor delay '00:00:05'
Select * from foobar
Commit

/*CON:Update conflicts can occur when the data that the current transaction is trying to update was modified by another transaction*/
--Session 1
Set Transaction Isolation Level Snapshot
Begin Tran
Select * from foobar
waitfor delay '00:00:05'

--Data read in the same transaction will always be consistent.
select * from foobar
--update conflict occurs,since the data was changed by another transaction.
Update foobar set sname='Heat' where sno=2
select * from foobar
Commit

--Session 2
Begin Tran
Update foobar set sname='Hawks' where sno=2
commit

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

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