Oracle7 Server Distributed Systems Volume II: Replicated Data

Contents Index Home Previous Next

Writing a Conflict Resolution Routine

A conflict resolution routine is a PL/SQL function that returns either TRUE or FALSE. TRUE indicates that the routine has successfully resolved all conflicting modifications for a column group. If the conflict cannot be successfully resolved, the routine should return FALSE. Oracle continues to evaluate available conflict resolution routines, in sequence order, until either a routine returns TRUE or there are no more routines available.

If the conflict resolution routine raises an exception, Oracle stops evaluation of the routine, and, if any other routines were provided to resolve the conflict (with a later sequence number), Oracle does not evaluate them.

Conflict Resolution Routine Parameters

The parameters needed by a conflict resolution routine are determined by the type of conflict being resolved (unique, update, or delete) and the columns of the table being replicated. All conflict resolution routines take some combination of old, new, and current column values for the table.

The old value represents the value of the row at the initiating site before you made the change. The new value represents the value of the row at the initiating site after you made the change. The current value represents the value of the equivalent row at the receiving site. Recall that Oracle uses the primary key (or the key specified by SET_COLUMNS) to determine which rows to compare.

The conflict resolution function should accept as parameters the values for the columns specified in the PARAMETER_COLUMN_NAME argument to the DBMS_REPCAT.ADD_conflicttype_CONFLICT procedures. The column parameters are passed to the conflict resolution routine in the order listed in the PARAMETER_COLUMN_NAME argument, or in ascending alphabetical order if you specified `*' for this argument. Where both old and new column values are passed as parameters (for update conflicts), the old value of the column immediately precedes the new value.

Attention: Type checking of parameter columns in user-defined conflict resolution routines is not performed until the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure generates and compiles the packages.

Resolving Update Conflicts

For update conflicts, the function should accept the following values for each column in the column group:

The old, new, and current values for a column are received consecutively. The final argument to the conflict resolution routine should be a boolean flag. If this flag is FALSE, it indicates that you have updated the value of the IN OUT parameter, new, and that you should update the current column value with this new value. If this flag is TRUE, it indicates that the current column value should not be changed.

Resolving Uniqueness Conflicts

Uniqueness conflicts can occur as the result of an INSERT or UPDATE. Your uniqueness conflict resolution routine should accept the new column value from the initiating site in IN OUT mode for each column in the column group. The final parameter to the conflict resolution routine should be a BOOLEAN flag.

If the routine can resolve the conflict, it should modify the new column values so that the symmetric replication facility can insert or update the current row with the new column values. Your function should set the BOOLEAN flag to TRUE if it wants to discard the new column values, and FALSE otherwise.

Because your conflict resolution routine cannot guarantee convergence for uniqueness conflicts, your routine should include a notification mechanism.

Resolving Delete Conflicts

Delete conflicts occur when you successfully delete from the local site, but the associated row cannot be found at the remote site (for example, because it had been updated). For delete conflicts, the function should accept old column values in IN OUT mode for the entire row. The final parameter to the conflict resolution routine should be a BOOLEAN flag.

If the conflict resolution routine can resolve the conflict, it modifies the old column values so that the symmetric replication facility can delete the current row that matches all old column values. Your function should set the BOOLEAN flag to TRUE if it wants to discard these column values, and FALSE otherwise.

If you perform a delete at the local site and an update at the remote site, the remote site detects the delete conflict, but the local site detects an unresolvable update conflict. This type of conflict cannot be handled automatically. The conflict will raise a NO_DATA_FOUND exception and the transaction will be placed in the error table.

Designing a mechanism to properly handle these types of update/delete conflicts is difficult. It is far easier to avoid these types of conflicts entirely, by simply "marking" deleted rows, and then purging them using procedural replication, as described [*].

Restrictions

You should avoid the following commands in your conflict resolution routines. Use of these commands can result in unpredictable results.

Example Conflict Resolution Routine

The following examples show variations on the standard MAXIMUM and ADDITIVE conflict resolution methods. Unlike the standard methods, these user functions are designed to handle nulls in the columns used to resolve the conflict.

Maximum User Function

-- User function similar to MAXIMUM method.
-- If curr is null or curr < new, use new values.
-- If new is null or new < curr, use current values.
-- If both are null, no resolution.
-- Does not converge with > 2 masters, unless 
-- always increasing.
FUNCTION max_null_loses(old                 IN     NUMBER,
                        new                 IN OUT NUMBER,
                        cur                 IN     NUMBER,
                        ignore_discard_flag OUT    BOOLEAN)
  RETURN BOOLEAN IS
BEGIN
    IF (new IS NULL AND cur IS NULL) OR new = cur THEN
        RETURN FALSE;
    END IF;
    IF new IS NULL THEN
      ignore_discard_flag := TRUE;
    ELSIF cur IS NULL THEN
      ignore_discard_flag := FALSE;
    ELSIF new < cur THEN
      ignore_discard_flag := TRUE;
    ELSE
        ignore_discard_flag := FALSE;
    END IF;
    RETURN TRUE;
END max_null_loses;

Additive User Function

-- User function similar to ADDITIVE method.
-- If old is null, old = 0.
-- If new is null, new = 0.
-- If curr is null, curr = 0.
-- new = curr + (new - old) -> just like ADDITIVE method.
FUNCTION additive_nulls(old                 IN     NUMBER,
                        new                 IN OUT NUMBER,
                        cur                 IN     NUMBER,
                        ignore_discard_flag OUT    BOOLEAN)
  RETURN BOOLEAN IS
  old_val NUMBER := 0.0;
  new_val NUMBER := 0.0;
  cur_val NUMBER := 0.0;
BEGIN
    IF old IS NOT NULL THEN
      old_val := old;
    END IF;
    IF new IS NOT NULL THEN
      new_val := new;
    END IF;
    IF cur IS NOT NULL THEN
      cur_val := cur;
    END IF;
    new := cur_val + (new_val - old_val);
    ignore_discard_flag := FALSE;
    RETURN TRUE;
END additive_nulls;


Contents Index Home Previous Next