0

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).

Improve this question
1
  • Actually I think the tempdb maintains the before snapshot, queries see uncommitted data and look up the before-state in tempdb.
    – eckes
    Nov 15, 2018 at 11:58

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • Does it means that any SELECT on records impacted by modified data (eg: in a pending transaction) could be slower than usual ? What about indexes and such ?
    – tigrou
    Nov 15, 2018 at 13:29
  • You need to consider the alternative. If you don't use versioning, you will be blocked instead! So, instead of waiting because of being blocked, you will get at the (old) data, using the pointer (chain).
    – Tibor Karaszi
    Nov 15, 2018 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.