How to: Handle Data Conflicts and Errors
This topic shows you how to handle data conflicts and errors in Sync Framework. The examples in this topic focus on the following Sync Framework types and events:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.
Understanding Data Conflicts and Errors
In Sync Framework, conflicts and errors are detected at the level of the row. A row is in conflict if it has been changed at more than one node between synchronizations. Errors during synchronization typically involve a constraint violation, such as a duplicate primary key. Applications should be designed to avoid conflicts if they can, because conflict detection and resolution introduce additional complexity, processing, and network traffic. The most common ways to avoid conflicts are as follows: to update a table at only one node (typically the server); or to filter data so that only one node updates a particular row. For more information about filtering, see How to: Filter Rows and Columns. In some applications, conflicts cannot be avoided. For example, in a sales force application, two salespeople might share a territory. Both salespeople could update the data for the same customer and orders. Therefore, Sync Services provides a set of features that can be used to detect and resolve conflicts.
Data conflicts can occur in any synchronization scenario in which changes are made at more than one node. Obviously, conflicts can occur in bidirectional synchronization, but they can also occur in download-only and upload-only synchronization. For example, if a row is deleted at the server and the same row is updated at the client, there is a conflict when Sync Services tries to apply the update that is uploaded to the server. Conflicts are always between the server and the client that is currently synchronizing. Consider the following example:
Client A and client B synchronize with the server.
A row is updated at client A, and then client A synchronizes. There is no conflict, and the row is applied at the server.
The same row is updated at client B, and then client B synchronizes. The row from client B is now in conflict with the row from the server because of the update that originated at client A.
If you resolve this conflict in favor of the server, Sync Services can apply the row from the server to client B. If you resolve in favor of client B, Sync Services can apply the row from client B to the server. During a later synchronization between client A and the server, the update that originated at client B is applied to client A.
Types of Conflicts and Errors
Sync Services detects the following types of conflicts. These are defined in the ConflictType enumeration:
A ClientInsertServerInsert conflict occurs when the client and server both insert a row with the same primary key. This type of conflict is also known as a primary key collision.
A ClientUpdateServerUpdate conflict occurs when the client and server change the same row. This is the most common type of conflict.
A ClientUpdateServerDelete conflict occurs when the client updates a row and the server deletes the same row.
A ClientDeleteServerUpdate conflict occurs when the client deletes a row and the server updates the same row.
An ErrorsOccurred conflict occurs when an error prevents a row from being applied.
Conflict and Error Detection
If a row cannot be applied during synchronization, it is typically because either an error or a data conflict occurred. In both cases, the DbServerSyncProvider ApplyChangeFailed event or SqlCeClientSyncProvider ApplyChangeFailed event is raised, depending on whether the error or conflict occurred during the upload or download phase of synchronization. If the client ApplyChangeFailed event is raised, Sync Services selects any conflicting rows for you. Then you decide how to resolve those conflicts. If the server ApplyChangeFailed event is raised, conflicting rows are selected by using two commands that you define on the SyncAdapter for each table:
The query or stored procedure that you specify for the SelectConflictUpdatedRowsCommand property selects conflicting rows from the base table in the server database. Sync Services executes this command if an insert, update or delete operation returns a @sync\_row\_count value of 0. This value indicates that the operation failed. This command selects the rows for ClientInsertServerInsert, ClientUpdateServerUpdate, and ClientDeleteServerUpdate conflicts.
The query or stored procedure that you specify for the SelectConflictDeletedRowsCommand selects conflicting rows from the tombstone table in the server database. Sync Services executes this command if the conflicting row was not found in the base table. This command selects the rows for the ClientUpdateServerDelete conflict.
The data from each conflicting row is stored in a SyncConflict collection. This collection could grow large enough to cause an out-of-memory error in the following situations:
There are a large number of conflicting rows. Consider synchronizing a smaller number of rows in each session, or limit the number of conflicts by updating a particular row at only one node.
The conflicting rows contain columns that use large data types. Consider not including the columns that use large data types in the set of columns that is synchronized. For more information, see How to: Filter Rows and Columns.
Conflict and Error Resolution
Conflict and error resolution should be handled in response to the DbServerSyncProvider ApplyChangeFailed event and the SqlCeClientSyncProvider ApplyChangeFailed event. The ApplyChangeFailedEventArgs object provides access to several properties that can be used during conflict resolution:
Continue: ignore the conflict and continue synchronization.
RetryApplyingRow: retry applying the row. The retry will fail, and the event will be raised again if you do not address the cause of the conflict by changing one or both of the conflicting rows.
RetryWithForceWrite: retry with logic to force applying the change. The SqlCeClientSyncProvider has built-in support for this option. To use this option on the server, use the @sync\_force\_write parameter and add support in the commands that apply changes to the server database. For example, for a ClientUpdateServerDelete conflict, you could change the update into an insert when @sync\_force\_write is set to 1. For example code, see the "Examples" section later in this topic.
Obtain the conflict type and view the conflicting rows from the client and server by using the Conflict property.
Obtain the dataset of changes that is being synchronized by using the Context property. The rows exposed by the Conflict property are copies; therefore, overwriting them does not change the rows that are applied. Use the dataset exposed by the Context property to develop custom resolution schemes if the application requires them. For example code, see the "Examples" section later in this topic.
The SqlCeClientSyncProvider also includes a ConflictResolver property that you can use to resolve conflicts on the client. For each type of conflict, you can set a value from the ResolveAction enumeration:
There is no requirement to set the ConflictResolver for each type of conflict. You can resolve conflicts as you do on the server, by handling the ApplyChangeFailed event. However, the ConflictResolver property does provide an easy way to specify conflict resolution options on the client.
The following code examples show how to configure conflict detection and resolution for the Customer table in the Sync Services sample database. In this example, the synchronization commands are manually created, instead of by using SqlSyncAdapterBuilder. You can use conflict detection and resolution with the commands that are generated by SqlSyncAdapterBuilder, but manual commands provide much more flexibility, especially in how you can force the application of conflicting changes.
Key Parts of the API
This section provides code examples that point out the key parts of the API that are used in conflict detection and resolution. The following query selects conflicting rows from the base table in the server database.
The following query selects conflicting rows from the tombstone table in the server database.
The following code example creates a stored procedure that applies updates to the server database. This procedure is specified for the UpdateCommand property. Stored procedures can also be used to apply inserts and deletes to the server database. For examples of these procedures, see Setup Scripts for Database Provider How-to Topics.
The usp_CustomerApplyUpdate update procedure tries either an update or an insert operation, based on the value of the @sync\_force\_write parameter and whether the row to update exists in the server database. If the row does not exist, the procedure turns the update into an insert operation. In this example, the missing row is caused by an update/delete conflict.
The following code example sets conflict resolution options for SqlCeClientSyncProvider. As it is noted earlier, these options are not required, but they provide an easy way to resolve conflicts. In this example, updates should always win in update/delete conflicts, and all other conflicts should raise the client ApplyChangeFailed event.
For client update/server delete conflicts, the update is force-written at the server, as shown in the following code example. The client update/server delete conflict is handled on the server by using the RetryWithForceWrite option in the server ApplyChangeFailed event handler. When you use this option, it means that the @sync\_force\_write parameter is set to 1 when the update stored procedure is called at the server.
The following code example logs conflict information and force-writes any conflicting inserts in the client ApplyChangeFailed event handler.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. Also, be aware that the example provides users of the application a choice of how to resolve update/update conflicts. One of the options is a custom resolution scheme that combines column values from the conflicting rows. Code for the custom resolution scheme is contained in the SampleServerSyncProvider_ApplyChangeFailed and SampleServerSyncProvider_ChangesApplied event handlers. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.