SQL Server – How Many Versions Stored in Multiple Updates in a Single Transaction

snapshot-isolationsql servertempdb-version-storetransaction

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

  • "… 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"

which is in the section "Understanding Row Versioning" common for following both "Row Versioning in Read Committed Using Row Versioning" and "Row Versioning in Snapshot Isolation" sections.

Further on the example of row-versioning due to modifications is given in context of multiple updates of the same record by multiple transactions (T1, T2, T3) in SNAPSHOT only.

In case only one transaction updates a record multiple times (by multiple statements), will multiple version stores be stored (linked) or only the one taken at the moment of the SNAPSHOT "image" taken?

Well, the answer to this question should immediately answer my other pending related questions:

Best Answer

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

Just one.

The first update to the row generates a row version and exclusively locks the row. Later updates to the same row within the same transaction do not generate new row versions.

A linked list of row versions can arise as follows:

  • Transaction T1 in a SNAPSHOT transaction reads a row and sees the value 'a'.

  • Transaction T2 (under any isolation level) updates the value from 'a' to 'b' and commits. This generates a row version for the value 'a'.

  • Transaction T3 (under any isolation level) updates the value from 'b' to 'c' and commits. This generates a row version for the value 'b'. This row version links to the previous row version for 'a'.

  • Transaction T1 reads the row value by traversing the link from the value stored on the current page ('c'), to the row version 'b', and then on back to row version 'a'.

Row versions are generated by data changes regardless of whether there is an existing reading transaction (at any isolation level). The versions may never be needed, but they are still generated - the same linked list of versions is created even without transaction T1.

Related Question