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

SQL Anywhere 11.0.1 (中文) » MobiLink - 服务器管理 » 使用 MobiLink 服务器技术 » 同步技术 » 冲突处理 » 解决冲突


使用 upload_update 脚本解决冲突

解决冲突时,您可以调用 upload_update 脚本中的存储过程而不是使用 resolve_conflict 脚本。使用此技术时,必须以编程方式检测并解决冲突。

存储过程必须使用带有 WHERE 子句的 upload_update 脚本格式,此 WHERE 子句包括除使用前映像值(即旧值)的列以外的所有列。

upload_update 脚本可能如下所示:

{CALL UpdateProduct(
   {ml}, {ml}, {ml o.desc}, {ml}, {ml r.desc}

UpdateProduct 存储过程可能是:

  @id INTEGER,
  @preName VARCHAR(20), 
  @preDesc VARCHAR(200),
  @postName VARCHAR(20), 
  @postDesc VARCHAR(200) ) 
    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

与使用 resolve_conflict 脚本解决冲突相比,此方法更易于维护,因为只有一个脚本需要维护,所有逻辑都包含在一个存储过程中。但是,如果表列可以为空,或其中包含 BLOB 或 CLOB,存储过程的代码可能会非常复杂。此外,对于一些受 MobiLink 统一数据库支持的 RDBMS,它们可以传递到存储过程的值有大小限制。



下面的存储过程 sp_update_my_customer 包含用于进行冲突检测和解决的逻辑。它接受旧列值和新列值。此示例使用 SQL Anywhere 功能。可按照以下方法实现此脚本。

{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
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;