Sign up ×
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 Dec 9 '13 at 1:58

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

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

2 Answers 2

up vote 5 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

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

  1. Snapshot

In the first session


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.

share|improve this answer

Your Answer


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.