Conflict Resolution

Home | Previous Page | Next Page Setting Up and Managing Enterprise Replication > Selecting the Enterprise Replication System and Network Topology > Selecting the Enterprise Replication System >

Conflict Resolution

When multiple database servers try to update the same row simultaneously (the time stamp for both updates is the same GMT time), a collision occurs. For more information, see Applying Replicated Data. Enterprise Replication must determine which new data to replicate. To solve conflict resolution, you must specify the following for each replicate:

A conflict-resolution rule
The scope of the rule

Conflict-Resolution Rule

Enterprise Replication supports the following conflict-resolution rules.

Conflict Resolution Rule	Effect	Reference
Ignore	Enterprise Replication does not attempt to resolve conflicts.	page Ignore Conflict-Resolution Rule
Time stamp	The row or transaction with the most recent time stamp is applied.	page Time-Stamp Conflict-Resolution Rule
SPL routine	Enterprise Replication uses a routine written in SPL (Stored Procedure Language) that you provide to determine which data should be applied.	page SPL Conflict-Resolution Rule
Time stamp with SPL routine	If the time stamps are identical, Enterprise Replication invokes an SPL routine that you provide to resolve the conflict.	page Time-Stamp Conflict-Resolution Rule, page SPL Conflict-Resolution Rule
Always-apply	Enterprise Replication does not attempt to resolve conflicts.	page Always-Apply Conflict-Resolution Rule

For all conflict-resolution rules except ignore and always-apply, you must create shadow columns in the tables on both the source and target servers involved in replication. For more information, see Shadow Columns.

Enterprise Replication supports up to two conflict-resolution rules for each replicate: a primary rule and a secondary rule (if desired). Table 9 shows the valid combinations of Enterprise Replication conflict-resolution rules.

Table 9. Valid Conflict-Resolution Rule Combinations
Primary Rule	Secondary Rule
Ignore	None
Time stamp	None
Time stamp	SPL routine
SPL routine	None
Always-apply	None

Ignore Conflict-Resolution Rule

The ignore conflict-resolution rule does not attempt to detect or resolve conflicts. A row or transaction either applies successfully or it fails. A row might fail to replicate because of standard database reasons, such as a deadlock situation, when an application locks rows.

The ignore conflict-resolution rule can only be used as a primary conflict- resolution rule and can have either a transaction or a row scope (as described in Scope). Table 10 describes the ignore conflict-resolution rule.

Table 10. Ignore Conflict-Resolution Rule
Row Exists in Target?	Database Operation
Row Exists in Target?	Insert	Update	Delete
No	Apply row	Discard row	Discard row
Yes	Discard row	Apply row	Apply row

When a replication message fails to apply to a target, you can spool the information to one or both of the following directories:

Aborted-transaction spooling (ATS)
If selected, all buffers in a failed replication message that compose a transaction are written to this directory.
Row-information spooling (RIS)
If selected, the replication message for a row that could not be applied to a target is written to this directory.

For more information, see Monitoring and Troubleshooting Enterprise Replication.

Time-Stamp Conflict-Resolution Rule

The time-stamp rule evaluates the latest time stamp of the replication against the target and determines how to resolve any conflict.

Tip:

All time stamps and internal computations are performed in Greenwich Mean Time (GMT).

The time-stamp resolution rule behaves differently depending on which scope is in effect:

Row scope
Enterprise Replication evaluates one row at a time. The row with the most recent time stamp wins the conflict and is applied to the target database servers. If an SPL routine is defined as a secondary conflict-resolution rule, the routine resolves the conflict when the row times are equal.
Transaction scope
Enterprise Replication evaluates the most recent row-update time among all the rows in the replicated transaction. This time is compared to the time stamp of the appropriate target row. If the time stamp of the replicated row is more recent than the target, the entire replicated transaction is applied. If a routine is defined as a secondary conflict-resolution rule, it is used to resolve the conflict when the time stamps are equal.

For more information, see Scope.

A secondary routine is invoked only if Enterprise Replication evaluates rows and discovers equal time stamps.

If no secondary conflict-resolution rule is defined and the time stamps are equal, the transaction from the database server with the lower value in the cdrserver shadow column wins the conflict.

Table 11 shows how a conflict is resolved based on the latest time stamp with row scope. The time stamp T_{last_update} (the time of the last update) represents the row on the target database server with the last (most recent) update. The time stamp T_repl (the time when replication occurs) represents the time stamp on the incoming row.

Enterprise Replication first checks to see if a row with the same primary key exists in either the target table or its corresponding delete table.

Important:

Do not remove the delete tables created by Enterprise Replication. The delete table is automatically removed when the last replicate defined with conflict resolution is deleted.

If the row exists, Enterprise Replication uses the latest time stamp to resolve the conflict.

Table 11. Conflict Resolution Based on the Time Stamp
Row Exists on Target?	Time Stamp	Database Operation
Row Exists on Target?	Time Stamp	Insert	Update	Delete
No	n/a	Apply row	Apply row (Convert UPDATE to INSERT)	Apply row (INSERT into Enterprise Replication delete table)
Yes	T_{last_update} < T_repl	Apply row (Convert INSERT to UPDATE)	Apply row	Apply row
	T_{last_update} > T_repl	Discard row
	T_{last_update} = T_repl	Apply row if no routine is defined as a secondary conflict-resolution rule. Otherwise, invoke the routine.

The time-stamp conflict-resolution rule assumes time synchronization between cooperating Enterprise Replication servers. For more information, see Time Synchronization.

SPL Conflict-Resolution Rule

Tip:

The SPL rule allows you complete flexibility to determine which row prevails in the database. However, for most users, the time-stamp conflict-resolution rule provides sufficient conflict resolution.

You can assign an SPL routine as the primary conflict-resolution rule. If you use an SPL routine as a secondary conflict-resolution rule, the time-stamp conflict-resolution rule must be the primary rule.

Important:

The owner of an SPL routine used for conflict resolution must be the same as the owner of the table.

Routines for conflict-resolution must be in SPL. Enterprise Replication does not allow user-defined routines in C or in Java.

Important:

You cannot use an SPL routine or a time stamp with an SPL routine if the replicate is defined to replicate only changed columns or the replicated table contains any extensible data types. See Replicating Only Changed Columns.

Enterprise Replication passes the following information to an SPL routine as arguments.

Argument	Description
Server name [CHAR(18)]	From the local target row NULL if local target row does not exist
Time stamp (DATETIME YEAR TO SECOND)	From the local target row NULL if local target row does not exist
Local delete-table indicator [CHAR(1)] or Local key delete-row indicator [CHAR(1)]	Y indicates the origin of the row is the delete table. K indicates the origin of the row is the replicate-key delete row. If a conflict occurs while deleting a primary key row, because the local row with the old key no longer exists, the received key delete row is passed as the local row (using the seventh argument, local row data, described below). The received key insert row is passed to the stored procedure as the replicated row using the eighth argument, described below.
Server name [CHAR(18)]	Of the replicate source
Time stamp (DATETIME YEAR TO SECOND)	From the replicated row
Replicate action type [CHAR(1)]	`I` - insert `D` - delete `U` - update
Local row data returned in regular SQL format	Where the regular SQL format is taken from the SELECT clause of the participant list
Replicate row data after-image returned in regular SQL format	Where the regular SQL format is taken from the SELECT clause of the participant list

The routine must set the following arguments before the routine can be applied to the replication message.

Argument Description

An indicator of the desired database operation to be performed [CHAR(1)]

Argument	Description
An indicator of the desired database operation to be performed [CHAR(1)]	Same as the replicate action codes with the following additional codes `A` - Accept the replicated row and apply the column values returned by the SPL routine. For example, if Enterprise Replication receives an insert and the row already exists locally, the insert is converted to an update `S` - Accept the replicated row and apply the column values as received from the other site. For example, if Enterprise Replication receives an insert and the row already exists locally, the insert fails at the time Enterprise Replication tries to apply the transaction to the database, and the transaction aborts with an SQL error. `O` - Discard the replicated row. `X` - Abort the transaction.
A non-zero integer value to request logging of the conflict resolution and the integer value in the spooling files (INTEGER)	Logging value takes effect only if logging is configured for this replicate.
The columns of the row to be applied to the target table replicate action type in regular SQL format	This list of column values is not parsed if the replicate action type that the routine returns is `S`, `O`, or `X`.

Same as the replicate action codes with the following additional codes

A - Accept the replicated row and apply the column values returned by the SPL routine.

For example, if Enterprise Replication receives an insert and the row already exists locally, the insert is converted to an update

S - Accept the replicated row and apply the column values as received from the other site.

For example, if Enterprise Replication receives an insert and the row already exists locally, the insert fails at the time Enterprise Replication tries to apply the transaction to the database, and the transaction aborts with an SQL error.

O - Discard the replicated row.
X - Abort the transaction.

A non-zero integer value to request logging of the conflict resolution and the integer value in the spooling files (INTEGER) Logging value takes effect only if logging is configured for this replicate.

The columns of the row to be applied to the target table replicate action type in regular SQL format This list of column values is not parsed if the replicate action type that the routine returns is S, O, or X.

You can use the arguments to develop application-specific routines. For example, you can create a routine in which a database server always wins a conflict regardless of the time stamp.

The following list includes some items to consider when you use an SPL routine for conflict resolution:

Any action that a routine takes as a result of replication does not replicate.
You cannot use an SPL routine to start another transaction.
Frequent use of routines might affect performance.

In addition, you must determine when the SPL routine executes:

An optimized SPL routine is called only when a collision is detected and the row to be replicated fails to meet one of the following two conditions:
- It is from the same database server that last updated the local row on the target table.
- It has a time stamp greater than or equal to that of the local row.
A nonoptimized SPL routine executes every time Enterprise Replication detects a collision. By default, SPL routines are nonoptimized.

For information on specifying that the SPL routine is optimized, see Conflict Options.

Tip:

Do not assign a routine that is not optimized as a primary conflict-resolution rule for applications that usually insert rows successfully.

Always-Apply Conflict-Resolution Rule

The always-apply conflict-resolution rule does not attempt to detect or resolve conflicts. Unlike with the ignore conflict-resolution rule, replicated changes are applied even if the operations are not the same on the source and target servers. In the case of a conflict, the current row on the target is deleted and replaced with the replicated row from the source.

Table 12 describes the always-apply conflict-resolution rule.

Table 12. Always-Apply Conflict-Resolution Rule
Row Exists in Target?	Database Operation
Row Exists in Target?	Insert	Update	Delete
No	Apply row	Apply row (convert UPDATE to INSERT)	Apply row (INSERT into Enterprise Replication delete table, no error returned)
Yes	Apply as an UPDATE (overwrite the existing row)	Apply row	Deletes the row

Scope

Each conflict-resolution rule behaves differently depending on the scope. Enterprise Replication uses the following scopes:

Row scope
When you choose a row scope, Enterprise Replication evaluates one row at a time. It only applies replicated rows that win the conflict resolution with the target row. If an entire replicated transaction receives row-by-row evaluation, some replicated rows are applied while other replicated rows might not be applied.
Transaction scope
When you choose a transaction scope, Enterprise Replication applies the entire transaction if the replicated transaction wins the conflict resolution. If the target wins the conflict (or other database errors are present), the entire replicated transaction is not applied.

A transaction scope for conflict resolution guarantees transactional integrity.

Important:

Enterprise Replication defers some constraint checking on the target tables until the transaction commits. If a unique constraint or foreign-key constraint violation is found on any row of the transaction at commit time, the entire transaction is rejected (regardless of the scope) and, if you have ATS set up, written to the ATS directory. For more information, see Aborted Transaction Spooling Files.