none
Sync Framework Update Conflict RRS feed

  • Question

  • Hello,

    I'm using Sync Framework 2.1 for p2p SQLServer database synchronization. My scenario is:POS terminals (SQL Server 2005 Express SP3) do sinchronization for some tables (I have created a scope for that tables) with master database (SQL Server 2008 SP2). The data for every POS terminal are different from the data of each other. They have a fields identifying them uniquely. I have developed scheduler that starts sichronization for all POS terminals at the same time. They are queued (sorded by name). Sinchronization runs consecutively, so POS terminals sinchronized their data with the master database.

    For the sinchronization I have created two SqlSyncProvider(s) so that POS terminals are remote providers and master database is a local provider.

    Direction = Download

    I did a scope provisioning on a local and remote providers.

    During the sinchronization time there is no exceptions, so I think that all is OK, but it's not. In the sync trace log file I saw that if there is some changes in the POS terminal (remote provider), they are detected and downloaded on the local provider but update has failed. However coresponding tracking tables are updated.

    I did the following test:

    1.change the value on one field in one record for the POS terminal database table included in my scope

    2. Run sinchronization for the peers (terminal from step 1 and master database)

    Result:

    My custom log file:

    Synced scope: MyScope
    StartTime: 7/10/2012 7:00:01 PM
    Total Changes Downloaded: 1
    Complete Time: 7/10/2012 7:00:06 PM

    SyncTrase.log

    INFO   , SyncService, 6, 07/10/2012 16:00:04:266,    ----- Table "MyTable" -----
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:376,       Executing Command: [MyTable_selectchanges]
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:376,          Parameter: @sync_min_timestamp Value: 10006242
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:377,          Parameter: @sync_scope_local_id Value: 27
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:377,          Parameter: @sync_scope_restore_count Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:377,          Parameter: @sync_update_peer_key Value: 1
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:543,       Update for row with PK: MyField_1 = 0, MyField_2 = PO35122, Store = Store_001 on MYDATABASE
    VERBOSE, SyncService, 6, 07/10/2012 16:00:04:543,          UV: 0,10006265 CV: 0,9862119
    INFO   , SyncService, 6, 07/10/2012 16:00:04:574,       Inserts: 0
    INFO   , SyncService, 6, 07/10/2012 16:00:04:574,       Deletes: 0
    INFO   , SyncService, 6, 07/10/2012 16:00:04:574,       Updates: 1
    INFO   , SyncService, 6, 07/10/2012 16:00:04:574,       Changes Enumerated: 1
    INFO   , SyncService, 6, 07/10/2012 16:00:04:574,    --- End Table "MyTable" ---

    INFO   , SyncService, 6, 07/10/2012 16:00:05:146,    ----- Updates for Table "MyTable" -----
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:336,    Executing Command: [MyTable_bulkupdate]
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:336,       Parameter: @sync_min_timestamp Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:336,       Parameter: @sync_scope_local_id Value: 7
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:400,       Parameter: @changeTable Value: MyTable
    INFO   , SyncService, 6, 07/10/2012 16:00:05:681, Applied 0 of 1 rows with bulk command BulkUpdateCommand
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:688,    Failed to update row with PK using bulk apply: MyField_1="0" MyField_2 ="PO35122" Store="Store_001"  on MyDatabase
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:732,
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:733,    RowId: mytable-0-PO35122-Store_001 UV: 72,10006265 CV: 72,9862119 IsTomb: False
    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:956,    Updating row with PK: MyField_1="0" MyField_2="PO35122" Store="Store_001"  on MYDATABASE
    INFO   , SyncService, 6, 07/10/2012 16:00:05:976, Optimistic change application failed
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:124,    Executing Command: [MyTable_selectrow]
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:127,       Parameter: @P_2 Len: 1 Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:130,       Parameter: @P_3 Len: 13 Value: PO35122
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:132,       Parameter: @P_15 Len: 6 Value: Store_001
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:134,       Parameter: @sync_scope_local_id Value: 7
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:135,       Parameter: @sync_scope_restore_count Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:212,
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:212,    RowId: mytable-0-PO35122-Store_001 UV: 0,160107217 CV: 15,9702029 IsTomb: False
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:229,             Checking for conflict.
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:229,             Change cannot be applied. Returning Conflict.
    WARNING, SyncService, 6, 07/10/2012 16:00:06:229, Conflict detected while applying update
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:262,    Local UV: 0,160107217 CV: 15,9702029 IsTomb: False
    INFO   , SyncService, 6, 07/10/2012 16:00:06:262,          Local Wins, update metadata
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:268,             New local UpdateVersion: 0,163193444
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,    Executing Command: [MyTable_updatemetadata]
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @P_2 Len: 1 Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @P_3 Len: 13 Value: PO35122
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @P_15 Len: 6 Value: Store_001
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_scope_local_id Value: NULL
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_row_is_tombstone Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_create_peer_key Value: 15
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_create_peer_timestamp Value: 9702029
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_update_peer_key Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_update_peer_timestamp Value: 163193444
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_check_concurrency Value: 1
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_row_timestamp Value: 160107217
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:304,       Parameter: @sync_row_count Value: 0
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:426,    Rows affected: 1
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:426,
    INFO   , SyncService, 6, 07/10/2012 16:00:06:426,       1 Updates Applied
    INFO   , SyncService, 6, 07/10/2012 16:00:06:426,    --- End Updates for Table "MyTable" ---

    My question is: How can I solve that problem?

    Thank you.

    Tuesday, July 10, 2012 4:22 PM

All replies

  • what is your setting for conflict resolution?

    i suggest you subscribe to the ApplyChangeFailed event and specify explicitly how you want to resolve conflicts.

    Wednesday, July 11, 2012 1:08 AM
    Moderator
  • Hello JuneT,

    Thank you for the quick answer.

    My case is a bit complicated. The POS terminal's software we used is LS Retail. A third party company has been created additional funtionality. They have created three tables (with composite primary key), that we have to sinchronize. I have created a scope fot that tables and provisioned it on the local and remote provider (described in my early post). When the scope is provisioning, Sync Fx 2.1 creates an insert, update and delete triggers as well as stored procedures. The logic of that triggers and stored procedures is equal to the logic of triggers and stored procedures of the tables (originaly created by LS) participating in other scopes. The mentioned other scopes sinchronized successfully (insert, update and delete) without subscribtion to the ApplyChangeFailed event. I don't understand what is the reason the original tables(created by LS) to be sinchronized successfully, but the sinchronization of the new tables (created by the third party company) to be failed. That is the question!

    I have found a lot of examples for ApplyChangedFailed event but they described how to create stored procedures for select changes and update conflict, which Sync Fx 2.1. created during scope provisioning time. I'm mixed-up. Is there any good explanation how to create ApplyChangeFailed event for any sync scenario deppend on Direction?

    Regards,

    Lucather.

    Wednesday, July 11, 2012 8:21 AM
  • the ApplyChangeFailed event doesnt require you to write any stored procedure, the OOTB provisioning has taken care of that for you already.

    sync will work without the ApplyChangeFailed event, what it does is inform you if a conflict or an error has occured in applying a change.

    if you want to listen to conflicts in the case of an upload, you listen to the event in the remote provider.

    if you want to listen to conflicts in the case of a download, you listen to the local provider.

    for bidirectional, both.

    these lines already tell you there is a problem applying the change:

    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:688,    Failed to update row with PK using bulk apply: MyField_1="0" MyField_2 ="PO35122" Store="Store_001"  on MyDatabase

    VERBOSE, SyncService, 6, 07/10/2012 16:00:05:956,    Updating row with PK: MyField_1="0" MyField_2="PO35122" Store="Store_001"  on MYDATABASE
    INFO   , SyncService, 6, 07/10/2012 16:00:05:976, Optimistic change application failed

    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:229,             Checking for conflict.
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:229,             Change cannot be applied. Returning Conflict.
    WARNING, SyncService, 6, 07/10/2012 16:00:06:229, Conflict detected while applying update
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:262,    Local UV: 0,160107217 CV: 15,9702029 IsTomb: False
    INFO   , SyncService, 6, 07/10/2012 16:00:06:262,          Local Wins, update metadata
    VERBOSE, SyncService, 6, 07/10/2012 16:00:06:268,             New local UpdateVersion: 0,163193444

    have you checked that record to see why it would not update? FK violations, null values, etc...

    Wednesday, July 11, 2012 8:30 AM
    Moderator
  • Hi JuneT,

    I fond an example that show me the records from local and remote provider when the conflict occurs.

            static void SyncProvider_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
            {
                ////Verbose tracing includes information about conflicts. 
                ////Check if Verbose tracing is enabled and if the conflict is an error.
                ////If the conflict is not an error, we write a warning to the trace file
                ////with information about the conflict.
                if (SyncTracer.IsVerboseEnabled() == false && e.Conflict.Type != DbConflictType.ErrorsOccurred)
                {
                    DataTable conflictingClientChange = e.Conflict.LocalChange;
                    DataTable conflictingServerChange = e.Conflict.RemoteChange;
                    int serverColumnCount = conflictingServerChange.Columns.Count;
                    int clientColumnCount = conflictingClientChange.Columns.Count;
                    StringBuilder clientRowAsString = new StringBuilder();
                    StringBuilder serverRowAsString = new StringBuilder();
    
                    for (int i = 0; i < clientColumnCount; i++)
                    {
                        clientRowAsString.Append(conflictingClientChange.Rows[0][i] + " | ");
                    }
    
                    for (int i = 0; i < serverColumnCount; i++)
                    {
                        serverRowAsString.Append(conflictingServerChange.Rows[0][i] + " | ");
                    }
    
                    SyncTracer.Warning(1, "CONFLICT DETECTED FOR SESSION {0}", e.Session.SessionId);
                    SyncTracer.Warning(2, "** Client change **");
                    SyncTracer.Warning(2, clientRowAsString.ToString());
                    SyncTracer.Warning(2, "** Server change **");
                    SyncTracer.Warning(2, serverRowAsString.ToString());
                }
            }        

    I subscribe to the ApplyChangeFailed to the local provider. The result is:

    WARNING, SyncService, 8, 07/01/2012 20:14:02:584, Conflict detected while applying update
    WARNING, SyncService, 8, 07/01/2012 20:14:02:632,    CONFLICT DETECTED FOR SESSION fcb47c9e-7e98-4900-ab34-572ed5fbbab9
    WARNING, SyncService, 8, 07/01/2012 20:14:02:632,       ** Client change **
    WARNING, SyncService, 8, 07/01/2012 20:14:02:632,       0 | PO35122 | Store_001 | 5413 | 00248680 |  | ENAN | 6/26/2012 12:00:00 AM | 6/26/2012 12:00:00 AM | 0 | 15 | 0 | 0 | 0.00000000000000000000 | 0.00000000000000000000 | 0 | 160107215 | 0 | 9702029 | 15 |
    WARNING, SyncService, 8, 07/01/2012 20:14:02:632,       ** Server change **
    WARNING, SyncService, 8, 07/01/2012 20:14:02:633,       5413 | 0 | PO35122 | 00248680 |  | ENAN | 6/26/2012 12:00:00 AM | 6/26/2012 12:00:00 AM | 2 | 15 | 33 | 33 | 158.47000000000000000000 | 158.47000000000000000000 | Store_001 | 0 | 9948774 | 0 | 9862119 | 0 |

    In my case "client change" is change of local provider, "server change" is change of remote provider.

    As far as see the order of columns is different in local and remote providers. In the SQL Server Management Studion I saw that the table definitions (column order) are equals on the local and remote providers.

    I am totally mixed-up! Any suggestions?

    Regards,

    Lucather.



    • Edited by Lucather Wednesday, July 11, 2012 10:02 AM the changes are in bold
    Wednesday, July 11, 2012 9:08 AM
  • so what's the conflict type? what's the value for this: e.Conflict.Type

    looking at your rows, they have different values, must be an update-update conflict, the row is updated on client and was updated on the server as well.

    which one do you want to win?

    the code you added is simply displaying the conflicting row, you should check what type of conflict and decide which one you want to win.

    see this link: http://msdn.microsoft.com/en-us/library/cc761628

    you should find the same information in the documentation that gets installed with the framework.

    Wednesday, July 11, 2012 10:03 AM
    Moderator
  • In my case the client is master database (that consolidate the data from POS terminals). This database (used only for BI) is never updated except during the synchronization. The server is the POS terminal, so that is the database wich is updated. In this situation (Direction = Download, client download data from the server), The client should be the winner. I dont understand why in other scope mentioned in my early post (in the same situation) there is no problem with update, so I sould not define who is the winner. 

    Thank you for your guidelines. I will check wahat's the value for e.Conflict.Type and get back to you with result.

    Wednesday, July 11, 2012 10:32 AM
  • The value for e.Conflict.Type is : LocalUpdateRemoteUpdate
    Wednesday, July 11, 2012 3:52 PM
  • so which one do you want to keep?

    if sync is supposed to be the only one updating the master, why do you have an update on the master?

    do you have overlapping scopes or different scopes having the same table?

    Thursday, July 12, 2012 6:29 AM
    Moderator
  • Hi, JuneT,

    I don't understand why conflict type is LocalUpdateRemoteUpdate in fact that LocalUpdate is never ocurrs, because the master is never update except during the sichronization (as I mentioned in my early post). 

    There is not overlapping scopes.

    In the ApplyChangeFailed event I do the following:

                if (e.Conflict.Type == DbConflictType.LocalUpdateRemoteUpdate)
                {
                    e.Action = ApplyAction.RetryWithForceWrite;
                }

    Is ist correct?

    In my case the winner is POS terminal record.

    Regards,

    Lucather

    Thursday, July 12, 2012 1:09 PM
  • yes, retrywithforcewrite will make the POS row win.
    Thursday, July 19, 2012 12:50 PM
    Moderator