CODEWITHSUNDEEP
sqlsql-serverisolation-levelsnapshot-isolation
Chris H
Chris H

Reputation: 86

How does SNAPSHOT isolation read snapshot data for tempdb?

I'm trying to understand how SNAPSHOT isolation pulls data into tempdb. I understand that there is transactional consistency due to row versioning in tempdb, but I'm more curious about how that data is copied into tempdb in the first place.

The documentation talks more about how data is read from the snapshot, but doesn't really touch on how the snapshot is taken. It seems to me that a snapshot would be done in a serialized fashion, since that would be the most accurate representation of the database at a particular time. But, I know better than to assume.

Does anyone know exactly how this works?

Upvotes: 1

Views: 232

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

Every change in the database is associated with a "transaction sequence number" (XSN).

Every snapshot or RCSI query runs "as of" some XSN.

Every modified row is marked with the XSN of its last modification.

So before a query reads a row, it checks the row's XSN against the query's XSN. If the row has been modified since the query began, the query looks up the correct version of the row in the version store.

The 6-byte XSN is part of the 14 bytes of overhead added to modified rows in row versioning isolation levels. It's not present on all rows, and is added as rows are modified (or inserted for tables with a trigger). Also the version store is being moved out of TempDb and into the user databases as part of the Accelerated Database Recovery feature in Azure SQL Database and SQL Server 2019.

Upvotes: 1

Related Questions