Database Replication – Part IV

In this post I will discuss handling replication conflicts. A replication conflict can occur if a replicated record was changed since the last time it was replicated to the subscriber. This is most likely to occur when replication to the subscriber has been delayed due to a connection issue or other replication error. The default behavior with Advantage is a “last in wins” approach and the record will be updated to reflect the latest changes.

• Part I – Overview and Server to Server strategies
• Part II – Multiple server strategies
• Part III – Replication filtering
• Part IV – Replication conflicts

Let me start with a bit of background. I refer to the source database as the publisher , the target database as a subscriber and the item to be replicated as an article. Horizontal filtering limits the records through the use of a filter expression. Vertical filtering specifies the fields to be replicated. Although these strategies can apply to database replication in general some of the information I discuss is specific to Advantage Database Server. You can get an overview of Advantage replication in our online help files.

What Causes a Conflict

In order for a conflict to occur the same record must be modified by two or more people before a replication occurs. For example someone at the corporate office updates a customer contact information and that same customer’s information was updated at a branch office at the same time. In this case the record at the branch office does not contain the same data as the original record at the corporate office.

Conflicts rarely occur in an environment where a connection was always available allowing changes to be replicated almost instantly. The chance for conflicts rises when the connection is not available and many changes are waiting to be replicated. Conflicts are also likely when replication is being used by disconnected users (i.e. traveling salespeople) update information changed while disconnected. In these situations conflicts can be minimized by limiting changes on the same records.

Replication Internals

When a record is updated Advantage first computes a CRC of the original record data (NOTE: ModTime and RowID fields are not included in the checksum). If there is a horizontal filter defined it is checked to verify that the record needs to be replicated. If the record passes the filter or no filter is defined it will be added to the replication queue. A thread is then scheduled to process the replication queue.

If a CONFLICT trigger is defined on the table in the subscriber database a CRC for the target record will be computed. This value will be compared to the CRC value that was calculated on the original record by the publisher. If these values match then there is no conflict and the update is applied to the subscriber. If the values are different the conflict trigger will be executed.

Replication can use either a primary key or some subset of the searchable fields in the table to uniquely identify records at the subscriber. When conflicts are expected, the primary key should be used to identify the replicated records. Otherwise, if one or more of the searchable fields being used to identify the record have changed at the target, the record will not be found. This will cause an error which will need to be resolved before replication can continue.

Conflict Triggers

A conflict trigger behaves like an INSTEAD OF trigger, meaning the specified operation is performed rather than the normal update operation. Updates are the only events that raise a conflict trigger. Inserts and deletes occur regardless of whether the subscriber has changed the specified record.

The conflict trigger is written just like any other Advantage trigger as either an SQL script or Advantage Extended Procedure. This allows for a vast array of functionality in handling the conflict. An example conflict trigger can be found in the following Advantage tech-tip.

Regardless of how you choose to handle the conflict the changes made by the conflict trigger at the subscriber will not be reflected at the publisher. This is true even if the subscriber publishes its changes to the original publisher of the change. Since the conflict trigger runs as part of a replication event it is not pushed back to the publisher which would cause a loop condition.