SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

Question

I was researching the differences between SQL Server's 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.

1. Why would the tempdb space be different for these modes? Does one store more granular versioning than the other?

2. Why is snapshot isolation more vulnerable to update conflicts?

Answer 1

1. READ COMMITTED SNAPSHOT uses 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-running SNAPSHOT transactions.) 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.
2. When a SNAPSHOT transaction T1 writes to a row that was modified by another transaction T2 in the time between T1 started and T1 attempted the write, the statement fails with an update conflict error. This is an optimistic concurrency model. With READ COMMITTED SNAPSHOT T1 would wait for T2 to release the X-lock on the row and continue normally.

Answer 2

One more difference between snapshot and read committed snapshot is the following.

1. Snapshot

In the first session

SET TRAN ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT * FROM TB1 ..... .....

In the second session

Update TB1 SET NAME = NAME + 'test' Where id = 1

In the first session

SELECT * FROM TB1 -- THIS WILL return the value name for ID = 1, not name + 'test' COMMIT TRAN

In the read committed snapshot the first select in the session 1 will return name for id = 1, and the second select will return name + 'test'.

So in snapshot isolation SQL SERVER do a snapshot in the start of the transaction, and read from that snapshot during the whole transaction.

In read committed snapshot the snapshot is taken for every SELECT statement during the transaction.