How to: Handle Data Conflicts and Errors for Database Synchronization (SQL Server)
This topic shows you how to handle data conflicts and errors when using Sync Framework to synchronize SQL Server and SQL Server Compact databases. The examples in this topic focus on the following Sync Framework types and events:
ApplyChangeFailed event, and DbApplyChangeFailedEventArgs
For more information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Data Conflicts and Errors
In Sync Framework database providers, conflicts and errors are detected at the level of the row. A row is in conflict when it has been changed at more than one node between synchronizations. Errors that occur 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: update a table at only one node, or filter data so that only one node updates a particular row. 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 Framework provides a set of features that applications can use to detect and resolve conflicts.
Data conflicts can occur in any synchronization scenario in which changes are made at more than one node. 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 one node and the same row is updated at another node, there is a conflict when Sync Framework tries to upload and apply the update at the first node.
Conflicts are always between the two nodes that are currently synchronizing. Consider the following scenario:
Node A and node B both perform bidirectional synchronization with node C.
A row is updated at node A, and then node A synchronizes. There is no conflict, and the row is applied at node C.
The same row is updated at node B, and then node B synchronizes. The row from node B is now in conflict with the row from node C because of the update that originated at node A.
If you resolve this conflict in favor of node C, Sync Framework can apply the row from node C to node B. If you resolve in favor of node B, Sync Framework can apply the row from node B to node. During a later synchronization between node A and node C, the update that originated at node B is applied to node A.
Kinds of Conflicts and Errors
Sync Framework detects the following kinds of conflicts. These are defined in the DbConflictType enumeration:
A LocalInsertRemoteInsert conflict occurs when two nodes insert a row that has the same primary key. This kind of conflict is also known as a primary key collision.
A LocalUpdateRemoteUpdate conflict occurs when two nodes change the same row. This is the most common kind of conflict.
A LocalUpdateRemoteDelete and LocalDeleteRemoteUpdate conflicts occur when one node updates a row that the other node has deleted.
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 ApplyChangeFailed event is raised. The provider raises the error for the node at which the conflict is detected. For example, if you specify a value of UploadAndDownload for the Direction property, changes are first uploaded from the local provider to the remote provider. In this case, the event is raised by the provider that you specified for the RemoteProvider property. If changes were first downloaded and then uploaded, the event would be raised by the provider that you specified for the LocalProvider property. Regardless of which provider raises the event and where the synchronization components are located, the data change at the node on which the event is raised is considered the local change (LocalChange), and the other row is considered the remote change (RemoteChange). This differs from client and server synchronization, in which ClientChange and ServerChange are always associated with, respectively, the client database and the server database.
After the ApplyChangeFailed event is raised, conflicting rows are selected by a stored procedure that Sync Framework creates for each table when a database is provisioned for synchronization. By default, this procedure is named <TableName>_selectrow. Sync Framework executes this procedure when an insert, update or delete operation returns a @sync\_row\_count value of 0. This value indicates that the operation failed.
Conflict and Error Resolution
Conflict and error resolution should be handled in response to the ApplyChangeFailed event. The DbApplyChangeFailedEventArgs object provides access to several properties that can be used during conflict resolution:
Specify how to resolve the conflict by setting the Action property to one of the values of the ApplyAction enumeration:
Continue: ignore the conflict and continue synchronization.
RetryApplyingRow and RetryNextSync: 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. Specifying this option sets the session variable @sync\_force\_write to 1. The "Examples" section of this topic shows how a remote change is forced to overwrite a local change based on the logic in the update stored procedure that Sync Framework creates.
Obtain the conflict type and view the conflicting rows from the each node by using the Conflict property.
Obtain the dataset of changes that is being synchronized by using the Context property. The rows that are 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.
Nota
The Sync Framework API contains one type and one property that are related to conflict resolution but that are not used in this version of the API: DbResolveAction and ConflictResolutionPolicy.
Examples
The following code examples show how to configure conflict detection and resolution.
Key Parts of the API
This section provides code examples that point out the key parts of the API used in conflict detection and resolution. The following code example shows the stored procedure that Sync Framework uses to apply updates to the Customer table. This procedure performs an update based on the value of the @sync\_force\_write parameter. If the row has been updated in the local database and the parameter is set to 0, the remote update is not applied. However, if the parameter is set to 1, the remote update overwrites the local update.
CREATE PROCEDURE [Sales].[Customer_update]
@CustomerId UniqueIdentifier,
@CustomerName NVarChar(100),
@SalesPerson NVarChar(100),
@CustomerType NVarChar(100),
@sync_force_write Int,
@sync_min_timestamp BigInt,
@sync_row_count Int OUTPUT
AS
BEGIN
UPDATE [Sales].[Customer] SET [CustomerName] = @CustomerName,
[SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType FROM
[Sales].[Customer] [base] JOIN [Sales].[Customer_tracking] [side] ON
[base].[CustomerId] = [side].[CustomerId] WHERE
([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR
@sync_force_write = 1) AND ([base].[CustomerId] = @CustomerId); SET
@sync_row_count = @@ROWCOUNT;
END
GO
The following code example shows how update-update conflicts can be processed in an ApplyChangeFailed event handler. In the example, the conflicting rows are displayed to the console with an option to specify which row should win the conflict. If you run the complete code example at the end of this topic, you will see two sets of conflicting rows: when node 1 synchronizes with node 2 and when node 2 synchronizes with node 3.
The following code example logs error information to a file.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.