If the transaction isolation level is ‘Read Committed‘ and the database has Read Committed Snapshot enabled, then the transactions automatically run under “Read Committed Snapshot Isolation“. To Enable the option on database:
Alter Database <<DatabaseName>>
Set read_committed_snapshot on
with RollBack Immediate
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
Set Transaction Isolation Level Read Committed
Begin Tran
Select * from foobar
waitfor delay '00:00:05'
--This will read the data committed in session 2
select * from foobar
--updates the data
Update foobar set sname='Lakers' where sno=2
select * from foobar
Commit
--Session 2
Set Transaction Isolation Level Read Committed
Begin Tran
Update foobar set sname='Clippers' where sno=2
commit
SQL Server Repository 