GoldenGate simple conflict resolution example

I found it incredibly difficult to find a simple working example of an inventory type system where a user at SITE A updates the inventory for ‘Bowl’, while at SITE B another user updates the inventory at the exact same time. Normally, without conflict detection and resolution, each update would be replicated to the other database, at which point you would have out of sync data. In other words, update SITE A to 20, and SITE B to 30. After each change is replicated, you have inventory of 30 at SITE A, and 20 at SITE B.

Assume we start with what is below at each site…

create table inventory(id number primary key, quantity number);
begin
  for i in 1..10 loop
    insert into inventory values(i, i * 10);
    commit;
  end loop;
end;
/

I found it easy to detect this conflict with what is below in the replicat parameter file at each site, changing the schema names. In other words, flip the CMH and ATL in the SITE B replicat file from what it is at SITE A (we have two different schemas in the same database for testing)

map CMH.INVENTORY, target ATL.INVENTORY, &
sqlexec (id INVENTORY_check, on update, beforefilter, &
query "select QUANTITY from ATL.INVENTORY where ID = :p1", params (p1 = ID)), &
filter (on update, before.QUANTITY = INVENTORY_check.QUANTITY, raiseerror 9999);

If a row with the expected “before” update quantity was not found at the replicat site, the replicat process would abend, which is good (for now).

You would see something like what is below

2012-04-03 21:33:22  WARNING OGG-01432  Oracle GoldenGate Delivery for Oracle, atlrep.prm:  Aborted grouped transaction on 'ATL.INVENTORY', Filter not passed.
2012-04-03 21:33:22  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, atlrep.prm:  Repositioning to rba 10304 in seqno 6.
2012-04-03 21:33:22  WARNING OGG-01155  Oracle GoldenGate Delivery for Oracle, atlrep.prm:  Filter not passed: user error 20000 mapping CMH.INVENTORY to ATL.INVENTORY.
2012-04-03 21:33:22  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, atlrep.prm:  Repositioning to rba 10304 in seqno 6.
2012-04-03 21:33:22  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, atlrep.prm:  Error mapping from CMH.INVENTORY to ATL.INVENTORY.
2012-04-03 21:33:22  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, atlrep.prm:  PROCESS ABENDING

However, all I ever found was how to calculate what the quantity should be (the actual math) if it were to be handled correctly by a conflict resolution process. I could never find the actual syntax to actually set up the conflict resolution rule!!

As a result, I decided to use a COLMAP, which both detects the conflict, and applies the fix if needed. This is shown below. Please note that if you use this, you don’t need the MAP entry I have above for conflict detection. This replaces it.

map CMH.INVENTORY, target ATL.INVENTORY, &
sqlexec (id INVENTORY_check, on update, &
query "select quantity from ATL.inventory where ID = :p1", params (p1 = ID)), &
COLMAP (usedefaults, &
        quantity = @if (before.quantity <> inventory_check.quantity, &
             @compute(quantity - (before.quantity - inventory_check.quantity)), &
             quantity)
       );

All this does is apply the values found in the replicat trail file for all columns with the exception of quantity. For this column, it determines if the pre-update version in the target replicat database is the same as the pre-update value was in the source extract database. If it is, it just replicates the inventory value provided in the trail file. If it doesn’t match (a conflicting update occurred at the target between the update at the source and the time the target tried to process it from the replicat), it dynamically calculates what total inventory should be based on the fact the net of the two transactions should increment or decrement inventory by the combined amounts. This replaces what is found in the replicat trail file. As noted, this both detects the conflict and handles it.

Our next post will show how to handle something like this using a stored procedure.

I hope this helps someone!

EDIT:

Technically, you don’t even have to check if the values match. You could run the update and just assume the values don’t match, as the update statement will do the correct thing, anyway.

You can use what is below:

map CMH.INVENTORY, target ATL.INVENTORY, &
sqlexec (id INVENTORY_check, on update, &
query "select quantity from ATL.inventory where ID = :p1", params (p1 = ID)), &
COLMAP (usedefaults, &
        quantity = @compute(quantity - (before.quantity - inventory_check.quantity))
       );