Page Comparison

...

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.

...

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.