TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Kalen Delaney — Tue, 12 Oct 2010 20:02:00 GMT

“It ain't what people don't know that hurts them it's what they know that ain't so” -- commonly attributed to Mark Twain SQL Server has been around for a long time now, and people who learn one version well sometimes have monumental task when a new version...(read more)

Geek City: How SQL Server Detects the Correct Versioned Rows

Kalen Delaney — Thu, 03 Apr 2008 19:16:00 GMT

Here is a question I just received from the feedback page on my web site:

I have finished the book <the storage engine> and like it very much. I am now reading <query tuning and optimization>
I know in the READ COMMITTED SNAPSHOT isolation, when a row is being modified in a transaction, it generates an old committed version so another transaction can read it without being blocked.
But I don’t know how SQL Server uses SNAPSHOT isolation to prevent Phantom Read being happening.
In SERIALIZABLE isolation the ranged key or the whole table is locked, but in SNAPSHOT isolation, it can NOT generate any committed version since the row does NOT exist at the moment. So how does it know that the newly inserted data should not be included in the second SELECT statement?

-Tom

Tom is correct in that INSERTS do not generate versioned rows. However, SQL Server is able to keep track of when each change was made under snapshot isolation. Once a database is enabled for snapshots, every rows inserted, deleted or modified gets an additional 14 bytes of overhead added to it. These bytes contain 2 pieces of information. First is a row pointer to the previous committed version of the row, which is stored in the version store in tempdb. This pointer is only used for deleted and updated rows, since there will be no previous values for newly inserted rows.

However, these extra bytes also include a value called XSN, or transaction sequence number, which you can think of like a timestamp for a database. Any database enabled for snapshot keeps an internal XSN value, that is incremented each time any change is made, or any snapshot select is performed. The metadata also keeps track of all active transactions, and what the XSN was when the transaction started. It uses the view sys.dm_tran_active_snapshot_database_transactions for this. So when you are reading data, SQL Server will look at the XSN number in each row, and not return any rows that have an XSN value greater than the XSN value at the time the transaction started.

I hope this helps!

~Kalen

Kalen Delaney : internals, Transactions

TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Geek City: How SQL Server Detects the Correct Versioned Rows