locked
How to avoid sync conflicts when using int primary keys? RRS feed

  • Question

  • If I use int as primary key, then I will get duplicate IDs if several clients or client and server add new rows to my table. What's the best practice for handling that?

    For example, in my own demo app I have customers and orders. I add a customer on the client and add an order to that customer, then I do the same on the server.In my example, both new customers have the ID 9. They both have one order with ID 86. Now I synchronize client and server. In my demo application, the result is that the ID of one of the new customers (the customer that I created on the client) is changed during synchronization, and the customer created on the server now has both orders.

    How do I prevent such synchronization errors? Shouldn't sync services report that as a conflict?

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:21 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, February 5, 2007 9:52 AM

Answers

  • The ClientSyncProvider & ServerSyncProvider fire ApplyChangeFailed event when it detects failure to apply a row. In your scenario, you should get the event for customer row due to primary key collision. The event gives you the ability to make changes to the row and reapply, reject the change and continue or rollback all the changes.

    If you decided to ignore the conflict and proceed with the sync, the order row will be applied without detecting any conflicts. However, the ApplyChangeFailed event exposes the dataset to you such that you can make any desired modification specific to your solution. So if you like you could remove the order row(s) from the data set for that customer.

    At the end of sync, SyncStatistics object reports all conflicts that were detected but not resolved.

    Monday, February 5, 2007 4:29 PM

All replies

  • The ClientSyncProvider & ServerSyncProvider fire ApplyChangeFailed event when it detects failure to apply a row. In your scenario, you should get the event for customer row due to primary key collision. The event gives you the ability to make changes to the row and reapply, reject the change and continue or rollback all the changes.

    If you decided to ignore the conflict and proceed with the sync, the order row will be applied without detecting any conflicts. However, the ApplyChangeFailed event exposes the dataset to you such that you can make any desired modification specific to your solution. So if you like you could remove the order row(s) from the data set for that customer.

    At the end of sync, SyncStatistics object reports all conflicts that were detected but not resolved.

    Monday, February 5, 2007 4:29 PM
  • Sync services does report conflicts.  Never the less, it is wise to avoid conflicts when possible:

    In SQL Server CE database, you can use ALTER TABLE <Table Name>  ALTER COLUMN <Column Name> int IDENTITY(<Client Seed>, <Client Step>) to alter the identity range.  This way the records inserted at client would use the range specified.

    Thanks,

    Laxmi

    Wednesday, February 7, 2007 9:12 AM