locked
How to maintain data consistency? RRS feed

  • Question

  • Our server application performs transactional table updates to maintain data consistency.

    Synchronization framework, however, enumerates inserts, updates and deletes in separate queries, so it is possible for server application to perform several updates inbetween, which leads to inconsistent data on the client (to simulate this you can insert a delay into SyncProgress event handler).

    Is there a way to command framework to lock tables during synchronization?

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:18 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, February 8, 2007 7:51 PM

Answers

  • Good News!

    SQL Server 2005 SP2, shipped yesterday, has a simple fix for the consistency issue. Check out all the details here.

    Thanks!

    Wednesday, February 21, 2007 12:44 AM
  • Nice, I love positive surprises like these!
    Wednesday, February 21, 2007 2:35 PM

All replies


  • Excellent question, Alex!

    True, using timestamp could miss changes due to lingering transaction on the server. One solution to this problem is exactly what you suggested which is locking the table while sync is in progress. You can edit SyncAdapter commands and add query hint to lock the table.

    Thursday, February 8, 2007 9:02 PM
  • Just out of curiosity have you tried this when snapshot isolation is enabled?  I would expect that if a server side user had a row locked and the user synchronized the data during the time the server side user had the row locked then they would never get this row as the remote user is just getting the previous snapshot.  Am I correct on this?
    Friday, February 9, 2007 7:24 PM
  • Good News!

    SQL Server 2005 SP2, shipped yesterday, has a simple fix for the consistency issue. Check out all the details here.

    Thanks!

    Wednesday, February 21, 2007 12:44 AM
  • Nice, I love positive surprises like these!
    Wednesday, February 21, 2007 2:35 PM