Sql-server – Blocking continues after enabling snapshot isolation

blockingisolation-levelsnapshot-isolationsql server

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?

Best Answer

If your intention is to avoid readers from blocking writers and visa-versa in the default READ_COMMITTED isolation level, turn on the READ_COMMITTED_SNAPSHOT database option. This will cause row versioning instead of locking to be used to implement statement-level read consistency.

Although often confused, the ALLOW_SNAPSHOT_ISOLATION option is not related to READ_COMMITTED_SNAPSHOT. ALLOW_SNAPSHOT_ISOLATION allows the separate SNAPSHOT isolation level to be used to provide multi-statement read consistency but code changes are required to use it. The READ_COMMITTED_SNAPSHOT option provides statement-level read-consistency without code changes, although one should be aware of the implications the locking behavior have on apps that rely upon locking.

Related Question