Page Comparison

The response to a conflict can be configured before the conflict occurs. This is done with conflict handlers. The configuration can be specified for each replicated table and for each operation type separately, and for the special case ("data divergence") when zero or more than one row are affected. The Dbvisit Replicate command to set conflict handlers is SET_CONFLICT_HANDLERS.

...

No Format

bgColor	CCC

dbvrep> SET_CONFLICT_HANDLERS FOR TABLE SCOTT.AVI_OBJECTS FOR UPDATE ON NO_DATA TO OVERWRITE

Note
Source tablename is used while setting conflict handlers.

To show what the conflict handler has been set to:

No Format

bgColor	CCC

dbvrep> show_conflict_handlers for table scott.avi_objects 
The table called SCOTT.AVI_OBJECTS on source is handled on apply as follows:
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: OVERWRITE logging: LOG
UPDATE (too_many): handler: OVERWRITE logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG

Example

The following example shows an example of a duplicate row conflict handler.

Create a simple table with two duplicate rows:

No Format

bgColor	CCC

create table scott.x (i number);
insert into x values (1);
insert into x values (1);
commit;

On the source database, the user issues the following update:

No Format

bgColor	CCC

update x set i=2 where i=1; 
commit;

This update will affect 2 rows in the table.

The MINE process will see this as two row changes, so the APPLY process will execute:

No Format

bgColor	CCC

(#1) update x set i=2 where i=1;
(#2) update x set i=2 where i=1;

The first statement conflicts with "2 rows updated".

There are several ways to handle this conflict:

FORCE: both rows are changed. APPLY now executes #2 - and conflicts again, but this time with "0 rows updated" as both records now i=2. This conflict has to be handled as well with a NO_DATA conflict handler. This second conflict has to be ignored because the second update is no longer needed.
SQL (rownum=1): only one row is changed. APPLY now executes #2 - only 1 row is updated, so no conflict.

Option 2) is the best approach – it handles only duplicates, not other conflicts.

Option 1) would (silently) handle other types of conflicts.

The syntax for Option 2 is:

UPDATE:

No Format

bgColor	CCC

SET_CONFLICT_HANDLERS FOR TABLE SCOTT.X FOR UPDATE ON TOO_MANY TO SQL s/$/ and rownum = 1/

DELETE:

No Format

bgColor	CCC

SET_CONFLICT_HANDLERS FOR TABLE SCOTT.X FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/

Info

Current conflicts will not be affected by newly defined CONFLICT HANDLERS. To resolve current conflicts there are 2 options:

Manually resolve with RESOLVE CONFLICT, OR
Restart the APPLY process. This will enable the newly defined conflict handler to manage the current conflict

Note

When the default global handlers are updated, this will only APPLY to newly prepared tables. Tables that have already been prepared (or replicated) will still use the previous defined default global handler.
We recommend to not set nontrivial default conflict handlers for the ERROR handlers. The reason is that if you get 0/many rows affected, the DATA handlers like OVERWRITE, NEWER, OLDER are a good way how to handle the conflict. If they fail, they fall through to the ERROR handler. If the ERROR handler is set to OVERWRITE, NEWER, OLDER, then the ERROR handler could loop forever.
The default handlers are also used if the conflict is "global", i.e. it's not for a particular table. The most prominent example is a conflict for a commit (e.g. a failed deferred constraint).
The column(s) specified in the by columns clause in newer/older handlers must not be a LOB or LONG data type.

For the full syntax for conflict handlers see Full Command-Line Reference

Versions Compared

Old Version 9

New Version 10

Key

Example