GoldenGate conflict detection in 11.2.1.0 and ignore when multiple statements are in a transaction

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 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?

The test below shows that the other statements in the transaction are successfully processed.

We start by showing our tables…

SQL> conn atl/atl
Connected.
SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1(c int primary key);

Table created.

SQL> create table t2(c int primary key);

Table created.

SQL> conn cmh/cmh
Connected.
SQL> drop table t1 purge;

Table dropped.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t1(c int primary key);

Table created.

SQL> create table t2(c int primary key);

Table created.

SQL>

We then show our CDR rules for these tables in our target replicat parameter file.

----------------------------------------------------------------------------------------------------
map ATL.T1, target CMH.T1, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL), &
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, IGNORE));
----------------------------------------------------------------------------------------------------
map ATL.T2, target CMH.T2, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL);
----------------------------------------------------------------------------------------------------

As you can see above, we ignore any missing delete conflicts on T1.

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.

SQL> conn ggadmin/ggadmin
Connected.
SQL> insert into atl.t1 values(1);

1 row created.

SQL> insert into atl.t2 values(1);

1 row created.

SQL> insert into cmh.t2 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>

We finally delete our rows in both ATL.T1 and ATL.T2, which are replicated to our target CMH schema.

SQL> conn atl/atl
Connected.
SQL> delete from t1;

1 row deleted.

SQL> delete from t2;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

After waiting for the transactions to come over, we see that our delete was handled by CDR…

GGSCI (expressdb1) 1> stats corer, reportcdr, table atl.t1

Sending STATS request to REPLICAT CORER ...

Start of Statistics at 2012-07-10 13:29:33.

Replicating from ATL.T1 to CMH.T1:

*** Total statistics since 2012-07-10 13:28:20 ***
        Total inserts                                      0.00
        Total updates                                      0.00
        Total deletes                                      1.00
        Total discards                                     0.00
        Total operations                                   1.00
        Total CDR conflicts                                1.00
        CDR resolutions succeeded                          1.00
        CDR DELETEROWMISSING conflicts                     1.00

*** Daily statistics since 2012-07-10 13:28:20 ***
        Total inserts                                      0.00
        Total updates                                      0.00
        Total deletes                                      1.00
        Total discards                                     0.00
        Total operations                                   1.00
        Total CDR conflicts                                1.00
        CDR resolutions succeeded                          1.00
        CDR DELETEROWMISSING conflicts                     1.00

*** Hourly statistics since 2012-07-10 13:28:20 ***
        Total inserts                                      0.00
        Total updates                                      0.00
        Total deletes                                      1.00
        Total discards                                     0.00
        Total operations                                   1.00
        Total CDR conflicts                                1.00
        CDR resolutions succeeded                          1.00
        CDR DELETEROWMISSING conflicts                     1.00

*** Latest statistics since 2012-07-10 13:28:20 ***
        Total inserts                                      0.00
        Total updates                                      0.00
        Total deletes                                      1.00
        Total discards                                     0.00
        Total operations                                   1.00
        Total CDR conflicts                                1.00
        CDR resolutions succeeded                          1.00
        CDR DELETEROWMISSING conflicts                     1.00

End of Statistics.

…and show our data was deleted from all tables, including the CMH.T2 delete, which was processed after our CDR routine was run.

SQL> select * from atl.t1;

no rows selected

SQL> select * from atl.t2;

no rows selected

SQL> select * from cmh.t1;

no rows selected

SQL> select * from cmh.t2;

no rows selected

SQL>