Oracle provides the following types of prebuilt update conflict handlers for a Streams environment: OVERWRITE, DISCARD, MAXIMUM, and MINIMUM.
The description for each type of handler later in this section refers to the following conflict scenario:
1. The following update is made at the dbs1.net source database:
UPDATE hr.employees SET salary = 4900 WHERE employee_id = 200;
COMMIT;
This update changes the salary for employee 200 from 4400 to 4900.
2. At nearly the same time, the following update is made at the dbs2.net destination database:
UPDATE hr.employees SET salary = 5000 WHERE employee_id = 200;
COMMIT;
3. A capture process captures the update at the dbs1.net source database and puts the resulting row LCR in a queue.
4. A propagation propagates the row LCR from the queue at dbs1.net to a queue at dbs2.net.
5. An apply process at dbs2.net attempts to apply the row LCR to the hr.employees table but encounters a conflict because the salary value at dbs2.net is 5000, which does not match the old value for the salary in the row LCR (4400).
The following sections describe each prebuilt conflict handler and explain how the handler resolves this conflict.
OVERWRITE When a conflict occurs, the OVERWRITE handler replaces the current value at the destination database with the new value in the LCR from the source database.
If the OVERWRITE handler is used for the hr.employees table at the dbs2.net destination database in the conflict example, then the new value in the row LCR overwrites the value at dbs2.net. Therefore, after the conflict is resolved, the salary for employee 200 is4900.
DISCARD When a conflict occurs, the DISCARD handler ignores the values in the LCR from the source database and retains the value at the destination database.
See Also:
■ "Managing Streams Conflict Detection and Resolution" on page 9-21 for instructions on adding, modifying, and removing an update conflict handler
■ Oracle Database PL/SQL Packages and Types Reference for more information about the SET_UPDATE_CONFLICT_HANDLER procedure
■ "Column Lists" on page 3-9
■ "Resolution Columns" on page 3-10
Conflict Resolution in a Streams Environment
If the DISCARD handler is used for the hr.employees table at the dbs2.net destination database in the conflict example, then the new value in the row LCR is discarded. Therefore, after the conflict is resolved, the salary for employee 200 is5000 at dbs2.net.
MAXIMUM When a conflict occurs, the MAXIMUM conflict handler compares the new value in the LCR from the source database with the current value in the destination database for a designated resolution column. If the new value of the resolution column in the LCR is greater than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the LCR. If the new value of the resolution column in the LCR is less than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the destination database.
If the MAXIMUM handler is used for the salary column in the hr.employees table at the dbs2.net destination database in the conflict example, then the apply process does not apply the row LCR, because the salary in the row LCR is less than the current salary in the table. Therefore, after the conflict is resolved, the salary for employee 200 is5000 at dbs2.net.
If you want to resolve conflicts based on the time of the transactions involved, then one way to do this is to add a column to a shared table that automatically records the transaction time with a trigger. You can designate this column as a resolution column for a MAXIMUM conflict handler, and the transaction with the latest (or greater) time would be used automatically.
The following is an example of a trigger that records the time of a transaction for the hr.employees table. Assume that the job_id, salary, and commission_pct columns are part of the column list for the conflict resolution handler. The trigger should fire only when an UPDATE is performed on the columns in the column list or when an INSERT is performed.
CONNECT hr/hr
ALTER TABLE hr.employees ADD (time TIMESTAMP WITH TIME ZONE);
CREATE OR REPLACE TRIGGER hr.insert_time_employees BEFORE
INSERT OR UPDATE OF job_id, salary, commission_pct ON hr.employees FOR EACH ROW
BEGIN
-- Consider time synchronization problems. The previous update to this -- row might have originated from a site with a clock time ahead of the -- local clock time.
IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN :NEW.TIME := SYSTIMESTAMP;
If you use such a trigger for conflict resolution, then make sure the trigger's firing property is fire once, which is the default. Otherwise, a new time might be marked when transactions are applied by an apply process, resulting in the loss of the actual time of the transaction.
See Also: "Trigger Firing Property" on page 1-30
Conflict Resolution in a Streams Environment
MINIMUM When a conflict occurs, the MINIMUM conflict handler compares the new value in the LCR from the source database with the current value in the destination database for a designated resolution column. If the new value of the resolution column in the LCR is less than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the LCR. If the new value of the resolution column in the LCR is greater than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the destination database.
If the MINIMUM handler is used for the salary column in the hr.employees table at the dbs2.net destination database in the conflict example, then the apply process resolves the conflict in favor of the row LCR, because the salary in the row LCR is less than the current salary in the table. Therefore, after the conflict is resolved, the salary for employee 200 is4900.
Column Lists
Each time you specify a prebuilt update conflict handler for a table, you must specify a column list. A column list is a list of columns for which the update conflict handler is called. If an update conflict occurs for one or more of the columns in the list when an apply process tries to apply a row LCR, then the update conflict handler is called to resolve the conflict. The update conflict handler is not called if a conflict occurs only in columns that are not in the list. The scope of conflict resolution is a single column list on a single row LCR.
You can specify more than one update conflict handler for a particular table, but the same column cannot be in more than one column list. For example, suppose you specify two prebuilt update conflict handlers on hr.employees table:
■ The first update conflict handler has the following columns in its column list:
salary and commission_pct.
■ The second update conflict handler has the following columns in its column list:
job_id and department_id.
Also, assume that no other conflict handlers exist for this table. In this case, if a conflict occurs for the salary column when an apply process tries to apply a row LCR, then the first update conflict handler is called to resolve the conflict. If, however, a conflict occurs for the department_id column, then the second update conflict handler is called to resolve the conflict. If a conflict occurs for a column that is not in a column list for any conflict handler, then no conflict handler is called, and an error results. In this example, if a conflict occurs for the manager_id column in the hr.employees table, then an error results. If conflicts occur in more than one column list when a row LCR is being applied, and there are no conflicts in any columns that are not in a column list, then the appropriate update conflict handler is invoked for each column list with a conflict.
Column lists enable you to use different handlers to resolve conflicts for different types of data. For example, numeric data is often suited for a maximum or minimum conflict handler, while an overwrite or discard conflict handler might be preferred for character data.
If a conflict occurs in a column that is not in a column list, then the error handler for the specific operation on the table attempts to resolve the conflict. If the error handler cannot resolve the conflict, or if there is no such error handler, then the transaction that caused the conflict is moved to the error queue.
Also, if a conflict occurs for a column in a column list that uses either the OVERWRITE, MAXIMUM, or MINIMUM prebuilt handler, and the row LCR does not contain all of the columns in this column list, then the conflict cannot be resolved because all of the
Conflict Resolution in a Streams Environment
values are not available. In this case, the transaction that caused the conflict is moved to the error queue. If the column list uses the DISCARD prebuilt method, then the row LCR is discarded and no error results, even if the row LCR does not contain all of the columns in this column list.
A conditional supplemental log group must be specified for the columns specified in a column list if more than one column at the source database affects the column list at the destination database. Supplemental logging is specified at the source database and adds additional information to the LCR, which is needed to resolve conflicts properly.
Typically, a conditional supplemental log group must be specified for the columns in a column list if there is more than one column in the column list, but not if there is only one column in the column list.
However, in some cases, a conditional supplemental log group is required even if there is only one column in a column list. That is, an apply handler or custom rule-based transformation can combine multiple columns from the source database into a single column in the column list at the destination database. For example, a custom rule-based transformation can take three columns that store street, state, and postal code data from a source database and combine the data into a single address column at a destination database.
Also, in some cases, no conditional supplemental log group is required even if there is more than one column in a column list. For example, an apply handler or custom rule-based transformation can separate one address column from the source database into multiple columns that are in a column list at the destination database. A custom rule-based transformation can take an address that includes street, state, and postal code data in one address column at a source database and separate the data into three columns at a destination database.