none
How to update child rows after a primary key conflict RRS feed

  • Question

  • I am testing primary key conflict resolution.  I have a row on the client and server with the same primary key.  The server is performing a RetryWithForceWrite on the ClientInsertServerInsert conflict type.  The stored procedure that I have created performs an insert of the conflicting row which assigns it a different primary key, this is working just fine.  My problem is how do I notify the child tables of this change.    Say I have an Order and OrderDetail table, there is a conflict on the Order insertion so a new primary key is created for the row and the row is inserted.  How do I notify the OrderDetail table of this change?  Do I somehow pass this info back down to the client to modify accordingly?  Unfortunately once the ApplyChangeFailed event is handled on the Server side it is not triggered on the client side.
    Thursday, January 7, 2010 9:09 PM

Answers

  • Andrew,

    Unfortunately, there is nothing we do in SyncFx that provides the ability to manage identities.  Database server or another server is the typical pattern for centralizing this management.  If you set the seeds and then sync before fixing them up, you will have to manage the insert-insert conflicts that will occur. 

    Regards,


    Sean Kelley
    Senior Program Manager
    Microsoft
    Wednesday, January 13, 2010 6:56 AM
    Moderator

All replies

  • Andrew,

    Let me attempt to restate your scenario just to make sure that I understand correctly:

    1. Client inserts and order with ID = 1
    2. Server inserts and order with ID =1 but this order is different than the one inserted in the prvious step
    3. Sync
    4. SyncFx attempts to insert order id = 1 on the server but it fails due to PK violation and a ClientInsertServerInsert  is generated
    5. You catch the event and specify force write
    6. The proc is intelligant enough to generate a new PK for the record (lets say id = 2)

    Desired behavior is that the new ID (i.e. 2) should be downloaded to the client correct?  Are you running into this issue because you are attempting to use identities?


    Sean Kelley
    Senior Program Manager
    Microsoft
    Friday, January 8, 2010 12:59 AM
    Moderator

  • Yes, your recap is correct and yes I am using identities.  I understand that there are alternatives (GUIDS, identity management) but am attempting to investigate the proper procedure to handle it in the manner described.  Is identity management the preferred method of handling such conflicts?

    It is currently downloading the new ID ( i.e. 2) back down to the device properly.  But if I have child tables that were originally pointing to ID 1 how do I update these references to point to ID 2?
    Friday, January 8, 2010 1:22 AM
  • I would suggest that you find a way to manage keys and avoid conflicting inserts.
    Repairing a conflicting inserts after the fact is difficult.
    For example: With Order and OrderDetail
        - insert Order 1 at server
        - insert OrderDetails 1 and 2 for Order 1 at server
        - insert Order 1 at client
        - insert OrderDetails 1,2 and 3 for Order 1 at client
        - Synchronize: OrderDetails 3 for Order 1 at client will synchronize and apply successfully at the server. All other rows from the client will fail as a conflict. 
    Repairing this situation is harder than giving the remote logic to pick a key that does not conflict.
    --
    Kyle

    Friday, January 8, 2010 10:20 PM
    Answerer

  • Ok, so if I go the route of assigning each client a unique set of keys is the GetSchema() method on the server  the best place to set the seeds?  Is it possible to set the seed on the mobile side once all rows on the initial creation have been inserted?



    Also, if a client happens to be close to running out of their alloted seeds, is there somewhere within the sync process where I can check this and force a database recreation?  Or does this have to be accomplished manually outside of the sync process.
    Sunday, January 10, 2010 6:16 PM
  • Andrew,

    Unfortunately, there is nothing we do in SyncFx that provides the ability to manage identities.  Database server or another server is the typical pattern for centralizing this management.  If you set the seeds and then sync before fixing them up, you will have to manage the insert-insert conflicts that will occur. 

    Regards,


    Sean Kelley
    Senior Program Manager
    Microsoft
    Wednesday, January 13, 2010 6:56 AM
    Moderator