Latest Timestamp Conflict Detection and Resolution

When you run the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package to configure a table for automatic Oracle GoldenGate conflict detection and resolution, a hidden timestamp column is added to the table. This hidden timestamp column records the time of a row change, and this information is used to detect and resolve conflicts.

When a row LCR is applied, a conflict can occur for an INSERT, UPDATE, or DELETE operation. The following table describes each type of conflict and how it is resolved.

Operation Conflict Detection Conflict Resolution

Operation	Conflict Detection	Conflict Resolution
`INSERT`	A conflict is detected when the table has the same value for a key column as the new value in the row LCR.	If the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table. If the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.
`UPDATE`	A conflict is detected in each of the following cases: There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table. There is a mismatch between an old value in a column group in the row LCR does not match the column value in the corresponding table row. A column group is a logical grouping of one or more columns in a replicated table. The table row does not exist. If the row is in the tombstone table, then this is referred to as an update-delete conflict.	If there is a value mismatch and the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table. If there is a value mismatch and the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained. If the table row does not exist and the timestamp of the row LCR is later than the timestamp in the tombstone table row, then the row LCR is converted from an `UPDATE` operation to an `INSERT` operation and inserted into the table. If the table row does not exist and the timestamp of the row LCR is earlier than the timestamp in the tombstone table row, then the row LCR is discarded. If the table row does not exist and there is no corresponding row in the tombstone table, then the row LCR is converted from an `UPDATE` operation to an `INSERT` operation and inserted into the table.
`DELETE`	A conflict is detected in each of the following cases: There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table. The table row does not exist.	If the timestamp of the row LCR is later than the timestamp in the table, then delete the row from the table. If the timestamp of the row LCR is earlier than the timestamp in the table, then the row LCR is discarded, and the table values are retained. If the delete is successful, then log the row LCR by inserting it into the tombstone table. If the table row does not exist, then log the row LCR by inserting it into the tombstone table.

INSERT

A conflict is detected when the table has the same value for a key column as the new value in the row LCR.

If the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table.

If the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.

UPDATE

A conflict is detected in each of the following cases:

There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table.
There is a mismatch between an old value in a column group in the row LCR does not match the column value in the corresponding table row. A column group is a logical grouping of one or more columns in a replicated table.
The table row does not exist. If the row is in the tombstone table, then this is referred to as an update-delete conflict.

If there is a value mismatch and the timestamp of the row LCR is later than the timestamp in the table row, then the values in the row LCR replace the values in the table.

If there is a value mismatch and the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.

If the table row does not exist and the timestamp of the row LCR is later than the timestamp in the tombstone table row, then the row LCR is converted from an UPDATE operation to an INSERT operation and inserted into the table.

If the table row does not exist and the timestamp of the row LCR is earlier than the timestamp in the tombstone table row, then the row LCR is discarded.

If the table row does not exist and there is no corresponding row in the tombstone table, then the row LCR is converted from an UPDATE operation to an INSERT operation and inserted into the table.

DELETE

A conflict is detected in each of the following cases:

There is a mismatch between the timestamp value in the row LCR and the timestamp value of the corresponding row in the table.
The table row does not exist.

If the timestamp of the row LCR is later than the timestamp in the table, then delete the row from the table.

If the timestamp of the row LCR is earlier than the timestamp in the table, then the row LCR is discarded, and the table values are retained.

If the delete is successful, then log the row LCR by inserting it into the tombstone table.

If the table row does not exist, then log the row LCR by inserting it into the tombstone table.

The following image displays the conflict resolution between database A and database B:
Latest timestamp conflict resolution using latest timestamp method.

This example shows a row being replicated at database A and database B. The database columns are Name, RowTS, Office, Title, and Salary. The RowTS column is the invisible column in both databases. There is an update in the Office column in database A and at the same time there is a update in the Title column in database B. This causes a conflict and the resolution for this conflict is done applying the latest timestamp method.

In database A, the value in the Office column gets updated from 1080 to 1103 and the RowTS value changes from @TS10 to @TS20. A arrow indicates that this change is replicated to database B.
In database B, the value of the Title column changes from MTS1 to MTS2 and the RowTS value changes from @TS10 to @TS22.
To resolve this conflict, the latest timestamp which exists in database B wins. This implies that the changes in database A are not applied. The final values applied to database A and database B are Scott, @TS22, 1080, MTS2, 100.