In GoldenGate 11.2.1.0, they introduced out of the box CDR (conflict detection and resolution). This is really useful in a bi-directional replication environment such as the one we are building.<\/p>\n
One question I had about this surrounded what happened if you handled the conflict on only one statement of a multi statement transaction. In other words, let’s say we configured CDR to ignore a missing delete on the target. Would the other statements in the same transaction still be executed?<\/p>\n
The test below shows that the other statements in the transaction are successfully processed.<\/p>\n
We start by showing our tables…<\/p>\n
\r\nSQL> conn atl\/atl\r\nConnected.\r\nSQL> drop table t1 purge;\r\n\r\nTable dropped.\r\n\r\nSQL> drop table t2 purge;\r\n\r\nTable dropped.\r\n\r\nSQL> create table t1(c int primary key);\r\n\r\nTable created.\r\n\r\nSQL> create table t2(c int primary key);\r\n\r\nTable created.\r\n\r\nSQL> conn cmh\/cmh\r\nConnected.\r\nSQL> drop table t1 purge;\r\n\r\nTable dropped.\r\n\r\nSQL> drop table t2 purge;\r\n\r\nTable dropped.\r\n\r\nSQL> create table t1(c int primary key);\r\n\r\nTable created.\r\n\r\nSQL> create table t2(c int primary key);\r\n\r\nTable created.\r\n\r\nSQL>\r\n<\/pre>\nWe then show our CDR rules for these tables in our target replicat parameter file.<\/p>\n
\r\n----------------------------------------------------------------------------------------------------\r\nmap ATL.T1, target CMH.T1, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL), &\r\nRESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, IGNORE));\r\n----------------------------------------------------------------------------------------------------\r\nmap ATL.T2, target CMH.T2, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL);\r\n----------------------------------------------------------------------------------------------------\r\n<\/pre>\nAs you can see above, we ignore any missing delete conflicts on T1.<\/p>\n
We then proceed to create a conflict by loading two rows into our source schema tables T1 and T2 as ggadmin so they won’t be replicated (we ignore any operations by ggadmin). We also load a row into our CMH.T2 table. We do this so we can see if the second part of our transaction is completed after ignoring the first transaction that is handled by CDR. It will need to be handled by CDR as the row to delete won’t be found in CMH.T1.<\/p>\n
\r\nSQL> conn ggadmin\/ggadmin\r\nConnected.\r\nSQL> insert into atl.t1 values(1);\r\n\r\n1 row created.\r\n\r\nSQL> insert into atl.t2 values(1);\r\n\r\n1 row created.\r\n\r\nSQL> insert into cmh.t2 values(1);\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL>\r\n<\/pre>\nWe finally delete our rows in both ATL.T1 and ATL.T2, which are replicated to our target CMH schema.<\/p>\n
\r\nSQL> conn atl\/atl\r\nConnected.\r\nSQL> delete from t1;\r\n\r\n1 row deleted.\r\n\r\nSQL> delete from t2;\r\n\r\n1 row deleted.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL>\r\n<\/pre>\nAfter waiting for the transactions to come over, we see that our delete was handled by CDR…<\/p>\n
\r\nGGSCI (expressdb1) 1> stats corer, reportcdr, table atl.t1\r\n\r\nSending STATS request to REPLICAT CORER ...\r\n\r\nStart of Statistics at 2012-07-10 13:29:33.\r\n\r\nReplicating from ATL.T1 to CMH.T1:\r\n\r\n*** Total statistics since 2012-07-10 13:28:20 ***\r\n Total inserts 0.00\r\n Total updates 0.00\r\n Total deletes 1.00\r\n Total discards 0.00\r\n Total operations 1.00\r\n Total CDR conflicts 1.00\r\n CDR resolutions succeeded 1.00\r\n CDR DELETEROWMISSING conflicts 1.00\r\n\r\n*** Daily statistics since 2012-07-10 13:28:20 ***\r\n Total inserts 0.00\r\n Total updates 0.00\r\n Total deletes 1.00\r\n Total discards 0.00\r\n Total operations 1.00\r\n Total CDR conflicts 1.00\r\n CDR resolutions succeeded 1.00\r\n CDR DELETEROWMISSING conflicts 1.00\r\n\r\n*** Hourly statistics since 2012-07-10 13:28:20 ***\r\n Total inserts 0.00\r\n Total updates 0.00\r\n Total deletes 1.00\r\n Total discards 0.00\r\n Total operations 1.00\r\n Total CDR conflicts 1.00\r\n CDR resolutions succeeded 1.00\r\n CDR DELETEROWMISSING conflicts 1.00\r\n\r\n*** Latest statistics since 2012-07-10 13:28:20 ***\r\n Total inserts 0.00\r\n Total updates 0.00\r\n Total deletes 1.00\r\n Total discards 0.00\r\n Total operations 1.00\r\n Total CDR conflicts 1.00\r\n CDR resolutions succeeded 1.00\r\n CDR DELETEROWMISSING conflicts 1.00\r\n\r\nEnd of Statistics.\r\n<\/pre>\n…and show our data was deleted from all tables, including the CMH.T2 delete, which was processed after our CDR routine was run.<\/p>\n
\r\nSQL> select * from atl.t1;\r\n\r\nno rows selected\r\n\r\nSQL> select * from atl.t2;\r\n\r\nno rows selected\r\n\r\nSQL> select * from cmh.t1;\r\n\r\nno rows selected\r\n\r\nSQL> select * from cmh.t2;\r\n\r\nno rows selected\r\n\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"In GoldenGate 11.2.1.0, they introduced out of the box CDR (conflict detection and resolution). This is really useful in a bi-directional replication environment such as the one we are building. One question I had about this surrounded what happened if…<\/p>\n