The 3 Fundamentals of Oracle Replication [Part 3 of 3]

This is part 3 of 3 in the series on "The three Oracle logical replication fundamentals". Part 1 can be found here The 3 Fundamentals Principles of Oracle Replication [Part 1 of 3] The third fundamental of logical based Oracle replication is: Conflict resolution can cause data divergence and lost updates. Lets define what a conflict is: A conflict occurs when there is a potential for the data between the source and target databases to be out of sync. This is also referred to as data divergence. The sooner data divergence is detected the easier it is to fix. So it is very important that the conflict is detected as soon as it occurs, the replication is stopped, and the conflict is resolved so that the replication can continue. A conflict occurs when either one or more of the following are true:

SQL affects zero rows. This causes data divergence and so a conflict is raised. This could also mean that the target data has changed independently of the source data.
SQL affects more than one row. This causes data divergence and so a conflict is raised.
Oracle error. This can range from usual primary key or foreign key violation (another type of data divergence) to purely technical reasons (cannot extend datafile).
Lock timeout. If the replication waits too long for a row lock, a conflict should also be reported.

But how is a conflict detected? Conflict detection is key to ensuring there is no data divergence. Item 3 and Item 4 are easily picked up because they are Oracle errors. Item 2 (SQL affects more than one row) can also be picked up because the update statement will return more than 1 row updated. This will result if each row in the target table is not uniquely identified (see fundamental 2). In Dbvisit Replicate this is called a "TOO_MANY" conflict. Item 1 (SQL affects zero rows) can be detected because when applying a row-change, previous values of the row at the target are checked (by means of adding them to the where clause). This effectively detects if the data in that row is inconsistent between source and target, and this is reported as a conflict, "0 rows updated/deleted." This can happen for update or delete only (there is no previous row to check for insert). In Dbvisit Replicate this is called a "NO_DATA" conflict. How is a "NO_DATA" conflict (SQL affects zero rows) detected? Let's use an example of a SALES table which has the following values on source and target (they are in sync):

PROD_ID AMOUNT_RECEIVED
101      250

When the following SQL is issued on the source database:

update SALES 
set AMOUNT_RECEIVED = 120 
where PROD_ID = 101;

The replicated SQL that is applied on the target database is as follows (note the WHERE clause contains the old value of AMOUNT_RECEIVED):

update SALES
set AMOUNT_RECEIVED = 120
where (1=1)
and AMOUNT_RECEIVED = 250 
and PROD_ID = 101;

If in the meantime, another transaction on the target database had changed AMOUNT_RECEIVED from 250 to another value (say 199), then the above update would affect zero rows and the conflict would occur. This is because the WHERE clause (AMOUNT_RECEIVED = 250) would no longer be satisfied because the value is now 199. Raising the conflict and pausing the replication ensures that the user is notified and no previous data (in this case 199) will be lost. To minimize the possibility of conflicts, it is very important to ensure that the data must be in sync before replication starts. This is called Data instantiation and this is a topic for a future blog. This completes the third fundamental of Oracle replication and the series. To finish up, lets review the three fundamentals again of Oracle replication: Logical replication fundamentals - Review

All set based SQL operations are converted to row-by-row SQL changes
For the data in the table to be replicated, each row in the table must be uniquely defined
Conflicts can cause data divergence and lost updates