On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION
and verified the state as ON
using
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 sp_who2
)
Looking at
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?