Two-way replication conflict example

WIth 2-way replication there is more chance of getting conflicts than with 1-way replication. This is because both sides are being updated in real time. 

By way of the following example, it is demonstrated how Dbvisit Replicate can assist with conflicts in 2-way replication.

Example:

We have 2 databases in our 2-way replication. Database A and Database B.

We have the following table with the same data in both databases:

Table TOLL.TOLL_ZONE
Id  Name      Update_date
--  --------  -----------------
1   Lane 1    10/10/2012 23:00
2   Lane 2    09/10/2012 21:00

Now the following scenario happens:

1. 2-Way replication is configured between table TOLL_ZONE in Database A and Database B
2. Both tables are in sync
3. Communication between database A and database B breaks and Dbvisit Replicate is no longer able to replicate changes
4. Record with id=2 is updated on database A and database B with different values:

DATABASE A
Table TOLL.TOLL_ZONE
Id  Name      Update_date
--  --------  -----------------
1   Lane 1    10/10/2012 23:00
2   Lane 222  12/12/2012 09:45  <== Updated

DATABASE B
Table TOLL.TOLL_ZONE
Id  Name      Update_date
--  --------  -----------------
1   Lane 1    10/10/2012 23:00
2   Lane 200  12/12/2012 10:10  <== Updated latest

5. Communication is restored
6. What will happen to the replication now that there are 2 different values in each database?

Once the replication continues and each update statement is replicated to the other database, both side will see a NO_DATA conflict. A NO_DATA conflict means that the update found 0 rows to be updated. This is because each update will have the old value in the where clause (Name = "Lane 2") and this value no longer exists on each side. 

How to ensure no conflict is raised

Depending on the business rules, there are different ways to set specific handlers on this table to ensure that a conflict is never raised and that it is automatically dealt with.

If we assume that the newer or latest value is always the value that we want, then we can set the following conflict handler:

dbvrep> set SET_CONFLICT_HANDLERS for table TOLL.TOLL_ZONE for update on NO_DATA to NEWER by columns UPDATE_DATE

This conflict handler:

• Is set for all update statements run against the TOLL.TOLL_ZONE table
• Will use the UPDATE_DATE column in the TOLL.TOLL_ZONE table to determine which is the NEWER data
• Will overwrite the data that is in the TOLL.TOLL_ZONE with the update statement if UPDATE_DATE in the update statement is newer than in the TOLL.TOLL_ZONE table
• Will ignore the update statement if UPDATE_DATE in the update statement is older than in the TOLL.TOLL_ZONE table

The end result is that both tables will be back in sync with the following data:

DATABASE A
Table TOLL.TOLL_ZONE
Id  Name      Update_date
--  --------  -----------------
1   Lane 1    10/10/2012 23:00
2   Lane 200  12/12/2012 10:10
DATABASE B
Table TOLL.TOLL_ZONE
Id  Name      Update_date
--  --------  -----------------
1   Lane 1    10/10/2012 23:00
2   Lane 200  12/12/2012 10:10

To see the existing conflict handlers for a table, please see:

dbvrep> SHOW_CONFLICT_HANDLERS FOR TABLE owner.name: owner.name is name of the table on SOURCE database.

CHOOSE REPLICATION

By default all commands in the Dbvisit Replicate console work with the default processes MINE and APPLY. The choose replication command is useful when working with 2-way replication where there are multiple processes such as MINE, APPLY, MINE1 and APPLY1. 

Example:

dbvrep> CHOOSE REPLICATION MINE1

The above command will choose the replication pair that is associated with MINE1. Typically this is MINE1->APPLY1.