An update conflict occurs when an inbound server applies a row LCR containing an update to a row that conflicts with another update to the same row.

Update conflicts can happen when two transactions originating from different databases update the same row at nearly the same time.

A uniqueness conflict occurs when an inbound server applies a row LCR containing a change to a row that violates a uniqueness integrity constraint, such as a PRIMARY KEY or UNIQUE constraint.

For example, consider what happens when two transactions originate from two different databases, each inserting a row into a table with the same primary key value. In this case, the transactions cause a uniqueness conflict.

A delete conflict occurs when two transactions originate at different databases, with one transaction deleting a row and another transaction updating or deleting the same row.

In this case, the row referenced in the row LCR does not exist and therefore cannot be updated or deleted.

A foreign key conflict occurs when an inbound server applies a row LCR containing a change to a row that violates a foreign key constraint.

For example, in the hr schema, the department_id column in the employees table is a foreign key of the department_id column in the departments table. Consider what can happen when the following changes originate at two different databases (A and B) and are propagated to a third database (C):

• At database A, a row is inserted into the departments table with a department_id of 271. This change is propagated to database B and applied there.

• At database B, a row is inserted into the employees table with an employee_id of 206 and a department_id of 271.

If the change that originated at database B is applied at database C before the change that originated at database A, then a foreign key conflict results because the row for the department with a department_id of 271 does not yet exist in the departments table at database C.

By default, an inbound server compares old values for all columns during conflict detection, but you can stop conflict detection for non-key columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package.

Conflict detection might not be needed for some non-key columns.

To detect conflicts accurately, Oracle Database must be able to identify and match corresponding rows at different databases uniquely.

By default, Oracle Database uses the primary key of a table to identify rows in a table uniquely. When a table does not have a primary key, it is best practice to designate a substitute key. A substitute key is a column or set of columns that Oracle Database can use to identify uniquely rows in the table.

You can avoid the possibility of conflicts by limiting the number of databases that have simultaneous update access to the tables containing shared data.

Primary ownership prevents all conflicts, because only a single database permits updates to a set of shared data. Applications can even use row and column subsetting to establish more granular ownership of data than at the table level. For example, applications might have update access to specific columns or rows in a shared table on a database-by-database basis.

If a primary database ownership model is too restrictive for your application requirements, then you can use a shared ownership data model, which means that conflicts might be possible. Even so, typically you can use some simple strategies to avoid specific types of conflicts.

• Avoid Uniqueness Conflicts in an XStream Environment
  You can avoid uniqueness conflicts by ensuring that each database uses unique identifiers for shared data.
• Avoid Delete Conflicts in an Oracle Replication Environment
  Always avoid delete conflicts in shared data environments.
• Avoid Update Conflicts in an XStream Environment
  After trying to eliminate the possibility of uniqueness and delete conflicts, you should also try to limit the number of possible update conflicts.

There are several types of prebuilt DML conflict handlers available. Column lists and resolution columns are used in prebuilt DML conflict handlers.

A column list is a list of columns for which the DML conflict handler is called when there is an insert or update conflict. The resolution column identifies a DML conflict handler. If you use a MAXIMUM or MINIMUM prebuilt DML conflict handler, then the resolution column is also the column used to resolve the conflict. The resolution column must be one of the columns in the column list for the handler.

Use the SET_DML_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package to specify one or more DML conflict handlers for a particular table. There are no prebuilt DML conflict handlers for delete or foreign key conflicts.

Oracle provides the following types of prebuilt DML conflict handlers for an Oracle Replication environment: RECORD, IGNORE, OVERWRITE, MAXIMUM, MINIMUM, and DELTA.

The description for each type of handler later in this topic refers to the following conflict scenario:

1. The following update is made at the dbs1.example.com 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.example.com destination database:

   UPDATE hr.employees SET salary = 5000 WHERE employee_id = 200;
   COMMIT;
   

3. A capture process captures the update at the dbs1.example.com source database and puts the resulting row LCR in a queue.

4. A propagation propagates the row LCR from the queue at dbs1.example.com to a queue at dbs2.example.com.

5. An apply process at dbs2.example.com attempts to apply the row LCR to the hr.employees table but encounters a conflict because the salary value at dbs2.example.com 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.

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;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

Each time you specify a prebuilt DML conflict handler for a table, you must specify a column list.

A column list is a list of columns for which the DML conflict handler is called. If an update conflict occurs for one or more of the columns in the list when an inbound server tries to apply a row LCR, then the DML conflict handler is called to resolve the conflict. The DML 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 multiple DML conflict handlers for a particular table, but the same column cannot be in more than one column list. For example, suppose you specify two prebuilt DML conflict handlers on hr.employees table:

• The first DML conflict handler has the following columns in its column list: salary and commission_pct.

• The second DML 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, the following examples illustrate the outcomes for different scenarios:

• If a conflict occurs for the salary column when an inbound server tries to apply a row LCR, then the first DML conflict handler is called to resolve the conflict.

• If a conflict occurs for the department_id column, then the second DML 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. For instance, 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 DML 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 if the row LCR does not contain all of the columns in this column list, then the conflict cannot be resolved because all of the 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.

If more than one column at the source database affects the column list at the destination database, then a conditional supplemental log group must be specified for the columns specified in a column list. 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 are multiple columns 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 are multiple columns 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.

The resolution column is the column used to identify a prebuilt DML conflict handler.

If you use a MAXIMUM or MINIMUM prebuilt DML conflict handler, then the resolution column is also the column used to resolve the conflict. The resolution column must be one of the columns in the column list for the handler.

For example, if the salary column in the hr.employees table is specified as the resolution column for a maximum or minimum conflict handler, then the salary column is evaluated to determine whether column list values in the row LCR are applied or the destination database values for the column list are retained.

In either of the following situations involving a resolution column for a conflict, the apply process moves the transaction containing the row LCR that caused the conflict to the error queue, if the error handler cannot resolve the problem. In these cases, the conflict cannot be resolved and the values of the columns at the destination database remain unchanged:

• The new LCR value and the destination row value for the resolution column are the same (for example, if the resolution column was not the column causing the conflict).

• Either the new LCR value of the resolution column or the current value of the resolution column at the destination database is NULL.

When you share data between multiple databases, and you want the data to be the same at all of these databases, ensure that you use conflict resolution handlers that cause the data to converge at all databases.

If you allow changes to shared data at all of your databases, then data convergence for a table is possible only if all databases that are sharing data capture changes to the shared data and propagate these changes to all of the other databases that are sharing the data.

In such an environment, the MAXIMUM conflict resolution method can guarantee convergence only if the values in the resolution column are always increasing. If successive time stamps on a row are distinct, then a time-based resolution column meets this requirement. The MINIMUM conflict resolution method can guarantee convergence in such an environment only if the values in the resolution column are always decreasing.

You can create a PL/SQL procedure to use as a custom conflict handler.

You use the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package to designate one or more custom conflict handlers for a particular table. Specifically, set the following parameters when you run this procedure to specify a custom conflict handler:

• Set the object_name parameter to the fully qualified name of the table for which you want to perform conflict resolution.

• Set the object_type parameter to TABLE.

• Set the operation_name parameter to the type of operation for which the custom conflict handler is called. The possible operations are the following: INSERT, UPDATE, DELETE, and LOB_UPDATE. You can also set the operation_name parameter to DEFAULT so that the handler is the default handler for all operations.

• If you want an error handler to perform conflict resolution when an error is raised, then set the error_handler parameter to TRUE. Or, if you want to include conflict resolution in your procedure DML handler, then set the error_handler parameter to FALSE.

  If you specify FALSE for this parameter, then, when you execute a row LCR using the EXECUTE member procedure for the LCR, the conflict resolution within the procedure DML handler is performed for the specified object and operation(s).

• Specify the procedure to resolve a conflict by setting the user_procedure parameter. This user procedure is called to resolve any conflicts on the specified table resulting from the specified type of operation.

If the custom conflict handler cannot resolve the conflict, then the inbound server moves the transaction containing the conflict to the error queue and does not apply the transaction.

If both a prebuilt DML conflict handler and a custom conflict handler exist for a particular object, then the prebuilt DML conflict handler is invoked only if both of the following conditions are met:

• The custom conflict handler executes the row LCR using the EXECUTE member procedure for the LCR.

• The conflict_resolution parameter in the EXECUTE member procedure for the row LCR is set to TRUE.