Share via

A nice gift from SQL Server 2005 SP2 to sync developers

  • Article

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:

 

Select @@DBTS

Select min_active_rowversion()

Before Tran

0x0000000000000BC4

0x0000000000000BC5

Begin Tran + Inserting 2 rows

0x0000000000000BC6

0x0000000000000BC5

Commit Tran

0x0000000000000BC6

0x0000000000000BC7

 

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

Comments

  • Anonymous
    March 11, 2007
    Where do I find more information on min_active_rowversion() - BOL does not appear to know it ?

  • Anonymous
    March 11, 2007
    Where do I find more information on min_active_rowversion() - BOL does not appear to know it ?

  • Anonymous
    March 12, 2007
    Hi Erik, It is not in the current docs but coming up in next doc refresh. Thanks

  • Anonymous
    March 18, 2007
    Not surprisingly we've been get a lot of great questions about specific features and scenarios for our

  • Anonymous
    March 23, 2007
    What about "dirty data is not visible in read committed isolation level"? BOL: READ COMMITTED Specifies that statements_cannot_read_data_that_has_been_modified_but_not_committed_by_other_transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default. Prevents, but don't "not visible"

  • Anonymous
    May 04, 2007
    How does this benefit SQL 2000? How do we work around the issue there?

  • Anonymous
    February 06, 2008
    In a bit old post I wrote about min_active_rowversion() as the best way to get the new anchor showing

  • Anonymous
    February 06, 2008
    In a bit old post I wrote about min_active_rowversion() as the best way to get the new anchor showing