Conflict detection in an Active-Active environment

****************************************
Conflict detection in an Active-Active environment
****************************************

As Goldengate operates independently of other processes, in an active-active environment. Conflicts can occur when changes are made to the identical data on different databases at the same time. These can occur when the timing of simultaneous changes results in one of the out of sync conditioins:

# A replicated insert attempts to add a row that already exists in the target.
# The before image of a replicated update does not match the current row in the target.
# A replicated delete attempts to remove a row that doesnot exist on the target.

An instance, when UserA on dbA updates a row, and UserB on dbB updates the same row. If userB's transaction occurs before userA's transactions is synchronized to Database B, there will be a confict on the replicated transaction.

In these scenarious, Goldengate doesn't provide default procedures for handling the conflicts and this can be handled with custom conflict resolution scripts that needs to be included in the parameter files.

Here is a small demonstration in an online retail environment:

* An online shopping store is stocked with 20 limited ipods. If the quantity goes to 0, then this item is tagged to be out of stock on the online system.

* Customer A places an order for 15 ipods that came from XYZ database:
sql would be:
"update products set quantity_in_stock = quantity_in_stock -15 where product_name = 'ipod' and quantity_in_stock = 20;"

* Customer B simultaneiously places an order for 4 ipods which came from ABC database.

sql would be:
"update products set quantity_in_stock = quantity_in_stock - 4 where products_name = 'ipod' and quantity_in_stock = 20;"


In the above example, the number of ipods in stock according to the XYZ database is 5 and the number of ipods in stock according to ABC database is 16. When Goldengate attempts to update the ABC database with the transaction from customer A, the transaction will fail because the before image of quantity_in_stock is 20 which does not match the current figure value which is 16. Reversely the transaction replicated to the ABC database will also fail because the before image of quantity_in_stock 20 does not match the current value of 5.

So, unless a conflict resolution script is not written, Goldengate doesnot veirfy target row data before replicating the changes. And this conflict resolution can be handled by writing a sql stored procedure or a query using the SQLEXEC parameter in the replicat parameter file on both the databases.