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’.
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.
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.
The prerequisites for The Delta Resolution Method are like the basic Auto-CDR using the Latest Timestamp method:
The implementation of the Delta Resolution method is simple.
On all participating databases, the following steps are required for each table in interest:
SQL> exec DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR('FINANCE','BANKING')
SQL> exec DBMS_GOLDENGATE_ADM. ADD_AUTO_CDR_DELTA_RES ('FINANCE','BANKING','ACCOUNT')
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 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.
Previous Post