Managing conflicts

An UPDATE conflict occurs when the following sequence of events takes place:

  1. User 1 updates a row at remote site 1.
  2. User 2 updates the same row at remote site 2.
  3. The update from User 1 is replicated to the consolidated database.
  4. The update from User 2 is replicated to the consolidated database.

When the SQL Remote Message Agent replicates UPDATE statements, it does so as a separate UPDATE for each row. Also, the message contains the old row values for comparison. When the update from user 2 arrives at the consolidated database, the values in the row are not those recorded in the message.

Conflict resolution, with the second update overwriting the first.
Default conflict resolution

By default, the UPDATE still proceeds, so that the User 2 update (the last to reach the consolidated database) becomes the value in the consolidated database, and is replicated to all other databases subscribed to that row.

In general, the default method of conflict resolution is that the most recent operation (in this case that from User 2) succeeds, and no report is made of the conflict. The update from User 1 is lost. SQL Remote also allows custom conflict resolution, using a trigger to resolve conflicts in a way that makes sense for the data being changed.

Conflict resolution does not apply to primary key updates

UPDATE conflicts do not apply to primary key updates. You should not update primary keys in a SQL Remote installation. Primary key conflicts must be excluded from the installation by proper design.

This section describes how you can build conflict resolution into your SQL Remote installation at the consolidated database.

How SQL Remote handles conflicts
Implementing conflict resolution
Using conflict resolution triggers
Conflict resolution examples
Designing to avoid referential integrity errors
Designing triggers to avoid errors

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0