Auto-CDR: Delta Resolution method

February 9, 2024 | 6 minute read
Volker Kuhr
Senior Principal Product Manager
Text Size 100%:

I used the Latest Timestamp conflict resolution method as a primary example in the previous article. The Latest Timestamp is commonly used in replication environments as it mimics the behavior of a database instance; the more recent DML operation updates the row after the previous transaction commits (or rolls back) and releases the resource. As GoldenGate is an asynchronous Replication, no locks prevent updating the row, but the Latest Timestamp conflict resolution depicts the same solution.  In this Blog, I want to highlight another Conflict Resolution method exclusive to numerical data types and explain its usage with several use cases. The Delta Conflict Resolution method uses the difference, or delta, of the before and after images of a specific column to determine which value should be applied.

Case #1: Financial Applications
In this example, lets assume that your banking account (with a new initial balance of $1000) is managed by two databases (WEST and EAST) in an active-active configuration. Both databases are kept consistent by GoldenGate Replication. Whenever you withdraw money from the account on one database, the logical change is replicated in the other database and vice versa.
In the case that you receive a grant of $700 at the EAST database while you withdraw $50 at the WEST database at almost the same time (there might be a higher network latency during both transactions), you want to make sure that the $700 are added and $50 are subtracted. The conflict (a different value of the before image of the change and the target image) is detected, and the resolution method guarantees that the entire system remains consistent. In Delta Resolutions case, you manage the account by the difference of the before and after image rather than the absolute numbers.

Here is the formula:        Balance new = Balance old + ΔEast         + ΔWest
As an example:                 $1700          = $1000        + ($750) + (-$50)

The following picture shows how GoldenGate manages the Delta Resolution in case of a conflict:

You also notice in this example that the time information is not needed.

Case 2: Order System
This example reviews an inventory management uses the Delta Conflict Resolution method. Assume there are two databases at different locations sharing information on available items (say cookies) within the entire system. At the beginning of the day, the system shows 250 items available. With each order, the number of items is decremented. At the database of site A, clients order one or multiple items; at site B, clients also order one or multiple items. Without any conflicts, the overall items are going down by time: 250, 247, 246, …. In case of a conflict, it must be guaranteed that both decrements are considered in the global system. Again, the conflict resolution does not use the after image of the change but the delta value from both sites. If there was an order (decrement of items) of 15 items at site A while there was another order (decrement of the items) of 70 items at site B, the overall system must decrement both changes:  

85 (new value on site A) = 170 (old value on site A) – 70 (orders site B) – 15 (orders site A).

Even if it is a different use case than the first, the operations are very similar to the previous use case, except that values are only decremented – a kind of ‘Countdown’.

Case #3: Warehouse Systems

 

In this example, there are four different physical entry points to a warehouse location (each managed by a database, and all databases are synchronized by GoldenGate replication): Database WEST, EAST, NORTH, and SOUTH. Each time,a truck is entering or leaving the warehouse, the imported or exported mineable ore is measured. If one truck is entering the designated area and imports mineable ore, the overall mineable ore is increasing, and the new value is replicated to the system.

A diagram of a factoryDescription automatically generated

If a truck leaves at another entry point and exports mineable ore, the overall mineable ore is decreasing. In the case that there is high activity in the warehouse location as trucks are constantly coming and going and there is a network outage, the business can still succeed with the Delta Resolution method that kicks in once the network is back. Again, all participating databases are detecting the conflicts, and the resolution is done with the difference (delta) of the images and not with the after image. BTW: You can also expose this example to a Customs Border Protection System where you have many more entry ports.

Prerequisites and Implementation Details

The prerequisites for The Delta Resolution Method are like the basic Auto-CDR using the Latest Timestamp method:

  • The source and target databases must be Oracle databases.
  • Auto-CDR only works with Parallel Replicat in integrated mode or Integrated Replicat.
  • The table has a primary/unique key.
  • The Delta Resolution column has a numeric data type.

The implementation of the Delta Resolution method is simple.

On all participating databases, the following steps are required for each table in interest:

  1. Run the basic ADD_AUTO_CDR procedure from the DBMS_GOLDENGATE_ADM package to setup the basics. Underneath, Supplemental Logging is added on the table level and the base table is adjusted with an invisible timestamp column, and the tombstone table is created. By default, you only need to address the schema and table name:
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> exec DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR('FINANCE','BANKING')
  1. Run the ADD_AUTO_CDR_DELTA_RES procedure from the DBMS_GOLDENGATE_ADM package.
    Besides the schema and table name, you also address the column name which must be a numeric data type:
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> exec DBMS_GOLDENGATE_ADM. ADD_AUTO_CDR_DELTA_RES ('FINANCE','BANKING','ACCOUNT')
  1. To double check the Auto-CDR set you might want to query the following dictionary views:
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> SELECT table_owner, table_name, row_resolution_method FROM <b>dba_gg_auto_cdr_tables</b>;

      TABLE_OWNER     TABLE_NAME      ROW_RESOLUTION_METHOD  
      --------------- --------------- -------------------------
      FINANCE         BANKING         LATEST TIMESTAMP   

     SQL> SELECT * FROM <b>dba_gg_auto_cdr_columns</b>;

     TABLE_OWNER     TABLE_NAME      COLUMN_GROUP_NAME         COLUMN_NAME     RESOLUTION_COLUMN  
     --------------- --------------- ------------------------- --------------- --------------------
     FINANCE         BANKING         <b>DELTA$</b>                    ACCOUNT                            
     FINANCE         BANKING         IMPLICIT_COLUMNS$         EMAIL_ADDRESS   CDRTS$ROW          
     FINANCE         BANKING         IMPLICIT_COLUMNS$         NAME            CDRTS$ROW

Note that this solution is about Conflict Resolution. The Conflict Detection is the same as for the Auto-CDR solution using the Latest Timestamp described in the previous article.

This Blog describes a ‘special’ use case related to Conflict Resolution method. While the implementation is very simple and straightforward, this solution might appear astonishing in firsthand. Still, I hope that the three use cases give a clear understanding of when to use the Delta Resolution method and open opportunities for other business cases.

Volker Kuhr

Senior Principal Product Manager

Volker is a Senior Principal Product Manager working in the GoldenGate Development group.
His primary focus is on the GoldenGate Core Product, mainly GoldenGate for Oracle. Key topics are Performance, High Availability, Security, and Resilience.
Volker has worked for more than 20 years in the field of database technology and data replication.
He has supported customers worldwide in different industries to develop & manage distributed database systems/applications and build Data Integration Solutions.

Show more

Previous Post

Oracle GoldenGate EM PlugIn 13.5.2.0.3 is available Now!

Next Post


OCI GoldenGate Stream Analytics is now generally available

Alex Kotopoulis | 2 min read