Is there a possible race condition in this UPDATE statement?

Question

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?

Answer 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...

Answer 2

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.