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.

MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

  • "tempdb must have enough disk space for the version store. If there are very long-running transactions, all the versions generated by update transactions during the time must be kept in tempdb. If tempdb runs out of space, update operations do not fail, but read operations using row versioning might fail"

Can you explain me why?
taking into account that (quoting the same article):

  • "When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level... Because the record versions are stored in tempdb"

that is, update (but not read) operations require space for row versioning should have IMO failed due to lack of space.
And why do read ops fail if they do not require version storage in tempdb ?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

(Converting my comment to an answer. I don't know if this is definitive, but it makes sense to me. If you can prove it wrong, let me know in the comments.)

The row versions aren't actually required to complete a write operation; they're only required by readers that come along and need those versions of the rows. If they need them.

If tempdb runs out of space during version generation, while that's not okay, it may end up working out if no readers need those versions. A read operation would only fail if it needs a version of a row that isn't in tempdb.

Furthermore, any write operation is considered more important than a SELECT, for obvious reasons (same idea as when the Deadlock Monitor figures out which process to kill if there's a deadlock), so it's preferable to fail a SELECT if possible.

share|improve this answer
    
Thanks but... I am accustomed to comprehend the term "version" as "version ID (timestamp)" added to rows (in user databases) but you seem to use the term "version" for snapshot copies of rows (in tempdb database). Well, I am not sure. Couldn't you rectify your answer using more clear terms like "version IDs" and "row copies"? –  Fulproof Dec 10 '13 at 2:25
    
Also, I cannot understand how writes can be without reads, i.e. in order to write isn't it necessary to first read underlying data?! doesn't SELECT failure lead to writes failure –  Fulproof Dec 10 '13 at 6:00
    
@Fulproof: Right, writes do involve reads. The point was that there is potential to fail the read portion of whatever operation it is (SELECT, UPDATE, DELETE, etc.). I just happened to use SELECT to make my point in the last paragraph. –  Jon Seigel Dec 10 '13 at 16:40
    
I am still confused - why read op needs (depends on) space in tempdb ? Is it a preventive measure? –  Fulproof Dec 12 '13 at 4:45
    
@Fulproof: Read operations don't depend directly on space in tempdb (in this example). They depend directly on whether or not the required row versions exist in tempdb (if snapshot isolation is used). The row versions may not exist if a write operation used up all the space in tempdb. Does that make more sense? –  Jon Seigel Dec 12 '13 at 14:04
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.