Conflict Resolution with Sync Services

Rafik Robeal has posted the fourth Microsoft Synchroniztion Services (OCS) demo project, Offline Application—Conflict Handling, on his The Synchronizer blog. This C# project demonstrates handling Sync Services' ApplyChangesFailed event to determine the failure location (server or client) and type of concurrency conflict, and then specifying an action to ignore or resolve the conflict. Rafik's post, which augments three preceding Sync Services demo projects, describes the ApplyChangesFailedEventArgs.Conflict.ConflictType enumeration:

• ClientInsertServerInsert also known as PK Collision; occurs when both client and server insert a row with same PK
• ClientUpdateServerUpdate the most common conflict; occurs when client and server make changes to the same row independently
• ClientUpdateServerDelete occurs when the client updates a row and the server independently deletes the same row
• ClientDeleteServerUpdate occurs when the client deletes a row and the server independently updates the same row

The SyncForm has four added buttons to generate random conflicts on the client or server:

Actions available to attempt to resolve concurrency conflicts are:

• ApplyAction.RetryApplyingRow without changing to the datarow the retry will fail causing the event to be raised again. The demo does not allow you to edit the datarow though.
• ApplyAction.RetryWithForceWrite has support built in the ClientSyncProvider. On the server side, you need to add support in your sync adapter commands; the demo shows an example for update-update conflict.
• ApplyAction.Continue ignores the conflict
• Abort the sync operations by throwing an exception; all changes will be rolled back and re-synced in the next sync session.

The ConflictForm's four action buttons are enabled as appropriate for the conflict type and location:

After taking the appropriate action, the ProgressForm opens: Rafik explains that the synchronization framework defines the following two commands to find the rows in conflict:

• SyncAdapter.SelectConflictUpdatedRowsCommand this command finds conflicting rows in the base table. The sync runtime executes this command if insert, update or delete command failed (i.e. returned 0 row count)
• SyncAdapter.SelectConflictDeletedRowsCommand this command finds conflicting rows in the tombstone table. The sync runtime executes this command if the conflicting row was not found in the base table. This is how ClientUpdateServerDelete conflict is detected.

The two commands are implemented for the two tables—orders and order_details—by four T-SQL stored procedures on the SQL Server 2005 [Express] server.

Note: Be sure to run the demo.sql script (to the -- Test Sample comment only) to create (or recreate) the pub database and the four server-side tables (orders, order_details, orders_tombstone, and order_details_tombstone) on an SQL Server 2005 [Express] instance, then run the server_procs.sql to generate the stored procedures, and finally build and run OfflineAppDemo-Conflicts.sln. Note that the tombstone tables have a new schema to support multiple deletions of records with the same primary key.

Technorati tags: SSCE, SQL Server 2005 Compact Edition, SQL Server Compact Edition, SQL Server CE, Orcas, Occasionally Connected Systems, OCS, Microsoft Synchronization Services, Sync Services.