Row Versioning based Isolation levels are very useful in reducing blocking. Which are.
- Read Committed (default) Isolation level with READ_COMMITTED_SNAPSHOT database option is ON.
- Snapshot Isolation level with the ALLOW_SNAPSHOT_ISOLATION database option is ON.
Read committed isolation using row versioning provides statement-level read consistency however Snapshot isolation provides transaction-level read consistency.
Statement-level read consistency Vs. Transaction-level read consistency
In Statement-level read consistency when each statement within the transaction executes, a new data snapshot is taken and remains consistent for each statement until the statement finishes execution. Enable this when:
- When concurrency benefits is more important than increased overhead of creating and managing row versions.
- An application requires absolute accuracy for long-running queries where data values must be consistent to the point in time when a query starts.
In Transaction-level read consistency, a data snapshot is taken when the snapshot transaction starts, and remains consistent for the duration of the transaction, due to that it provides the benefit of repeatable without using shared locks Use snapshot isolation when:
- Optimistic concurrency control is desired.
- Probability is low that a transaction would have to be rolled back because of an update conflict (As it happens here, when transaction running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with read committed isolation using row versioning).
- An application needs to generate reports based on long-running queries that must have point-in-time consistency.