I am actually considering using SNAPSHOT isolation for one WCF service.
Before doing so, I would like to know how it works.
AFAIK, when it's enabled, updated rows are maintained in tempDB (instead of modifying data directly). Because of that, other readers (eg : that execute a SELECT) won't be blocked and will not see any change until transaction is committed.
What is not clear to me is what happen when transaction is committed.
I tried the following (on a huge table with SNAPSHOT enabled) :
1)
BEGIN TRANSACTION
UPDATE foo SET bar = 'something' --take a LOT of time, which is expected
2) (in another connection/tab)
SELECT * FROM foo --still works, because of SNAPSHOT isolation
3) (back to connection/tab started in 1.)
COMMIT --very fast, almost immediate.
I expected that commit to be very slow, because I thought SQL would have to copy rows from tempDB back to the pages that contains the actual rows data.
Instead, it was almost immediate. Why is it so ?
Also when transaction was still not committed, I took a look at tempDB but could not see any table or data (when a temporary table is created, for example, it's possible to see it in tempDB).
Best Answer
SQL Server will store the "before image" of the values in tempdb. And if you modify the same row several times, then you will have a chain of before-values (a linked list, essentially). The row also has information about a "logical timestamp" (so to speak) for when it is valid. This information is used by SQL Server to determine what data a SELECT should be used (as you might know, the true snapshot isolation gives us repeatable read - so a reader in the same transaction might still need to see the prior version even after the modifier did commit).
Then there is a clean-up process (one per minute, i believe) that sweeps the no longer needed versioned data and removes it from tempdb (aka "version store").
I.e., no need to copy anything at commit time, since it was the "real" data that was modified in the first place.