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"
Why is there a need in a linked list, i.e. to link the changes of the same record ?
- "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