locked
@sync_last_received_anchor set incorrectly RRS feed

  • Question

  • Hi,

    I have set up client server setup with Sql exress as client and Sql ent as server.

    All my test cases are working fine except, when we downlaod data in bulk from server to client subsequent client updates gives conflict.

    I tracked the issue and noticed that the @sync_last_received_anchor at client end is less than the create_timestamp of all the downloaded rows.

    Do framework set anchors BEFORE downloading all the records from server ?

    Note : I am using bidirectional sync with decoupled tracking 
    • Moved by Max Wang_1983 Thursday, April 21, 2011 11:52 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, August 20, 2009 3:35 PM

Answers

  • Mandy,

    You will have to do the following to make the change for orders table. Same applies to other tables.

    1. Add a column create_originator_id to orders table with default value of 0.
    2. Change sp_orders_applyinsert proc to set create_originator_id to 1 instead of setting update_originator_id to 1.
    3. Change the where clause in sp_orders_incrinserts to the following.

     

    WHERE o.create_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor and o.create_originator_id = 0

     

    ORDER BY o.create_timestamp

    4. Change the where clause in sp_orders_incrupdates to the following.

     

    WHERE (o.create_timestamp <= @sync_last_received_anchor or o.create_originator_id = 1) and

    o

    .update_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor

     

    and o.update_originator_id = 0


    This should do it. Now the subsequent sync should pick up the local update as a update and not as an insert. Let me know how that goes.

    thanks
    Sudarshan
    Development Lead , Microsoft
    • Marked as answer by mandy.work Tuesday, August 25, 2009 4:46 AM
    Friday, August 21, 2009 11:01 PM
    Moderator
  • Sudarshan, it worked only after minor change in step 2

    2. Change sp_orders_applyinsert proc to set create_originator_id to 1 instead of setting update_originator_id to 1.

    Here we need also update the update_originator_id to 1 otherwise unedited records will also get selected in dbo.sp_orders_incrupdates. Will execute all other test for bidirectional sync and let you know.

     

    So is this a work around or we just fixed bug in a sample provided by Microsoft?

     

    One more confusion in my mind, as explained by you ‘ReceivedAnchor’ from anchor table is used as @sync_last_received_anchor at server end; I check the @sync_last_received_anchor but it is different from the one stored in anchor table of client. Could you please throw some more light?

     

    Thank you very much for your time again!!!

    • Marked as answer by mandy.work Tuesday, August 25, 2009 4:45 AM
    Saturday, August 22, 2009 12:20 PM

All replies

  • Hi Mandy,

    Can you clarify what you mean by "download data in bulk"? Are the changes being downloaded via sync? The @sync_last_recevied anchor corresponds to the maximum anchor value of the row on the server that has been sent to the client. The anchor value for the same row on client is expected to be different and if you are using time then it will be the time the row was downloaded not the time the row was inserted/updated on the server. They are essentially two different things and should not be compared.

    It seems client is essentially uploading data that it just downloaded from the server. This loopback has to be handled specifically by marking the downloaded changes. The SqlCeClientSyncProvider does this automatically by turning off tracking of changes that are applied by sync. I assume you are using the Sql Express client sync provider sample on client side. You might have to manually handle this loopback. One way to do this is to have a column on the base table that is set to a well known value when sync applies the change. Any local change should reset the value of this column to null. You can then filter out any changes that have been sent from the server to the client by using this column.

    thanks
    Sudarshan
    Development Lead , Microsoft
    Thursday, August 20, 2009 10:10 PM
    Moderator
  • I am facing the same issue.

    I downloaded the sample from http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200

    And looks like others are also facing the same http://social.msdn.microsoft.com/forums/en-US/uklaunch2007ado.net/thread/181fede5-5b91-4c15-9bc0-dfc303187772/

    The issue is the value of Create_timestamp of all the downloaded records is greater than @sync_last_received_anchor.  Or the framework is not correctly picking the Received anchor value.

    Anybody know why?

    Friday, August 21, 2009 5:02 AM
  • Sudarshan, I guess you almost got my issue.

    Difference is, I am not trying to Upload data that is just downloaded from the server rather I am trying to Update the rows, just downloaded from server.

    I noticed that when I download the changes from server to client the @sync_last_received_anchor value in anchor table is less than the create_timestamp of all the rows. isn't it wiered?

    So when I update the record in client and try to synchronize it, the records get selected in IncrementalInserts stored procedure at client side.
    [create_timestamp > @sync_last_received_anchor] and skipped in IncrementalUpdate stored procedure [create_timestamp <= @sync_last_received_anchor]

    Friday, August 21, 2009 2:06 PM
  • Mandy,

    I get your scenario now. To be clear the create_timestamp of the downloaded rows should not be greater than the ReceivedAnchor value in the anchor table. Note that this is not the same as the @sync_last_received_anchor value passed to the enumeration stored procs. The values are swapped. Here is what I mean.

    The client stores the following anchor values in the anchor table:

    SentAnchor - The timestamp of the last change sent to the server.
    During upload the client will read the SentAnchor value and use it to filter the rows in its local database to decide which rows have to be sent to the server. In the enumeration stored proc on client database this becomes the sync_last_received_anchor parameter. Essentially the last received by server.

    ReceivedAnchor - The timestamp of the last change received from the server.
    During download the client will read the ReceivedAnchor value and use it to filter the rows on the server to decide which rows have to be sent to the client. In the enumeration stored proc on server database this becomes the sync_last_received_anchor.

    Concept of Originator id:

    If you just used the watermarks then after a download all changes just downloaded will qualify for upload to the server in the next sync. To prevent this loopback a column update_originator_id is added to the local database. If you look at the enumerate stored procs (sp_orders_incrinserts and sp_orders_incrupdates) you will notice that in addition to the watermark check there is also a check for update_originator_id =0. This differentiates real local changes from changes applied during download. Sync sets the update_originator_id to 1 and the update trigger sets it to 0.

    What is missing in the sample is a create_originator_id check in the sp_orders_incrinserts. A column needs to be added to the orders table on client database. The value of this column should be set to 1 when sync applies the insert. This will prevent the local update from being enumerated incorrectly as a insert. Can you try making this change in your project? 

    I believe the anchors are behaving as expected.

    thanks
    Sudarshan

    Development Lead , Microsoft
    Friday, August 21, 2009 6:23 PM
    Moderator
  • Thanks for explanation!

    Friday, August 21, 2009 7:17 PM
  • Many thanks for the reply Sudarshan. I was really waiting for it :)

    Okey just to be on same page lets refer to example here http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200
    If we look at the sp,
    ALTER PROCEDURE [dbo].[sp_orders_incrinserts] (@sync_last_received_anchor binary(8),@sync_new_received_anchor binary(8) )
    as
    
    SELECT o.order_id, o.order_date FROM orders o 
    WHERE o.create_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor 
    and o.update_originator_id = 0
    ORDER BY o.create_timestamp
    

    According to your explanation : @sync_last_received_anchor is Sent anchor in anchor table and @sync_new_received_anchor is @@DBTS.
    And we are also filtering the records with o.update_originator_id = 0

    When debugged I noticed, create_timestamp of downloaded records from server is greater than @sync_last_received_anchor so such records get selected during Synchronization cycle 2 (Cycle 1 - Download records from server and update few records on client and then synchronize)

    And at the same time in [sp_order_incrupdates] updated records are not getting selected because of following condition

    o.create_timestamp <= @sync_last_received_anchor


    because create_timestamp of downloaded records from server is greater than @sync_last_received_anchor

    Did you mean, we need to add another column create_originator_id in orders table in addition to update_originator_id and set it's value to 1 upon download and add a check in IncrInsert stored procedure as create_originator_id = 1?


    Looks like others are facing the same problem http://social.msdn.microsoft.com/forums/en-US/uklaunch2007ado.net/thread/181fede5-5b91-4c15-9bc0-dfc303187772/

    Do let me know your thoughts. Thanks again !

    • Edited by mandy.work Friday, August 21, 2009 7:32 PM
    Friday, August 21, 2009 7:20 PM
  • Mandy,

    You will have to do the following to make the change for orders table. Same applies to other tables.

    1. Add a column create_originator_id to orders table with default value of 0.
    2. Change sp_orders_applyinsert proc to set create_originator_id to 1 instead of setting update_originator_id to 1.
    3. Change the where clause in sp_orders_incrinserts to the following.

     

    WHERE o.create_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor and o.create_originator_id = 0

     

    ORDER BY o.create_timestamp

    4. Change the where clause in sp_orders_incrupdates to the following.

     

    WHERE (o.create_timestamp <= @sync_last_received_anchor or o.create_originator_id = 1) and

    o

    .update_timestamp between @sync_last_received_anchor + 1 and @sync_new_received_anchor

     

    and o.update_originator_id = 0


    This should do it. Now the subsequent sync should pick up the local update as a update and not as an insert. Let me know how that goes.

    thanks
    Sudarshan
    Development Lead , Microsoft
    • Marked as answer by mandy.work Tuesday, August 25, 2009 4:46 AM
    Friday, August 21, 2009 11:01 PM
    Moderator
  • Sudarshan, it worked only after minor change in step 2

    2. Change sp_orders_applyinsert proc to set create_originator_id to 1 instead of setting update_originator_id to 1.

    Here we need also update the update_originator_id to 1 otherwise unedited records will also get selected in dbo.sp_orders_incrupdates. Will execute all other test for bidirectional sync and let you know.

     

    So is this a work around or we just fixed bug in a sample provided by Microsoft?

     

    One more confusion in my mind, as explained by you ‘ReceivedAnchor’ from anchor table is used as @sync_last_received_anchor at server end; I check the @sync_last_received_anchor but it is different from the one stored in anchor table of client. Could you please throw some more light?

     

    Thank you very much for your time again!!!

    • Marked as answer by mandy.work Tuesday, August 25, 2009 4:45 AM
    Saturday, August 22, 2009 12:20 PM
  • Hi Mandy,

    Agree with your point about setting update_originator_id as well. This change will obviously require some testing before deploying. For now this can be used as a workaround. The sample will probably get updated with this fix at some point.

    How are you checking the anchors. The one stored in the table is a serialized form of a .net type. Are you deserializing it and accessing the value from it? If you look at the sample code for the express provider you will see that the ReceivedAnchor value is read and then sent to the server side.

    thanks
    Sudarshan
    Development Lead , Microsoft
    Thursday, August 27, 2009 5:28 PM
    Moderator
  • yeah I am also testing it with different scenarios, so far so good...for anchors I am casting them as bigint and inserting to trace table during select @sync_new_received_anchor = @@DBTS at client as well as server.

    So updated understanding : SentAnchor and ReceivedAnchor at table level are used to enumerate records at client as well as server end. we don't maintain any other set of anchors for server. We maintain/persist synchronization anchors at table level only at client end.

    Bidirectional Sync - Algorithm

    1. Read Sent anchor from anchor table
    2. Read NewReceivedAnchor = @@DBTS at client
    3. Read all the incremental INSERT/UPDATE/DELETE in client tables between Sent and ReceivedAnchor
    4. Call apply XXX on server depending upon selection in step 3

    5. Send ReceivedAnchor to server as @sync_last_received_anchor
    6. Read NewReceivedAnchor = MIN_ACTIVE_ROWVERSION()-1 at server

    7. Read all the incremental INSERT/UPDATE/DELETE in Server tables between @sync_last_received_anchor and NewReceivedAnchor
    8. Call apply XXX on client depending upon selection in step 5

    9. Update NewReceived anchor from client as SentAnchor in client's anchor table.
    10. Update NewReceived anchor from server as ReceivedAnchor in client's anchor table

    Could you please verify/confirm this?

    Thursday, August 27, 2009 7:03 PM
  • Sudarshan, is there any other way to deseializing the varbinary anchors in anchor table?
    Thursday, August 27, 2009 7:11 PM