Lesson 6: Creating scripts for conflict detection and resolution

Conflicts arise during the upload of rows to the consolidated database. If two users modify the same row on different remote databases, a conflict is detected when the second row arrives at the MobiLink server. Using synchronization scripts you can detect and resolve conflicts.

For more information about MobiLink Conflict resolution, see Handling conflicts.

Inventory example

Consider the scenario of two salesmen in the field. Salesman1 starts with an inventory of ten items, and then sells three. He updates the inventory on his remote database, remote1, to seven items. Salesman2 sells four items and updates her inventory (on remote2) to six.

When remote1 synchronizes using the MobiLink synchronization client utility the consolidated database is updated to seven. When remote2 synchronizes, a conflict is detected because the inventory value in the consolidated database has changed.

To resolve this conflict programmatically, you need three row values:

The current value in the consolidated database.

After remote1 synchronizes, the value in the consolidated database is 7.
The new row value that Remote2 uploaded.
The old row value that Remote2 obtained during the previous synchronization.

In this case, you can use the following business logic to calculate the new inventory value and resolve the conflict:

current consolidated - (old remote - new remote)
that is, 7 - (10-6) = 3

The expression (old remote - new remote) provides the number of items sold by Salesman2 rather than the absolute inventory value.

Synchronization scripts for conflict detection and resolution

For conflict detection and resolution, you add the following scripts:

upload_update The upload_update event determines how data inserted into the remote database should be applied to the consolidated database. You can also use an extended prototype of upload_update to detect update conflicts.
For more information about using upload_update to detect conflicts, see Detecting conflicts.

For more information about upload_update, see upload_update table event.
upload_old_row_insert You use this script to handle old row values obtained by the remote database during its previous synchronization.
For more information about upload_old_row_insert, see upload_old_row_insert table event.
upload_new_row_insert You use this script to handle new row values (the updated values on the remote database).
For more information about upload_new_row_insert, see upload_new_row_insert table event.
resolve_conflict The resolve conflict script applies business logic to resolve the conflict.
For more information about resolve_conflict, see resolve_conflict table event.

To install scripts for conflict detection and resolution

Start Interactive SQL.
1. At a command prompt, type dbisql.
2. Click the Identification tab.
3. In the User ID field, type DBA.
4. In the Password field, type sql.
5. Click the Database tab.
6. In the Server Name field, type cons.
7. Click OK.

Install the conflict detection and resolution scripts:

Execute the following in Interactive SQL:

/* upload_update */
call ml_add_table_script( 'ver1', 'Product',
 'upload_update',
 'UPDATE Product
   SET quantity = ?, last_modified = ?
    WHERE name = ?
    AND quantity=? AND last_modified=?' )
go

/* upload_old_row_insert */
call ml_add_table_script( 'ver1', 'Product',
 'upload_old_row_insert',
 'INSERT INTO Product_old (name,quantity,last_modified)
   values (?,?,?)')
go

/* upload_new_row_insert */
call ml_add_table_script( 'ver1', 'Product',
 'upload_new_row_insert',
 'INSERT INTO Product_new (name,quantity,last_modified)
   values (?,?,?)')
go

/* resolve_conflict */
call ml_add_table_script( 'ver1', 'Product',
 'resolve_conflict',
 'declare @product_name varchar(128);
  declare @old_rem_val integer;
  declare @new_rem_val integer;
  declare @curr_cons_val integer; 
  declare @resolved_value integer;

  // obtain the product name
  SELECT name INTO @product_name
     FROM Product_old;

  // obtain the old remote value
  SELECT quantity INTO @old_rem_val
    FROM Product_old;

  //obtain the new remote value
  SELECT quantity INTO @new_rem_val
    FROM Product_new;

  // obtain the current value in cons
  SELECT quantity INTO @curr_cons_val
   FROM Product WHERE name = @product_name;

  // determine the resolved value
  SET @resolved_value =
    @curr_cons_val- (@old_rem_val - @new_rem_val);

  // update cons with the resolved value
  UPDATE Product
   SET quantity = @resolved_value
   WHERE name = @product_name;

  // clear the old and new row tables
  delete from Product_new;
  delete from Product_old
 ')

Setup for the SQL Anywhere consolidated database is complete.