Oracle7 Server Distributed Systems Volume II: Replicated Data

Examples

This section provides examples that you can modify and/or expand with your own conflict resolution routines.

Suppose that you allowed multiple sites to update your CUSTOMER table, and want to set up conflict resolution as follows:

the ADDITIVE method to resolve update conflicts on the AMOUNT column, which indicates the total merchandise ordered by the customer

the LATEST TIMESTAMP method on the remaining columns

SITE PRIORITY as a backup method, because two people could theoretically update the same field at precisely the same time

This example

uses conflict notification combined with the APPEND SEQUENCE system-defined routine to address uniqueness conflicts

does not resolve delete conflicts

assumes that your CUSTOMER table is as follows:

			CREATE TABLE customers  
			(
  			custno     NUMBER(4) PRIMARY KEY,
  			last_name  VARCHAR2(10),
  			first_name VARCHAR2(10),
  			addr1      VARCHAR2(30),
  			addr2      VARCHAR2(30),
 			 amount     NUMBER(7,2),
  			timestamp  DATE,
 			 site       VARCHAR2(128),
  			CONSTRAINT c_cust_name UNIQUE (last_name, first_name) 
		)

Conflict Notification Log

This example

shows how user-defined conflict resolution routines can be used for conflict notification.

uses a database table to record conflict notifications. This table will be registered as a replicated object so that it will be defined automatically at the other masters. However, this table will NOT have replication support generated for it because its entries are specific to the site that detects a conflict.

CREATE TABLE conf_report 
(
  line          NUMBER(2),     --- used to order message text
  txt           VARCHAR2(80),  --- conflict notification message
  timestamp     DATE,          --- time of conflict
  table_name    VARCHAR2(30),  --- table in which the conflict occurred
  table_owner   VARCHAR2(30),  --- owner of the table
  conflict_type VARCHAR2(6)    --- INSERT, DELETE or UNIQUE
)

Sample Conflict Notification Package and Package Body

The following package and package body perform a simple form of conflict notification by logging UNIQUENESS conflicts for the CUSTOMERS table into the CONF_REPORT table. With simple modifications, the user-defined conflict resolution routine can take more active steps. For example, instead of just recording the notification message, the package DBMS_OFFICE can be used to send an Oracle Office e-mail message to the DBA.

Note: This example of conflict notification does not resolve any conflicts. You should either provide a method to resolve conflicts (for example, discard or overwrite), or provide a notification mechanism that will succeed (for example, using e-mail) even if the error is not resolved and the transaction is rolled back.

CREATE OR REPLACE PACKAGE notify AS

  --- Report uniqueness constraint violations on customer table
  FUNCTION customer_unique_violation(first_name         IN OUT VARCHAR2,
                                     last_name          IN OUT VARCHAR2,
                                     discard_new_values IN OUT BOOLEAN) 
    RETURN BOOLEAN;
END notify;
/

CREATE OR REPLACE PACKAGE BODY notify AS

  --- Define a PL/SQL table to hold the notification message
  TYPE message_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;


  PROCEDURE report_conflict(conflict_report IN MESSAGE_TABLE,
                            report_length   IN NUMBER,
                            conflict_time   IN DATE,
                            conflict_table  IN VARCHAR2,
                            table_owner     IN VARCHAR2,
                            conflict_type   IN VARCHAR2) IS

  BEGIN
    FOR idx IN 1..report_length LOOP
      BEGIN
        INSERT INTO off_shore_accounts.conf_report 
        (line, txt, timestamp, table_name, table_owner, conflict_type)
         VALUES (idx, SUBSTR(conflict_report(idx),1,80), conflict_time,
                  conflict_table, table_owner, conflict_type);
      EXCEPTION WHEN others THEN NULL;
      END;
    END LOOP;
  END report_conflict;

  --- This is the conflict resolution routine that will be called first
  --- when a uniqueness constraint violated is detected in the customer
  --- table.
  FUNCTION customer_unique_violation(first_name         IN OUT VARCHAR2,
                                     last_name          IN OUT VARCHAR2,
                                     discard_new_values IN OUT BOOLEAN) 
     RETURN BOOLEAN IS
    local_node  VARCHAR2(128);
    conf_report MESSAGE_TABLE;
    conf_time   DATE := SYSDATE;
  BEGIN
    --- Get the global name of the local site
    BEGIN
      SELECT global_name INTO local_node FROM global_name;
    EXCEPTION WHEN others THEN local_node := '?';
    END;

    --- Generate a message for the DBA
    conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE CUSTOMER ON ' ||
                      TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
    conf_report(2) := '  AT NODE ' || local_node;
    conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING' ||
                      ' APPEND SEQUENCE METHOD';
    conf_report(4) := 'FIRST NAME: ' || first_name;
    conf_report(5) := 'LAST NAME:  ' || last_name;
    conf_report(6) := NULL;

    --- Report the conflict
    report_conflict(conf_report, 5, conf_time, 'CUSTOMER',  
                    'OFF_SHORE_ACCOUNTS', 'UNIQUE');
    
    --- Do not discard the new column values. They are still needed by
    --- other conflict resolution routines
    discard_new_values := FALSE;

    --- Indicate that the conflict was not resolved.
    RETURN FALSE;
  END customer_unique_violation;
END notify;
/

Creating the Object Group and Replicated Objects

After defining the tables and the NOTIFY package and package body, you can now create the object group and replicated objects. The CUSTOMERS table, CONF_REPORT tables, and the NOTIFY package and package body are defined as replicated objects in the same object group. You can also use the Symmetric Replication facility to distribute these objects to all master sites, but you must generate replication support for the CUSTOMER table.

  --- Create the replicated object group
  dbms_repcat.create_master_repgroup(gname          => 'ORDER_ENTRY',
                                     group_comment  => 'Foreign Orders',
                                     master_comment => 'Main Office: NY');

  --- Register customer as a replicated object.
  dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
                                      sname => 'OFF_SHORE_ACCOUNTS',
                                      oname => 'CUSTOMERS',
                                      type  => 'TABLE');

  --- Replication only the DDL for the notification table. Do NOT generate 
  --- replication support on this table
  dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
                                      sname => 'OFF_SHORE_ACCOUNTS',
                                      oname => 'CONF_REPORT',
                                      type  => 'TABLE');

  --- Register Notify package and body as a replicated object.
  dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
                                      sname => 'OFF_SHORE_ACCOUNTS',
                                      oname => 'NOTIFY',
                                      type  => 'PACKAGE');
  dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
                                      sname => 'OFF_SHORE_ACCOUNTS',
                                      oname => 'NOTIFY',
                                      type  => 'PACKAGE BODY');

Declaring Conflict Resolution Methods for Update Conflicts

After declaring the replicated objects, you can then begin declaring the conflict resolution methods. Here, we define two object groups: one for the AMOUNT column and one for the other non-primary key columns. The primary key in this example would be generated by a sequence that has its numbers partitioned among the sites so that this column should avoid being involved in conflicts.

The column group for the AMOUNT column is assigned the ADDITIVE method. The other column group is assigned the TIMESTAMP method with SITE PRIORITY as a back-up method. This example defines the priority of two sites.

  --- Indicate the columns to resolve if a conflict is detected
  --- Primary key is not in a user-defined column group

  dbms_repcat.make_column_group(
                       sname                => 'OFF_SHORE_ACCOUNTS',
                       oname                => 'CUSTOMERS',
                       column_group         => 'CG_CUSTOMERS',
                       list_of_column_names => 'LAST_NAME,FIRST_NAME,' ||
                                               'ADDR1,ADDR2,' ||
                                               'TIMESTAMP,SITE');

  dbms_repcat.make_column_group(
                        sname                => 'OFF_SHORE_ACCOUNTS',
                        oname                => 'CUSTOMERS',
                        column_group         => 'CG_CUST_AMT',
                        list_of_column_names => 'AMOUNT');

  --- make priority group for site priority

  dbms_repcat.define_site_priority(
                         gname           => 'ORDER_ENTRY',
                         name            => 'SITE PRIORITY', 
                         comment         => 'site priority for customers');

  --- add values to site priority group

  dbms_repcat.add_site_priority_site(
                 gname    => 'ORDER_ENTRY',
                 name     => 'SITE PRIORITY',
                 site     => 'DBS1.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
                 priority => 200);

  dbms_repcat.add_site_priority_site(
                 gname    => 'ORDER_ENTRY',
                 name     => 'SITE PRIORITY',
                 site     => 'DBS2.REGRESS.RDBMS.DEV.US.ORACLE.COM',
                 priority => 100);

  --- define update resolution for Amount column
  dbms_repcat.add_update_resolution(
                   sname                 => 'OFF_SHORE_ACCOUNTS',
                   oname                 => 'CUSTOMERS',
                   column_group          => 'CG_CUST_AMT',
                   sequence_no           => 1,
                   method                => 'ADDITIVE',
                   parameter_column_name => 'AMOUNT');

  --- define timestamp with site-priority backup for other column group
  dbms_repcat.add_update_resolution(
                   sname                 => 'OFF_SHORE_ACCOUNTS',
                   oname                 => 'CUSTOMERS',
                   column_group          => 'CG_CUSTOMERS',
                   sequence_no           => 1,
                   method                => 'LATEST TIMESTAMP',
                   parameter_column_name => 'TIMESTAMP');

  dbms_repcat.add_update_resolution(
                   sname                 => 'OFF_SHORE_ACCOUNTS',
                   oname                 => 'CUSTOMERS',
                   column_group          => 'CG_CUSTOMERS',
                   sequence_no           => 2,
                   method                => 'SITE PRIORITY',
                   parameter_column_name => 'SITE',
                   priority_group        => 'SITE PRIORITY');

Declaring Conflict Resolution Methods for Unique Conflicts

You can also declare methods for handling uniqueness conflicts for the C_CUST_NAME constraint:

1. Register a user-defined procedure that will perform the conflict notification.

2. Register the APPEND SEQUENCE system-defined method. Because the APPEND SEQUENCE method never converges, you will always want notification to occur. Therefore, the SEQUENCE_NO for the notification method must be smaller than sequence_no for APPEND SEQUENCE method. The notification method only wants the columns associated for the first and last names.

  --- register a user-defined resolution routine for notification

dbms_repcat.add_unique_resolution(
  sname                 => 'OFF_SHORE_ACCOUNTS',
  oname                 => 'CUSTOMERS',
  constraint_name       => 'C_CUST_NAME',
  sequence_no           => 1,
  method                => 'USER FUNCTION',
  comment               => 'Notify DBA',
  parameter_column_name => 'FIRST_NAME,LAST_NAME',
  function_name         =>'OFF_SHORE_ACCOUNTS.NOTIFY.CUSTOMER_UNIQUE_VIOLATION');

  --- register a system-defined resolution routine for non-convergent
  --- resolution of the uniqueness conflict. 

  dbms_repcat.add_unique_resolution(
              sname                 => 'OFF_SHORE_ACCOUNTS',
              oname                 => 'CUSTOMERS',
              constraint_name       => 'C_CUST_NAME',
              sequence_no           => 2,
              method                => 'APPEND SEQUENCE',
              comment               => 'Resolve Conflict',
              parameter_column_name => 'LAST_NAME');

Sample Timestamp and Site Maintenance Trigger

In either a trigger or in your application, you must implement the logic necessary to maintain the timestamp and site information. The following example trigger considers clock synchronization problems, but needs to be modified if the application crosses time zones. This trigger is specific to the CG_CUSTOMERS column group.

Because the trigger uses one of the generated procedures to check whether or not the trigger should actually be fired, it is necessary to generate replication support for the CUSTOMERS table before creating the trigger. This will also allow transactions on the customer table to be propagated.

  dbms_repcat.generate_replication_support(sname => 'OFF_SHORE_ACCOUNTS',
                                           oname => 'CUSTOMERS',
                                           type  => 'TABLE');

Now you can define the trigger:

  create or replace trigger "OFF_SHORE_ACCOUNTS"."T_CUSTOMERS"
    before insert or update on "OFF_SHORE_ACCOUNTS"."CUSTOMERS"
    for each row
  declare
    TIMESTAMP$X DATE := SYSDATE;
    SITE$X VARCHAR2(128) := dbms_reputil.global_name;
  begin
    -- Don't fire if a snapshot refreshing;
    -- Don't fire if a master and replication is turned off
    if ("OFF_SHORE_ACCOUNTS"."CUSTOMERS$TP".active) then
      if not dbms_reputil.from_remote then
        if inserting then
          -- set site and timestamp columns.
          :new."TIMESTAMP" := TIMESTAMP$X;
          :new."SITE" := SITE$X;
        elsif updating then
          if (:old."ADDR1" = :new."ADDR1" or
             (:old."ADDR1" is null and :new."ADDR1" is null)) and
             (:old."ADDR2" = :new."ADDR2" or
             (:old."ADDR2" is null and :new."ADDR2" is null)) and
             (:old."FIRST_NAME" = :new."FIRST_NAME" or
             (:old."FIRST_NAME" is null and :new."FIRST_NAME" is null)) and
             (:old."LAST_NAME" = :new."LAST_NAME" or
             (:old."LAST_NAME" is null and :new."LAST_NAME" is null)) and
             (:old."SITE" = :new."SITE" or
             (:old."SITE" is null and :new."SITE" is null)) and
             (:old."TIMESTAMP" = :new."TIMESTAMP" or
             (:old."TIMESTAMP" is null and :new."TIMESTAMP" is null)) then
            -- column group was not changed; do nothing
            NULL;
          else
            -- column group was changed; set site and timestamp columns.
            :new."SITE" := SITE$X;
            :new."TIMESTAMP" := TIMESTAMP$X;
            -- consider time synchronization problems;
            -- previous update to this row may have originated from a site
		 -- with a clock time ahead of the local clock time.
            if :old."TIMESTAMP" is not null and
              :old."TIMESTAMP" > :new."TIMESTAMP" then
              :new."TIMESTAMP" := :old."TIMESTAMP" + 1 / 86400;
            elsif :old."TIMESTAMP" is not null and
              :old."TIMESTAMP" = :new."TIMESTAMP" and
              (:old."SITE" is null OR :old."SITE" != :new."SITE") then
              :new."TIMESTAMP" := :old."TIMESTAMP" + 1 / 86400;
            end if;
          end if;
        end if;
      end if;
    end if;
  end;

You can propagate the trigger to other masters by registering it with the symmetric replication facility:

dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY', sname => 'OFF_SHORE_ACCOUNTS', oname => 'T_CUSTOMERS', type => 'TRIGGER');

After confirming that all replication objects are correctly generated and replicated, start up replication activity:

dbms_repcat.resume_master_activity(gname => 'ORDER_ENTRY');