Snapshot isolation: detailed info on operation

Question

I am thinking of implementing a delta logic for an olap application using snapshot isolation (transactional only). I cannot use change tracking, because I have to deal with sql server version coughhellcough 2005, so it's rowversion for me. What I've read about snapshot is that it creates copies of the old rows in tempdb. What I don't understand is how the system "knows" what tables to do this for.

So let's say I have tables A,B,C who are receiving multiple inserts/updates/deletes from various batches with plain old read committed. Each has an indexed rowversion column named [rv]. Now, I run the following:

set transaction isolation level snapshot
begin transaction
declare @current_version varbinary(8)=@@dbts -- get current rowversion
declare @last_version varbinary(8)=(select last_delta_version from configuration_table)

select * from A where rv>@last_version
select * from B where rv>@last_version
select * from C where rv>@last_version

update configuration_table set last_delta_version=@current_version

commit

If I'm not mistaken this should work as delta (it doesn't cover deletions, let's ignore this). Here is the practical example I'd like an explanation for: Let's say my code has started and is reading table A. At that time, some other transaction modifies table C. Will the snapshot transaction create a tempdb copy of the rows affected on C? If so, how did the SQL server know? Does it check my query beforehand and notices that "ah, I gotta keep track of A,B and C for this guy's transaction"? Does it keep track of changes on all db tables instead? Does something else happen?

Answer 1

To answer your question, yes, SQL Server keeps track of that. That is the promise of snapshot isolation: you get consistent picture of the database of how it looked when the transaction started. How it keeps track? It adds 14 extra bytes to the row, and these 14 bytes includes the LSN, which it can compare with the LSN for your transaction.

Now over to the bad news: your scheme is not going to work out, but you can miss updates.

Say that when you start @latest_version is 0x0000A000 and @@SuzyMc67 is 0x0000B000. Say furthermore that when your transaction starts, there is a new row in C where the rowversion value is 0x000C123. However that row was not committed when your transaction started, so you will not get it next round. And next round it is below the high-water mark.

I've worked a bit with getting changes by using rowversion columns as a high-water mark. Or more precisely, I had to, because some colleagues thought it was a bright idea, but they did not understand what they were doing. Any form of snapshot is a no-no, since you can miss updates. But also without snapshots, it's precarious. I recall that I recommended my colleagues to first run a query that got the changed ids from the index of the rowversion column. That improved things, but I think that for really high-frequency scenarios it still broke down. (But they tracked the high-water mark per table, rather than using @@SuzyMc67 , which I think is a little safer.)

And that is the key thing. The less common updates are, the lesser the risk that you miss updates. But, still, nope, do not use snapshot. If you need snapshot - I think an upgrade is the only solution. For Change Tracking, snapshot isolation is recommended.

Answer 2

A simpler solution to your problem is to create an "audit" table and a trigger to populate the audit table with the operation, PK and datetime of the LAST change to the row. That will handle all your problems. It is a CDC for old versions. However, if you have lot of table to manage, you have to manage a trigger on every table.

Answer 3

Hi GeorgeMenoutis-7690,
In addition, please refer to How Snapshot Isolation and Row Versioning Work for more details.
And here is a script for audit trail triggers for SQL Server 2005 which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.