Replication changes the set based operation on the source database to row-by-row changes on the target database. This is true for all replication based solutions. 

SQL issued on the source database can change arbitrary number of rows. This is mined by Dbvisit Replicate as row-by-row changes, and the changes are applied at the target database as row-by-row changes, only affecting one row at a time.

The consequence is that the SQL issued at the APPLY database is not the same issued against MINE – instead, each SQL updates/deletes/inserts exactly one row, applying just one change.

Definition of a conflict

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.

If either one or more of the following are true, then a conflict is raised:

1. 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.
2. SQL affects more than one row. This causes data divergence and so a conflict is raised.
3. 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).
4. Lock timeout. If the APPLY waits for a row lock more than WATCHDOG_TIMEOUT seconds, a conflict is also reported.

Notes:

• Data divergence means that the data between the source and the target database is no longer in sync. The sooner data divergence is detected the easier it is too fix. Dbvisit Replicate will detect and notify as soon as any of the above issues are true (unless default conflict settings have been changed).
• When applying a row-change, previous values of the row at APPLY are checked (by means of adding them to the where clause). This effectively detects if the data in that row are inconsistent between MINE and APPLY, 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) and due to its special role, it can be handled differently.

How is a conflict detected?

To detect that the target data has changed independently of the source data, Dbvisit Replicates includes the changed or updated column in the where clause. 

Example

A SALES table has the following values on source and target (they are in sync):

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 AMOUT_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 not be satisfied.

This ensures that the user is notified and no previous data will be lost.