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.

The below scenarios 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 doesnot match the current row in the target.

# a replicated delete attempts to remove a row that doesnot exist on the target.

An example, when UserA on SiteA updates a row, and UserB on SiteB 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 doesnot provide default procedures for handling the conflicts and this can be handled with custom conflict resulution 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 Site A 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 SiteA database is 5 and the number of ipods in stock according to SiteB database is 16. When Goldengate attempts to update the SiteA database with the transaction from customer A, the transaction will fail because the before image of quantity_in_stock is 20 which doesnot match the current figure value which is 16. Reversely the transaction replicated to the SiteB database will also fail because the before image of quantity_in_stock 20 doesnot 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.

Demo(Extract pump is not used as both the DB's are in the same server):

Site A
_______

e_sitea --> Extract(Change data capture that reads redo from the SiteA database)

EXTRACT E_SITEA
SETENV (ORACLE_SID=SITEA)
GETENV (ORACLE_SID)
USERID orclgg, PASSWORD oracle
EXTTRAIL D:/GG11/dirdat/ea
-- This parameter prevents the transaction looping as the transactions already exists on the target(SiteB).
TRANLOGOPTIONS EXCLUDEUSER ORCLGG
-- This parameter causes Extract to capture the before image of the record and write it into the trail file.
GETUPDATEBEFORES
TABLE SITEA.PRODUCTS;

r_sitea --> Replicat(Replicat to apply data captured from the SiteB database to the SiteA database)

REPLICAT R_SITEB
SETENV (ORACLE_SID=SITEA)
GETENV (ORACLE_SID)
USERID orclgg, PASSWORD oracle
ASSUMETARGETDEFS
ALLOWDUPTARGETMAP
IGNOREUPDATES
MAP SITEA.*, SITEB.*;
GETUPDATES
IGNOREINSERTS
IGNOREDELETES
MAP SITEB.PRODUCTS, TARGET SITEB.PRODUCTS,
SQLEXEC (id conflict,
query "select qty_in_stock from sitea.products where products_number = :vpnum",
params (vpnum = products_number)),
colmap (products_number = products_number,
products_name = products_name,
qty_in_stock = @if(conflict.qty_in_stock <> BEFORE.qty_in_stock,
@compute(conflict.qty_in_stock - (BEFORE.qty_in_stock - qty_in_stock)),
qty_in_stock)
);

Site B
______

e_siteB --> Extract (Change data capture that reads redo from the SiteB database)

EXTRACT E_SITEB
SETENV (ORACLE_SID=SITEB)
GETENV (ORACLE_SID)
USERID orclgg1, PASSWORD oracle
EXTTRAIL D:/GG1112/dirdat/eb
-- This parameter prevents the transaction looping as the transactions already exists on the target(SiteA).
TRANLOGOPTIONS EXCLUDEUSER ORCLGG1
-- This parameter causes Extract to capture the before image of the record and write it into the trail file.
GETUPDATEBEFORES
TABLE SITEB.PRODUCTS;

r_siteA --> Replicat (Replicat to apply data captured from the SiteA database to the SiteB database)

REPLICAT R_SITEA
SETENV (ORACLE_SID=SITEB)
GETENV (ORACLE_SID)
USERID orclgg1, PASSWORD oracle
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rwconf.dsc, purge
ALLOWDUPTARGETMAP
IGNOREUPDATES
MAP SITEB.*, TARGET SITEA.*;
GETUPDATES
IGNOREINSERTS
IGNOREDELETES
MAP SITEA.PRODUCTS, TARGET SITEB.PRODUCTS,
SQLEXEC (id conflict,
query "select qty_in_stock from siteb.products where products_number = :vpnum",
params (vpnum = products_number)),
colmap (products_number = products_number,
products_name = products_name,
qty_in_stock = @if(conflict.qty_in_stock <> BEFORE.qty_in_stock,
@compute(conflict.qty_in_stock - (BEFORE.qty_in_stock - qty_in_stock)),
qty_in_stock)
);


Over here, the replicats on both the sites have been added with "ALLOWDUPTARGETMAP", this is used to perform conflict detection on the update operation. This would turn off processing the update operations and do a wild card mapping of all sitea and siteb tables(only the inserts and deletes will be processed by the wild card mapping).

Next, we turn on processing for updates, and turn off processing for insert and delete operations. This update would be applied using a pl/sql code which is used in by SQLEXEC.

For all update operations performed on the PRODUCTS table, a query is executed against the target table and the data from the qty_in_stock column is returned. The GoldenGate column mapping munction @IF, is used to compare the returned value with the before column image read from the GoldenGate Trail. If the values are not equal, the first statement is executed where the source table value for qty_in_stock is subtracted from the before image qty_in_stock value. The result is then subtracted from the qty_in_stock value returned from the query, and the result is applied to the target table.

Test:

1. Create the below table on both the databases SiteA and SiteB:

create table products (
products_number number(10),
products_name varchar2(10),
qty_in_stock number(5),
primary key (products_number)
using index
);

insert into products values (1,'IPOD', 20);
commit;

2. Get the database to log the keys for the table products using "add trandata" from ggsci prompt. Do this on both the GG instances.

Site A:
ggsci> dblogin userid orclgg, password ggsci> add trandata sourcea.products

Site B:
ggsci> dblogin userid orclgg, password ggsci> add trandata sourceb.products

3. create the Goldengate components on both sides.

Site A:

ggsci> add extract e_sitea, tranlog, begin now
ggsci> add exttrail D:/GG11/dirdat/ea extract e_sitea
ggsci> dblogin userid orclgg, password
ggsci> add replicat r_siteb, exttrail D:/GG1112/dirdat/eb checkpointtable


Site B:

ggsci> add extract e_siteb, tranlog, begin now
ggsci> add exttrail D:/GG1112/dirdat/eb extract e_siteb
ggsci> dblogin userid orclgg1, password
ggsci> add replicat r_sitea, exttrail D:/GG11/dirdat/ea checkpointtable


4. Update the product table on both the databases and commit the transaction.

update sitea.products set qty_in_stock = qty_in_stock-15 where products_name = 'IPOD' and qty_in_stock = 20;

commit;

update siteb.products set qty_in_stock = qty_in_stock-4 where products_name = 'IPOD' and qty_in_stock = 20;

commit;

Now, validate the results from both the databases and see that "qty_in_stock" results with 1 ipod.