Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 9 '13 at 1:58

This question came from our site for professional and enthusiast programmers.

4  
Isolation levels explained via poster and lots of detail too. –  billinkc Dec 8 '13 at 20:21
    
@billinkc: Very cool graphic. I appreciate the link! This will certainly come in handy. Right beside the section for Snapshot Isolation, it mentioned "Lots of tempdb". But why would it use more space than read committed snapshot? –  John Russell Dec 8 '13 at 20:40
add comment

1 Answer

up vote 2 down vote accepted
  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.
share|improve this answer
    
For #2, is it safe to say that SNAPSHOT does not exclusively lock for updates - it just relies on row versioning? –  John Russell Dec 10 '13 at 2:59
    
@JohnRussell it does lock exclusively to support rollback. All writes must X-lock to ensure that the row can be restored in case of a rollback. –  usr Dec 10 '13 at 9:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.