5

I'm writing a synchronizer software which will take all changes in one DB and synchronize them to another DB. To this end I've added in my table T two columns:

alter table T add LastUpdate rowversion, LastSync binary(8) not null default 0

Now I can easily select all rows that have changed since the last synchronization:

select * from T where LastUpdate > LastSync

However after performing the synchronization I should make the two fields equal. But updating the row also updates the timestamp, so I must do this:

update T set LastSync=@@DBTS+1 where ID=@syncedId

But I'm wondering - will this always work? What if I read the value of @@DBTS and then another user manages to insert/update a row somewhere before my row is committed? Is this risky code? And if yes - how could it be made better?

A-K
  • 16,804
  • 8
  • 54
  • 74
Vilx-
  • 104,512
  • 87
  • 279
  • 422
  • By the way what version and edition of SQL Server are you on? Is [Change Data Capture](http://msdn.microsoft.com/en-us/library/bb522489.aspx) an option? – Martin Smith Oct 22 '10 at 11:52
  • @Martin Smith - 2008, I think. Not sure what the client has. – Vilx- Oct 22 '10 at 13:37
  • @Martin Smith - I checked out the change data capture, but that would be an overkill. A simple timestamp will suffice. I only need to know which records still need to be sync'ed. I don't need the full history. – Vilx- Oct 22 '10 at 14:09

2 Answers2

4

Storing "LastSync" in the same table as the real data is maybe not a good idea at all. Try storing it in another Table that doesn't have a rowversion. That way you avoid the "updating the row also updates the timestamp"-problem.

Your synchronizer software can then work this way:

  • Get the @LastSync value from the additional table
  • "Select @ThisSync = max(LastUpdate) from T where LastUpdate > @LastSync"
  • "Select * from T where LastUpdate > @LastSync and LastUpdate <= @ThisSync" are your rows for sync
  • Store @ThisSync as the new "LastSync" in the additional table.

Entries that are modified while the synchronization is running will have a higher rowversion value than the max() query. They will be synchronized the next time your synchronizer is called.

Oliver
  • 3,225
  • 1
  • 18
  • 12
-1

If you run this in a Serializable transaction then no other reads/writes will be able to affect these tables.

RepeateableRead may also do the job...

cjk
  • 45,739
  • 9
  • 81
  • 112
  • 1
    Tables - yes. But what about the value of `@@DBTS`? That isn't stored in any table! – Vilx- Oct 22 '10 at 11:07
  • @Vilx If you take an exclusive table lock for the duration of the synchronisation then presumably it doesn't matter if `@@DBTS` gets incremented by an event in another table though. – Martin Smith Oct 22 '10 at 11:13
  • @Martin Smith - but @@DBTS is global for the whole DB. Why doesn't it matter? If I get one (older) value for `LastSync` field, and a newer for the `LastUpdate` field, my synchronization will be broken. – Vilx- Oct 22 '10 at 11:25
  • @Vilx Well if you update the table and the value of `@@DBTS` is 10 then another table gets a couple of rows updated making the value of `@@DBTS` equal to 12 and you end up storing 12 in the `LastSync` field why is that a problem? Next time you come to synchronise you are still looking for rows where `LastUpdate > LastSync`. – Martin Smith Oct 22 '10 at 11:29
  • @Martin Smith - the problem is that it's the other way round. `LastUpdate` gets the value **after** `LastSync`. Otherwise I wouldn't need that `+1` in the query in the first place. – Vilx- Oct 22 '10 at 11:34