principlesqlMajor
SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT
Viewed 0 times
committedreadsqlsnapshotserver
Problem
I was researching the differences between SQL Server's
Choosing Row Versioning-based Isolation Levels
For most applications, read committed isolation using row versioning
is recommended over snapshot isolation for the following reasons:
-
It consumes less tempdb space than snapshot isolation.
-
Snapshot isolation is vulnerable to update conflicts that are not applicable to read committed isolation using row versioning. When a
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.
I'm somewhat new to these topics, but I can't seem to understand the two bullet points from the link above.
-
Why would the tempdb space be different for these modes? Does one store more granular versioning than the other?
-
Why is snapshot isolation more vulnerable to update conflicts?
READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource:Choosing Row Versioning-based Isolation Levels
For most applications, read committed isolation using row versioning
is recommended over snapshot isolation for the following reasons:
-
It consumes less tempdb space than snapshot isolation.
-
Snapshot isolation is vulnerable to update conflicts that are not applicable to read committed isolation using row versioning. When a
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.
I'm somewhat new to these topics, but I can't seem to understand the two bullet points from the link above.
-
Why would the tempdb space be different for these modes? Does one store more granular versioning than the other?
-
Why is snapshot isolation more vulnerable to update conflicts?
Solution
READ COMMITTED SNAPSHOTuses a new snapshot after each statement. That means that less row versions are being kept alive. (The statement you quoted from the docs is slightly misleading because it suggest that this is always true - it is only true in case of long-runningSNAPSHOTtransactions.) Snapshot row versions are created on writes. The reads do not influence what gets put into tempdb. Writers cannot possibly foresee what reads will be carried out in the future. Readers only influence only what can be purged.
- When a
SNAPSHOTtransactionT1writes to a row that was modified by another transactionT2in the time betweenT1started andT1attempted the write, the statement fails with an update conflict error. This is an optimistic concurrency model. WithREAD COMMITTEDSNAPSHOTT1would wait forT2to release the X-lock on the row and continue normally.
Context
StackExchange Database Administrators Q#54680, answer score: 23
Revisions (0)
No revisions yet.