Often content about versioning just states that you need to turn both options on and they don’t describe that they’re really distinct environments.

Even the combination of options ( that a database can really be configured into one of four possible states ) is not very well known. To be honest, I’ve been wanting to get that “simple” view of the locking world written down for a while. Without snapshot transaction isolation level requested, statements will reconcile to the point in time that the statement started and when snapshot isolation is requested, then all statements will reconcile to the point in time that the transaction began.

BOTH read_committed_snapshot and allow_snapshot_isolation set.

Every read will reconcile to the point in time when the TRANSACTION started. For those that request snapshot isolation, this provides transaction-level read consistency.

ONLY allow_snapshot_isolation set ( connections DEFAULT to read committed using LOCKING – because of the absence of read_committed_snapshot ).

Every read will reconcile to the point in time when the STATEMENT started. This provides statement-level read consistency.

ONLY read_committed_snapshot set ( connections default to read committed using versioning no other code changes needed for read committed statements ).

No options set (connections default to read committed using locking).

Here you’ll have all the overhead of versioning without anyone using it unless you make code changes for snapshot isolation.)Įssentially, your database can be configured for one of FOUR possible states:

Snapshot Isolation ( which is ALLOWED if allow_snapshot_isolation has been turned on and the client requests it, but won’t be used by anyone unless SET TRANSACTION ISOLATION LEVEL SNAPSHOT has been requested.Serializable ( which also uses locking – even when one or both flavors of versioning has been enabled ).Repeatable reads ( which also uses locking – even when one or both flavors of versioning has been enabled ).Read committed using version ( this is the DEFAULT when read_committed_snapshot has been turned on ).Read committed using locking ( this is the DEFAULT when read_committed_snapshot has NOT been turned on ).Read uncommitted ( same as using NOLOCK ) and a lower isolation level than the default.However, before I can get to the weirdness (and try to make sense of it), I need to make sure the foundation is set well… In IEPTO1, I discuss locking types and how long locks are held for the different isolation levels – spending a great deal of time talking about the default mode of locking (read committed – with locking) vs.

Today I ran into a really interesting locking / blocking problem that I want to discuss… originally, a related discussion came up last week in our IEPTO1 course ( Immersion Event on Performance Tuning – Part 1) but today I ran into a similar problem that brought me back to the original discussion and I was able to reproduce the original scenario with a really interesting combination of things (some of which I did not expect and that led me down a super interesting path).

Read committed snapshot isolation level