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.
The options for conflict handlers are as follows:
| Operations | "NO_DATA" and "TOO_MANY" handlers | "Error" handler |
|---|---|---|
| Update | discard | ignore | force | retry | overwrite | pause | abort | newer | older | plsql | sql | error | discard | ignore | force | retry | overwrite | pause | abort | newer | older | plsql | sql | error |
| Delete | discard | ignore | force | retry | overwrite | pause | abort | plsql | sql | error | discard | ignore | force | retry | overwrite | pause | abort | plsql | sql | error |
| Insert | n/a | discard | ignore | force | retry | pause | abort | plsql | error |
| Transaction | n/a | discard | ignore | retry | pause | abort | error |
DISCARD and IGNORE handlers are synonyms.
The transaction handler is used for errors in DDL and during commit/rollback.
The update handler also covers LOB operations.
The "NO_DATA" and "TOO_MANY" handler is used only once for a conflict – if using this handler leads to a conflict again, "Error" handler is used for the next attempt. For the next change SQL, the Data conflict will be used again.
Additionally, logging may be specified:
- log: the conflict is logged, and an error PLOG is created with the offending statement
- nolog: the conflict is not logged
- fast_nolog: the conflict is not logged into the database and it may be omitted from conflict counters altogether
- log_transaction: the conflict is logged, and an error PLOG is created with the offending transaction
- default: same as log
Use SHOW_CONFLICT_HANDLERS to query current settings of handlers. See the Command Reference or use HELP SET_CONFLICT_HANDLERS for the exact syntax, example:
dbvrep> help set_conflict_handlers SET_CONFLICT_HANDLERS: Set apply conflict handlers. SET_CONFLICT_HANDLERS FOR TABLE owner.name FOR operation ON type TO handler [logging] [additional info] SET_CONFLICT_HANDLERS FOR DEFAULT FOR operation ON type TO handler [logging] [additional info] owner.name is name of the table on SOURCE database, use DEFAULT to set it for future added/created tables operation is one of UPDATE, DELETE, INSERT, TRANSACTION type is one of ERROR, TOO_MANY, NO_DATA (TOO_MANY and NO_DATA available for UPDATE or DELETE only) handler is one of RETRY, PAUSE, ABORT, DISCARD, FORCE, OVERWRITE, PLSQL, SQL, NEWER, OLDER (NEWER and OLDER available for DATA type only) logging is one of LOG, NOLOG, FAST_NOLOG, LOG_TRANSACTION (FAST_NOLOG may optimize processing and not update conflict counters) Additional info is 'BY COLUMNS comma-delimited-column-list 'for NEWER/OLDER, 'full-procedure-name' for PLSQL and 'regular expression' for SQL.
Default values
Default values for all available conflict types are defined by the following variables:
_DEFAULT_HANDLER_UPDATE_NO_DATA _DEFAULT_HANDLER_UPDATE_TOO_MANY _DEFAULT_HANDLER_UPDATE_ERROR _DEFAULT_HANDLER_DELETE_NO_DATA _DEFAULT_HANDLER_DELETE_TOO_MANY _DEFAULT_HANDLER_DELETE_ERROR _DEFAULT_HANDLER_INSERT_ERROR _DEFAULT_HANDLER_TRANSACTION_ERROR
These variables are used for both tables and default conflict handlers. This default value is used when there are no conflict handlers defined for a given table for a given conflict. For example, the default value for _DEFAULT_HANDLER_INSERT_ERROR is retry. You can change the default value to another value (see chart above), creating a new default value. For example, you could set it to DISCARD. Discard will be the new default for all table. If you desire a certain table to not have discard and have another option, PAUSE, for example you can set a conflict handers for that individual table and leave the default (that you set) alone.
To set the conflict handlers for a given table (or to set default conflict handlers) to the default values (determined by the variables shown above) use UNSET_CONFLICT_HANDLERS command (see Full Command-Line Reference).
Examples
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE SCOTT.AVI_OBJECTS FOR UPDATE ON NO_DATA TO OVERWRITE
Source tablename is used while setting conflict handlers.
To show what the conflict handler has been set to:
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:
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:
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:
(#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:
SET_CONFLICT_HANDLERS FOR TABLE SCOTT.X FOR UPDATE ON TOO_MANY TO SQL s/$/ and rownum = 1/
DELETE:
SET_CONFLICT_HANDLERS FOR TABLE SCOTT.X FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/
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
- 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).
For the full syntax for conflict handlers see Full Command-Line Reference