Custom conflict resolution using a VERIFY clause

SQL Remote generates UPDATE statements in the messages that use the VERIFY clause. An UPDATE statement changes the value of one or more rows from the existing value to a new value. UPDATE statements that include a VERIFY clause also contain the existing value of the row.

When applying an UPDATE statement, the consolidated database compares its existing value of the row with what the remote database expects the existing value of the row to be. An update conflict is detected by the database server when the VERIFY clause values don't match the rows in the database.

For example, 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 sent and applied to the consolidated database.

  4. The update from User 2 is sent to the consolidated database.

    Because the UPDATE statement contains a VERIFY clause, the consolidated database can detect conflicts. On the consolidated database, SQL remote compares the value in its row with the old row value that User 2 sent. As these values are not the same, there is an update conflict.

    When an update conflict is detected, the consolidated database:

    1. Fires any conflict resolution triggers defined for the operation.

      You define conflict resolution triggers to handle update conflicts. Conflict resolution triggers are fired only at a consolidated database, when messages are applied by a remote user.

    2. Executes the UPDATE statements.

    3. Sends any actions of the conflict resolution trigger and the UPDATE statement to all remote databases, including the sender of the message that triggered the conflict.

      Typically, SQL Remote does not replicate the actions of triggers; the trigger is assumed present on the remote database. Conflict resolution triggers are fired only on consolidated databases, and so their actions are replicated to remote databases.

  5. The remote databases receives the UPDATE statements from the consolidated database.

    On remote databases, RESOLVE UPDATE triggers are not fired when a message from a consolidated database contains an update conflict.

  6. On the remote database, the UPDATE statements are processed.

    At the end of the process, the data is consistent throughout the system.

 See also

UPDATE statements with a VERIFY clause
The verify_all_columns option

Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1