A nice gift from SQL Server 2005 SP2 to sync developers

If you haven’t heard the news yet, SQL Server 2005 SP2 is out! Now if you are like me, thinking about data synchronization for the most part of your day, then you should be really excited about SP2. Let me tell you why.

Recall that in all sync services demos in this blog, we used timestamp tracking column to know when the row was updated. This works fine if there were no uncommitted transactions on the SQL server backend. So what would happen if there was a pending transaction? Well, changes made in that transaction will not be enumerated by the SelectIncrememental commands, that’s expected since dirty data is not visible in read committed isolation level in which we typically operate. When the transaction is committed sometime later on, the timestamp value of the changed rows could be older than the one the client stores for the next sync. Timestamp value is captured at statement execution time and that poses the problem for our simple tracking approach. Basically, next time the client synchronizes it will miss those changes with older timestamp. This is not cool and not acceptable of course!

To clarify this point more, let’s look at a timeline diagram:

As the diagram shows, timestamp is monotonically increasing with every change made in the database regardless if the change is committed or not. Transaction T2 is committed therefore all its changes (from 53-68) are visible. Transaction T1 on the other hand has not committed yet, changes at 50, 51, 52 and 60, 61, 62, 63 are not visible. In the diagram, the sync request for a new anchor came back with timestamp value of 61; therefore all sync enumerations will go up until this point for committed rows only. After T1 commits, all its changes will be visible. In the next sync, changes from 61 to N (70 for example) will see T1 changes of 60, 62 and 63 but will miss 50, 51 and 52. Too bad!

But hey, sync team is part of SQL Server and the SQL engine guys are next door. So we asked politely, of course, for a little favor to somehow be able to get the timestamp of the oldest uncommitted transaction. Well, guess what? Our request was granted and SP2 has this little function (min_active_rowversion())  for sync services developers.
Let’s put the new function to test. Below is a simple SQL commands to show the min_active_rowversion in action in the same database and table I used in the demos:

select min_active_rowversion()

select @@DBTS

select * from orders

begin tran

insert into orders(order_id, order_date) values (5, GetDate())

insert into orders(order_id, order_date) values (6, GetDate())

commit tran

The result I got on my test server is as follows:

Also try opening another query window to experiment when an older tran is committed the min_active_rowversion function will return the timestamp of the next oldest pending tran in line! Beautiful!

Back to sync services; to take advantage of this new function simply update the implementation of GetNewAnchorCommand on the server provider to “Select min_active_rowversion() - 1” with this little change you’ve solved a serious data consistency problem! Viva SP2!

Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews. See Examples: Romantic Hotels in Myrtle Beach , Top 10 Hotels in Seattle ,Best Hotels in Miami, Best Hotels in San Francisco , Hotels with in-room jacuzzi and 5 Star Hotels in Los Angeles . Your feedback is welcome on twitter.com/raveable, raveable blog. 

rowversion.jpg