SQL SERVER 2008 R2 Transaction Snapshot Isolation Level Not Working As Expected

Question

I created a database called 'test_isolation' and created a table 'person' with data

name    age
----    ---
test1    1
test2    2
test3    3
test4    4
test5    5
test6    6

Now the database is altered to allow snapshot isolation in session1

ALTER DATABASE test_isolation
SET ALLOW_SNAPSHOT_ISOLATION ON
GO 

Now I create a transaction in session 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRAN
SELECT * FROM PERSON
GO
DELETE FROM PERSON WHERE name = 'test6'
GO
SELECT * FROM PERSON
GO

The results are as expected. (Note we haven't committed this transaction yet!)

Now I execute the following query in session 3

SELECT * FROM PERSON

The query in session 3 keeps on running infinitely which means the table is locked.

If I go back to session 2 and commit the transaction.. I'm able to run the query on session 3 and the results are as expected.

Transaction isolation level SNAPSHOT is not supposed lock the table right? Am I doing something wrong or my understanding of transaction SNAPSHOT isolation is wrong?

Please help..

Answer 1

You must explicitly declare SET TRANSACTION ISOLATION LEVEL SNAPSHOT in session three, otherwise session 3 will still operate as READ_COMMITTED and block on the update.

This option can also be set at the database level to replace READ_COMMITTED with SNAPSHOT.

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON