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