patternsqlMinor
Blocking continues after enabling snapshot isolation
Viewed 0 times
continuesafterisolationblockingsnapshotenabling
Problem
On database hosted on a SQL Server 2012 instance, I have enabled
However, in 2 separate sessions, if I run a long running select with
Looking at
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?
ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using SELECT snapshot_isolation_state_desc,name from sys.databasesHowever, 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?
Solution
If your intention is to avoid readers from blocking writers and visa-versa in the default
Although often confused, the
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.Context
StackExchange Database Administrators Q#137911, answer score: 8
Revisions (0)
No revisions yet.