On database hosted on a SQL Server 2012 instance, I have enabled
ALLOW_SNAPSHOT_ISOLATION and verified the state as
SELECT snapshot_isolation_state_desc,name from sys.databases
However, in 2 separate sessions, if I run a long running select with
TABLOCK in the 1st and an
UPDATE in the 2nd (or vice versa), whichever query starts first blocks the second query (as per
select * from sys.dm_exec_requests , both queries have a transaction isolation level of read committed (2)
As per my understanding, with snapshot isolation on, tempdb usage should increase however blocking should not occur in this situation. Am I missing some configuration steps to achieve this behaviour?