Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Remote » SQL Remote Replication Design » SQL Remote replication design and set up » Update conflicts » Custom conflict resolution using triggers


Resolving inventory conflicts

Consider a warehouse system for a manufacturer of sporting goods. There is a table of product information, with a Quantity column holding the number of each product left in stock. An update to this column typically depletes the quantity in stock or, if a new shipment is brought in, adds to it.

A sales representative at a remote database enters an order, depleting the stock of small, tank top, tee shirts by five, from 28 to 23, and enters this in her database. Meanwhile, before this update is replicated to the consolidated database, another sales representative receives 40 returned tee shirts. This sales representative enters the returns into his remote database and replicates the changes to the consolidated database at the warehouse, adding 40 to the Quantity column to make it 68.

The first update adds 40 to the Quantity column.

The warehouse entry is added to the database: the Quantity column now shows there are 68 small tank-top tee shirts in stock. When the update from the first sales representative arrives, it causes a conflict—SQL Anywhere detects that the update is from 28 to 23, but that the current value of the column is 68.

By default, the more recent update succeeds, and the inventory level is set to the incorrect value of 23.

Incorrect conflict resolution: the second update incorrectly overwrites the first update.

In this example, the conflict should be resolved by summing the changes to the inventory column to produce the result, so that a final value of 63 is placed into the database.

Correct conflict resolution: the second update modifies the first update.
Implementing the solution

A suitable RESOLVE UPDATE trigger for this situation would add the increments from the two updates. For example:

CREATE TRIGGER resolve_quantity
 ON "DBA".Products
  NEW AS new_name
  REMOTE AS remote_name
   SET new_name.Quantity = new_name.Quantity
                        + old_name.Quantity
                        - remote_name.Quantity

This trigger adds the difference between the old value in the consolidated database (68) and the old value in the remote database when the original UPDATE statement was executed (28) to the new value being sent, before the UPDATE statement is implemented. So, new_name.Quantity becomes 63 (= 23 + 68 - 28), and this value is entered into the Quantity column.

Consistency is maintained on the remote database as follows:

  1. The original remote UPDATE statement changed the value from 28 to 23.

  2. The warehouse's entry is replicated to the remote database, but fails as the old value is not what was expected.

  3. The changes made by the RESOLVE UPDATE trigger are replicated to the remote database.