Snapshot and RCSI.When does SQL Server go to VersionStore?

Question

When SQL Server reads data with SNAPSHOT or RCSI on, how does it know it has to look a record up in the version store vs the table?

Let’s pretend session A modifies a few records of a table and escalates the row-level locks to a page-level one. At the same time another session B tries to read that page. It sees the page being under X lock but what happens afterwards? How does session B know which records on that page are modified and therefore in the version store, and which are not? 14 bytes pointers? But, to me, all the rows on the page can have them: some pointers can be relevant, some can be just outdated.

Thanks!

Answer 1

Referencing the documentation about Understanding Row Versioning-Based Isolation Levels, I've extracted what I think are the important take-aways with regards to your question, but I'd recommend reading the entire post for additional valuable information.

When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set ON, the SQL Server Database Engine assigns a transaction sequence number (XSN) to each transaction that manipulates data using row versioning. Transactions start at the time a BEGIN TRANSACTION statement is executed. However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement. The transaction sequence number is incremented by one each time it is assigned.

When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. The Database Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number.

Behavior When Reading Data

When a transaction using the snapshot isolation level starts, the instance of the Database Engine records all of the currently active transactions. When the snapshot transaction reads a row that has a version chain, the Database Engine follows the chain and retrieves the row where the transaction sequence number is:

• Closest to but lower than the sequence number of the snapshot transaction reading the row.
• Not in the list of the transactions active when the snapshot transaction started.

Read operations performed by a snapshot transaction retrieve the last version of each row that had been committed at the time the snapshot transaction started. This provides a transactionally consistent snapshot of the data as it existed at the start of the transaction.

Read-committed transactions using row versioning operate in much the same way. The difference is that the read-committed transaction does not use its own transaction sequence number when choosing row versions. Each time a statement is started, the read-committed transaction reads the latest transaction sequence number issued for that instance of the Database Engine. This is the transaction sequence number used to select the correct row versions for that statement. This allows read-committed transactions to see a snapshot of the data as it exists at the start of each statement.