Delete wins conflict resolution rule

The delete wins rule ensures that DELETE and INSERT operations win over UPDATE operations and that all other conflicts are resolved by comparing time stamps.

All time stamps and internal computations are performed in Greenwich mean time (GMT). The delete wins conflict-resolution rule assumes time synchronization between cooperating Enterprise Replication servers.

The delete wins rule is similar to the time stamp rule except that it prevents upsert operations and does not use a secondary conflict resolution rule. The delete wins rule prevents upsert operations that results from an UPDATE operation that is converted to an INSERT operation because the row to update was not found on the target server. An upsert operation can occur if a row is deleted from a target server before an UPDATE operation is processed on that target server or if an UPDATE operation was processed by the target server before the INSERT operation for that row. Depending on your business logic, upsert operations might violate referential integrity.

The delete wins rule prevents upsert operations in the following ways:

  • If a row is deleted on a replication server, that row is deleted on all other replication servers, regardless of whether an UPDATE operation to that row occurred after the delete.
  • If an UPDATE operation to a row is received before its INSERT operation, the UPDATE operation fails and generates and ATS or RIS file. The INSERT operation succeeds, but results in data inconsistency. To repair the inconsistency, run the cdr check replicate command with the --repair option.

The delete wins rule handles time stamp conflicts 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.

  • 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 the time stamps are equal, the transaction from the database server with the lower value in the cdrserver shadow column wins the conflict.

The following table shows how a conflict is resolved with the delete wins rule with row scope. The time stamp Tlast_update (the time of the last update) represents the row on the target database server with the last (most recent) update. The time stamp Trepl (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 replication key exists in either the target table or its corresponding delete table. If the row exists, Enterprise Replication uses the latest time stamp to resolve the conflict.

The following table describes how the delete wins conflict resolution rule handles INSERT, UPDATE, and DELETE operations that are performed on the source server.

Table 1. Conflict Resolution Based on the Time Stamp
Row Exists on Target? Time Stamp INSERT UPDATE DELETE
No n/a Apply row Discard row and generate and ATS or RIS file Apply row (INSERT into Enterprise Replication delete table)
Yes Tlast_update < Trepl Apply row (Convert INSERT to UPDATE) Apply row Apply row
Yes Tlast_update > Trepl Discard row Discard row Apply row
Yes Tlast_update = Trepl The server with the lower value in the cdrserver shadow column wins the conflict. The server with the lower value in the cdrserver shadow column wins the conflict. The server with the lower value in the cdrserver shadow column wins the conflict.
Important: Do not remove the delete tables that are created by Enterprise Replication. The delete table is automatically removed when the last replicate defined with conflict resolution is deleted.

To use delete wins conflict resolution for repairing inconsistencies with the cdr check replicate or cdr check replicateset command, include the --timestamp and --deletewins options with the --repair option. Also set the CDR_DELAY_PURGE_DTC configuration parameter to the maximum age of modifications to rows that are being actively updated. If you temporarily stop replication on a server whose replicates use the delete wins conflict resolution rule, disable the replication server with the cdr disable server command. When you disable a server, information about deleted rows is kept in the delete tables to be used during the time stamp repair after the server is enabled.