locked
Sync Collision Resolution Problem RRS feed

  • Question

  • I am using the Sync Framework with SQL Express 2005.  I am having a problem when multiple users try to change the same data at the same time.  When this happens then the data is updated/sync appropriately on User1's local database but is does not update/sync properly on User2's local database.

     

    For example, User1 and User2 will select a row of data to update.  User1 shows the change properly but User2 does not.  I tried manipulating the "update_originator_id" field but this does not work.  Is there a standard away to make User2's data sync properly?  Otherwise, User2 ends up with inaccurate data and this causes allot of problems.  I thought about checking the anchor tables but the fields are in binary and I am not sure how to deal with that.

     

    Thanks,

    cj

    Friday, October 31, 2008 7:18 PM

Answers

  • Are you using custom change tracking or the in-built SQL Server change tracking support?

     

    When User1 picks up the package does he do some update to it because of which the Server now sees as an update coming from User1 and hence update_originator_id reflects User1 at server? Now in this case, the client's update_originator_id is already updated with its local id so I dont understand why it still shows up as 0 in step 3 above. Can you check if the server is then doing another update which is then setting it to 0?

     

    With the column set to User1, when User2 makes local update and syncs to Server, it should raise a conflict, detect that first one wins (User1) and then send the current values from Server to User2. At the User2 local database, you detect that this row should not be displayed as an available package.

     

    Can you also try to add a dummy user column (similar to the update_originator_id), call it active_user and use this to achieve the same. This may help troubleshoot the issue further. Essentaially User1, updates this column to its id, syncs to Server. When User2 syncs with its id, a conflict detected and the row with User1 id in the column is sent back to User2 DB. At this point, User1 and User2 DBs have the same row with value=User1. At this point, your mid-tier can show all available rows with Value=<local_user_id> or 0 (meaning available for grabbing)

    Monday, November 3, 2008 8:40 PM

All replies

  • What is happening in your case? What does User2 see? User2's local update data or something completely different?

    What  conflict resolution are you using?

     

    Are you using the hub spoke (SQL Express Server and SQL Compact client) or the peer-to-peer sync

    model?

    Take a look at these articles that explain how to handle conflicts:

    http://msdn.microsoft.com/en-us/library/bb725997.aspx

    http://msdn.microsoft.com/en-us/library/cc761628.aspx

     

    Saturday, November 1, 2008 12:21 AM
  • We are using bi-directional sync with the hub-spoke model.   Our scenario is that we have van drivers with tablet pc's.   Each van driver has a SQL Express database on the pc and this database needs to sync with a main SQL Server database.  Drivers have a screen that lists where packages are located.  Each driver will select a package for pick-up and then go to pick up the package.  The driver's pc will occasionally go offline and then sync with the main server once the tablet is back online.  The basic conflict-resolution rule is first-in-wins. 

     

    The problem seems to occur when two or more driver's are online, select the same package, and then sync at the same time.  The goal is that if User1 and User2 sync at the same time then only one of them will be assigned to the package and have this displayed on his pc.  The other user should NOT have the package listed as available for pick-up but this is not happening.  I do not know if this is due to the table/row's update_originator_id field not updating correctly on one of the pc's or what.

     

    For example:

    1.  User1 and User2 sync at the same time

    2.  The table-row on the server's update_originator_id field is set to User1's ID

    3.  User1's update_originator_id remains at 0.

    4.  User2's update_originator_id remains at 0.

    5.  User1's screen correctly shows that he is assigned to the package.

    6.  User2's screen InCorrectly shows that the package is unassigned.   If User2 tries the select the package for pick-up, a message box correctly appears reading that the package has been assigned to another driver BUT the package REMAINS on User2's screen, is unchanged in the database, and gets stuck there forever. 

     

    If I could update User2's database so that the package no longer appears as active then that would fix the problem but I do not know how to do this?  I thought that this would be noted in the update_originator_id field but changing that bit does not work so this leads me to think the other unknown factors are coming in to play here.  I do not know if it is a setting in the anchor table or what?  The setting's in the anchor table are in binary format so it is difficult to decipher.

    Sunday, November 2, 2008 4:43 PM
  • Are you using custom change tracking or the in-built SQL Server change tracking support?

     

    When User1 picks up the package does he do some update to it because of which the Server now sees as an update coming from User1 and hence update_originator_id reflects User1 at server? Now in this case, the client's update_originator_id is already updated with its local id so I dont understand why it still shows up as 0 in step 3 above. Can you check if the server is then doing another update which is then setting it to 0?

     

    With the column set to User1, when User2 makes local update and syncs to Server, it should raise a conflict, detect that first one wins (User1) and then send the current values from Server to User2. At the User2 local database, you detect that this row should not be displayed as an available package.

     

    Can you also try to add a dummy user column (similar to the update_originator_id), call it active_user and use this to achieve the same. This may help troubleshoot the issue further. Essentaially User1, updates this column to its id, syncs to Server. When User2 syncs with its id, a conflict detected and the row with User1 id in the column is sent back to User2 DB. At this point, User1 and User2 DBs have the same row with value=User1. At this point, your mid-tier can show all available rows with Value=<local_user_id> or 0 (meaning available for grabbing)

    Monday, November 3, 2008 8:40 PM