Oracle7 Server Distributed Systems Volume II: Replicated Data

Handling Deletes

To avoid encountering delete conflicts, you might find it easiest to mark rows as deleted and purge them later. This section outlines a simple technique for purging these marked rows using procedural replication.

Suppose that your database contains the following MAIL_LIST table:

Name                           Null?    Type
------------------------------ -------- --------------
CUSTNO                         NOT NULL NUMBER(4)      PRIMARY KEY
CUSTNAME                                VARCHAR2(10)   
ADDR1                                   VARCHAR2(30)    
ADDR2                                   VARCHAR2(30)      
CITY                                    VARCHAR2(30)   
STATE                                   VARCHAR2(2)    
ZIP                                     NUMBER(9)    
PHONE                                   NUMBER(10)  
REMOVE_DATE                             DATE

Instead of deleting a customer when he or she requests to be removed from your mailing list, the REMOVE_DATE column would be used to indicate former customers; A NULL value would be used for current customers. After customers request removal from the mailing list, their rows are no longer updated. Such a convention avoids conflicts when the rows are actually deleted sometime later. A view of current customers could be defined as follows:

CREATE OR REPLACE VIEW corp.current_mail_list AS
  SELECT custno, custname, addr1, addr2, city, state, zip, phone 
    FROM corp.mail_list WHERE remove_date IS NULL;

Periodically, perhaps once a year after the holiday sales, the former customers would be purged from the table using the REMOVE_DATE field. Such a delete could be performed using row-level replication just by performing the following delete:

DELETE corp.mail_list WHERE remove_date IS NOT NULL AND 
                      remove_date < '01-JAN-95';

However, for a large company with an extensive mail order business, the number of former customers could be quite large resulting in a lot of undesired network traffic and database overhead. Instead, the procedural replication could be used using the following package:

CREATE OR REPLACE PACKAGE corp.purge AS
  PROCEDURE remove_cust(purge_date IN DATE);
END;
/

CREATE OR REPLACE PACKAGE BODY corp.purge AS
  PROCEDURE remove_cust(purge_date IN DATE) IS
  BEGIN
    -- turn off row-level replication for set delete
    dbms_reputil.replication_off;
    -- prevent phantom reads
    LOCK TABLE corp.mail_list IN EXCLUSIVE MODE;
    DELETE corp.mail_list WHERE remove_date IS NOT NULL AND 
                                remove_date < purge_date;
    dbms_reputil.replication_on;
  EXCEPTION WHEN other THEN
    dbms_reputil.replication_on;
  END;
END;

The DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure would have been used to generate the DEFER_PURGE package during the initial replication setup. Then, the procedural replication package could be called as follows by a single master site:

BEGIN
  defer_purge.remove_cust('01-FEB-95','Y');
END;

The procedure, PURGE.REMOVE_CUST, would be executed locally and asynchronously executed at each master, resulting in many rows being deleted with only minimal network traffic.

To ensure that there are no outstanding transactions against the rows to be purged, your application should be written to never update logically deleted rows and the REMOVE_DATE should be old enough to ensure that the logical delete of the row is propagated before the row is purged. Thus, in the previous example, it is probably not necessary to lock the table in EXCLUSIVE mode; although this is another method of guaranteeing that these rows not be updated during the purge.