Resolving conflicts with upload

Resolving conflicts with upload_update scripts

Instead of using the resolve_conflict script for conflict resolution, you can call a stored procedure in the upload_update script. With this technique, you must both detect and resolve conflicts programmatically.

The stored procedure must accept all columns, including both the new (post-image) and old (pre-image) values.

The upload_update script could be as follows:

{CALL UpdateProduct(
   {ml o.id}, {ml o.name}, {ml o.desc}, {ml r.name}, {ml r.desc}
)
}

The UpdateProduct stored procedure could be:

CREATE PROCEDURE UpdateProduct( 
  @id INTEGER,
  @preName VARCHAR(20), 
  @preDesc VARCHAR(200),
  @postName VARCHAR(20), 
  @postDesc VARCHAR(200) ) 
BEGIN
    UPDATE product
     SET name = @postName, description = @postDesc
     WHERE id = @id
       AND name = @preName
       AND description = @preDesc
    IF @@rowcount=0 THEN
        // A conflict occurred: handle resolution here.
    END IF
END

This approach is often easier to maintain than resolving conflicts with resolve_conflict scripts because there is only one script to maintain and all the logic is contained in one stored procedure. However, the code of the stored procedure may be complicated if the tables columns are nullable or if they contain BLOBs or CLOBs. Also, some RDBMSs that are supported MobiLink consolidated databases have limitations on the size of values that can be passed to stored procedures.

See:

Example

The following stored procedure, sp_update_my_customer, contains logic for conflict detection and resolution. It accepts old column values and new column values. This example uses SQL Anywhere features. The script could be implemented as follows.

{CALL sp_update_my_customer(
   {ml o.cust_1st_pk},
   {ml o.cust_2nd_pk},
   {ml o.first_name},
   {ml o.last_name},
   {ml o.nullable_col},
   {ml o.last_modified},
   {ml r.first_name},
   {ml r.last_name},
   {ml r.nullable_col},
   {ml r.last_modified}
)} 
CREATE PROCEDURE sp_update_my_customer(
     @cust_1st_pk        INTEGER,
     @cust_2nd_pk        INTEGER,
     @old_first_name     VARCHAR(100),
     @old_last_name      VARCHAR(100),
     @old_nullable_col   VARCHAR(20),
     @old_last_modified  DATETIME,  
     @new_first_name     VARCHAR(100),
     @new_last_name      VARCHAR(100),
     @new_nullable_col   VARCHAR(20),
     @new_last_modified  DATETIME
) 
BEGIN
DECLARE @current_last_modified DATETIME;
// Detect a conflict by checking the number of rows that are
// affected by the following update. The WHERE clause compares
// old values uploaded from the remote to current values in
// the consolidated database. If the values match, there is
// no conflict. The COALESCE function returns the first non-
// NULL expression from a list, and is used in this case to
// compare values for a nullable column.


UPDATE my_customer
SET first_name                  = @new_first_name,
 last_name                      = @new_last_name,
 nullable_col                   = @new_nullable_col,
 last_modified                  = @new_last_modified

WHERE cust_1st_pk               = @cust_1st_pk
 AND cust_2nd_pk                = @cust_2nd_pk
 AND first_name                 = @old_first_name
 AND last_name                  = @old_last_name
 AND COALESCE(nullable_col, '') = COALESCE(@old_nullable_col, '')
 AND last_modified              = @old_last_modified;
... 
// Use the @@rowcount global variable to determine
// the number of rows affected by the update. If @@rowcount=0,
// a conflict has occurred. In this example, the database with
// the most recent update wins the conflict. If the consolidated
// database wins the conflict, it retains its current values
// and no action is taken.

IF( @@rowcount = 0 ) THEN
// A conflict has been detected. To resolve it, use business
// logic to determine which values to use, and update the
// consolidated database with the final values.

  SELECT last_modified INTO @current_last_modified
     FROM my_customer WITH( HOLDLOCK )
     WHERE cust_1st_pk=@cust_1st_pk
        AND cust_2nd_pk=@cust_2nd_pk;

     IF( @new_last_modified > @current_last_modified ) THEN
     // The remote has won the conflict: use the values it
     // uploaded.
  
       UPDATE my_customer
       SET first_name    = @new_first_name,
         last_name     = @new_last_name,
         nullable_col  = @new_nullable_col,
         last_modified = @new_last_modified
       WHERE cust_1st_pk  = @cust_1st_pk
          AND cust_2nd_pk   = @cust_2nd_pk;
       
     END IF;
END IF;
END;

See:

COALESCE function [Miscellaneous]
@@rowcount in Global variables

Discuss this page in DocCommentXchange.