1. Oracle GoldenGate Microservices Documentation
  2. Administer
  3. Error Management
  4. Automatic Conflict Detection and Resolution
  5. About Automatic Conflict Detection and Resolution
  6. Track Primary Key Updates in Delete Tombstone

Track Primary Key Updates in Delete Tombstone

Full support of primary key (PK) updates requires handling conflicts on both the rows represented by the before image of the key and the row represented by the after image of the key. A PK update is an autonomous delete and insert, so, the PK update conflicts must be supported as a delete for conflicts with the before image of the key and inserts with the after image of the key (and row).

Supporting the PK update as a delete of the row represented by the before image of the key means that it should insert into the delete tombstone table as a delete. An update internal trigger is added to insert into the tombstone table when the PK is updated (actually the row identifying key, either the PK if it exists or the chosen UK with at least one non-nullable column). As a PK update may lead to two conflicts, up to two resolutions are attempted at the row level, delete of the row with the original PK and the insert of the row with the new PK.

Example: Using latest timestamp resolution

Database 1: Update to tab1 key1 at ts1

Database 2: Update to tab1 key1 set key1 to key2 ts2

Database 3: Update to tab1 key2 ts3

In this scenario, it appears that at the row level tab1 row with key1 should be deleted and the database 3 update should be the final modification of tab1 row key2. If instead the database 2 is at ts3 and database 3 is at ts3, then the PK update at database 2 would be the final modification of tab1 row key2.

Now, consider a case where the database 1 was at ts3, database 2 at ts2 and database 3 at ts1, then the update to tab1 row key1 on database 1 should succeed and the PK update from database 2 on tab1 row key2 should succeed. At this point, it looks like the complete resolution is that both the delete at the before image and the insert at the after image must be resolved separately. This implies that they are not dependent on each other and a loss for one, is not a loss for both.