locked
Update/Insert order RRS feed

  • Question

  • I am having trouble with parent and child rows being updated properly. I have a child row that, when inserted or updated, will fire a trigger and update a column on the corresponding parent row so that the parent row will be picked up for changetracking as well.

    When I ran a SQL Profiler trace, I see that the insert sproc for the child table is being run before the update sproc (which is truly an upsert) is run. Thus, we end up with a FK error because the sprocs are being run out of order. I have checked the data sync definitions and the tables are seemingly being loaded in order and belong to the same sync group. When I step through the code, it appears that the tables are in the correct order in the adapter and belong to the same sync group. But still, the child insert is firing before the parent update.

    Any thoughts as to why and how to fix?

    Thanks,
    Dennis

    Friday, April 16, 2010 2:47 PM

Answers

  • for v1 offline providers, sync group will be your best option out of the box.

    if you were using collaboration providers, since the FK violation will be caught in the ApplyChangeFailed event, you can specifyRetryNextSync to retry the failed row on another round of sync.

    • Marked as answer by Dennis Porter Friday, April 16, 2010 6:23 PM
    Friday, April 16, 2010 5:52 PM

All replies

  • Hi Dennis,

    Changes are applied in the following order: Deletes, Inserts, Update.

    Peeking at DBServerSyncProvider ApplyChanges, its  looping thru the tables collection, but it's applying all Deletes for all tables first, then applying all Inserts for all tables, and finally applying all Updates for all tables.

    This explains why you get all the child inserts first before the parent updates.

     

     

    Friday, April 16, 2010 4:50 PM
  • You are correct. We discovered that in the documentation:

    http://msdn.microsoft.com/en-us/library/bb902828(SQL.105).aspx  (see "Understanding Synchronization Order and Batching")

     

    That is just weird. I would think I would want to get the parent table finalized (at least inserts and updates) then move to the next table in the list (i.e. the child table).

     

    I found that if I put the parent tables in one syncgroup and the child tables in another, then it will work as I was hoping. The update (upsert) on the parent table will fire, then when it gets to the child table, the insert will fire and no FK errors will occur.

     

    My concern with this is, according to the documentation, these two sync groups will now be in separate transactions. So, it is possible that during an update of parent and child information, parent or child information could not update and rollback its transaction. The other group might finish okay and commit its transaction. Thus, leaving the mobile db in a state of inconsistency.

     

    See any other way to approach this? Am I overlooking something?

     

    Thanks for all of your help,

    Dennis

    Friday, April 16, 2010 5:04 PM
  • for v1 offline providers, sync group will be your best option out of the box.

    if you were using collaboration providers, since the FK violation will be caught in the ApplyChangeFailed event, you can specifyRetryNextSync to retry the failed row on another round of sync.

    • Marked as answer by Dennis Porter Friday, April 16, 2010 6:23 PM
    Friday, April 16, 2010 5:52 PM
  • Thank you for your thoughts.

    I think at this point, because it is a v1 offline scenario, we will use sync groups.

    Don't have a RetryNextSync method, but sure wish I did!

    Thank you so much for all of your help. I really appreciate it.

    Friday, April 16, 2010 6:23 PM