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.

Trying to better understand row-versioning based transaction isolations in SQL Server...

According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":

  • "...when a record in a table or index is modified, the new record is stamped with the "sequence_number" of the transaction that is performing the modification"

which makes perfect sense - when a transaction ID is linked to a record change.

What I cannot understand is, from the same source:

  • "... All the earlier versions of a particular record are chained in a linked list"

enter image description here

Why is there a need in a linked list, i.e. to link the changes of the same record ?

UPDATE:

As stated by Remus Resanu in his answer to "Does Snapshot Isolation only give you what has been committed? (And General Snapshot Isolation questions.)":

  • *"Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use read_committed_snapshot RCSI"*

In other words, there is a need to keep multiple row versions of a record for multiple/different transactions (with corresponding pointers/links), that is to be accessed directly to a corresponding copy taken when snapshot was taken (though multiple ones per multiple transactions but the single one per one corresponding transaction). This is in perfect agreement with explanation that RCSI takes less space in tempdb but contradicts to statement (or makes ambiguous to my comprehension), referring to both RCSI and SNAPSHOT (note, that it is not making any difference between RCSI and SNAPSHOT according to its context used in that article):

  • "All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed on each access to reach the transactionally consistent version of the row. Version records must be kept in the version store only as long as there are row versioning–based queries that might be interested in them"

The latter sounds like the space required is still the same both for RCSI and SNAPSHOT?!
and it is stated contradictory in different MSDN articles

SUBQUESTION:

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, Paul White, Shawn Melton, RolandoMySQLDBA, Michael - sqlbot Dec 15 '13 at 7:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Are you asking why there are multiple row versions, or why those versions are stored in a linked list, specifically? –  Jon Seigel Dec 13 '13 at 17:11
    
See UPDATE in my question. I ask why those versions are stored in a linked list as it is unnecessary, will require to store all versions per updates made in the same transaction and will not make possible for less space in tempdb in RCSI –  Fulproof Dec 14 '13 at 15:15
    
I assume they're in a linked list for efficiency reasons. I'm still not sure what you're getting at here. –  Jon Seigel Dec 14 '13 at 17:05
    
By "SNAPHOT" in the original title of my question I meant both Read_Committed_Snaphot and SNAPSHOT isolations, so I've changed from "SNAPHOT" (of original question) to "row-versioning". Here are 2 subquestions: 1)is it correct that the needed storage is the same for both isolations? 2)Why to have multiple versions of the same record per the same transaction in any of isolations? –  Fulproof Dec 15 '13 at 0:43

1 Answer 1

up vote 2 down vote accepted

Why is there a need in a linked list, i.e. to link the changes of the same record?

The "current row" in the index or data page provides a pointer to only the most recent row version in the version store. A transaction that needs older versions needs a way to navigate to the exact version it should use. The linked list provides this mechanism.

share|improve this answer
    
So, there is never a difference in required version storage between SNaPSHOT and RCSI (Read Committed Snaphot Isolation) isolations? –  Fulproof Dec 15 '13 at 3:42
    
@Fulproof That's not really related to this question, but: The row versions generated are the same, but SNAPSHOT will tend to generate more versions (for the whole transaction). RCSI is limited to one statement. –  Paul White Dec 15 '13 at 4:17

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