Collision Design
In a multiple database environment, there is always the possibility of two users updating the same record simultaneously on two different databases. When creating a replicated system, consider designing the system to avoid or at least minimize collisions.
To reduce collisions significantly or eliminate them altogether, design transactions that allow newly created information to be presented in the form of inserts and deletes instead of updates. This is particularly true for updates to key columns.
For information on how to resolve collisions, see
Collision Resolution.
How Collisions Are Caused
A collision is an event that occurs when simultaneous changes are made in two different databases to rows with identical keys. The Replicator Servers detect this collision condition when the data is transmitted between the two databases.
In a collision condition, Ingres Replicator cannot synchronize the databases without destroying information.
There are five possible situations that cause collision between a source and target database. Collisions can occur during an insert, update, or a delete transaction and is detected in these instances:
• Insert transaction detects a duplicate key.
• Update transaction is attempted on a record that no longer exists.
• Update transaction is attempted on a record that does not match the original.
• Delete transaction is attempted on a record that no longer exists.
• Delete transaction is attempted on a record that does not match the original.
Note: If you use referential integrity constraints in a distributed environment, you create collision-like conflicts that cannot be resolved. For example, assume a customer record is deleted in one database while at the same time a new order for that customer is entered in a different database. If there is a referential constraint requiring orders to reference existing customers, there is an exception when the order is replicated back to the first database.
The best defense against a collision is prevention. Design your replication system to reduce the probability of collisions. Even in well-designed databases, collisions can occur during a system failure when it becomes necessary to switch between replicated databases. For this reason alone, you need to plan how to handle collisions in your replication system.
Collision Handling
Collision handling happens at the CDDS level; when defining your CDDS you must specify which collision mode you want to use. There are two ways to handle collisions, automatically or manually. Each method has advantages and disadvantages. Automatic resolution takes less time but can produce unexpected results. Manual resolution can give you more control and, in some cases, is the only way you can resolve a conflict.
All collisions are counted as errors; they cause the Replicator Server to log the error, increase the error count, and send e-mail to the mail notification list. If the collision is resolved, the Replicator Server continues processing. If the collision is not resolved, the Replicator Server behaves in accordance with the error mode setting.
Automatic Resolution of Collisions
With automatic resolution, when two records collide, one record prevails over the other. If the operation was an insert or an update, a record survives a collision by overwriting the record in the target database (the target row is deleted and the prevailing source row is inserted in its place). If the transaction was a delete, the record in the target database is deleted. If a record does not survive a collision, its replication operation (insert, update, or delete) for that target is removed from the distribution queue.
Note: Automatic resolution overwrites the entire record and can overwrite columns that have correct information with columns that do not have correct information. You must not use automatic resolution if the information contained in the losing record is important. For example, if your database contains documents that are continually updated with new information, you can lose information with automatic resolution.
Manual Resolution of Collisions
You can resolve collisions manually by editing the base table, the shadow table, and the distribution queue. For instructions, see
How You Resolve Collisions Manually. You can also resolve collisions manually using Visual DBA. For step-by-step instructions for resolving collisions manually, see the Visual DBA online help.
Collision Modes
How a Replicator Server acts when it detects a collision depends on the collision mode of the CDDS to which the colliding records belong. The Passive Detection mode involves no action on the part of the Replicator Server. Modes other than Passive Detection require the Replicator Server to search the target database to see if the row it is propagating already exists. If a Replicator Server detects a collision, its subsequent actions are determined by the collision mode and error mode assigned to the CDDS to which the colliding row belongs.
The available collision modes are:
• Passive Detection
(Default) This mode detects insert collisions and update and delete collisions where the row to be updated or deleted does not exist. The Replicator Server does not resolve the collision. The collision is an error and the Replicator Server action is dependent on the error mode setting.
This mode requires you to perform manual collision resolution.
• Active Detection
This mode detects all collisions, but does not resolve them.
Before propagating the row, the Replicator Server searches the target database to see if the row already exists. If it detects a collision, however, the Replicator Server does not resolve it. The collision is an error and the Replicator Server action is dependent on the error mode setting.
This mode requires you to perform manual collision resolution.
• Benign Resolution
This mode detects and resolves benign collisions (when the same row with the same transaction ID arrives at the same target more than once).
Before propagating the row, the Replicator Server searches the target database to see if the row already exists. If the row does exist and it came from the same transaction, the Replicator Server issues a warning message and removes the operation from the distribution queue. Otherwise, the server action is dependent on the error mode setting.
This mode requires you to perform manual collision resolution on all but benign collisions.
• Priority Resolution
This mode detects and resolves all collisions according to assigned priorities.
Before propagating the row, the Replicator Server searches the target database to see if the row already exists. If the Replicator Server detects a collision, it resolves it by comparing the priority numbers assigned to the underlying rows. The row with the highest priority number prevails. If the priorities are the same or do not exist, the row with the lower database number survives the collision.
Priority numbers are assigned in a priority collision resolution lookup table. For instructions for creating this lookup table, see chapter “Using Advanced Features.”
In this mode, all collisions are resolved automatically.
• Last Write Wins
This mode detects and resolves all collisions according to transaction timestamps.
Before propagating the row, the Replicator Server searches the target database to see if the row already exists. If the Replicator Server detects a collision, it resolves it by comparing the transaction timestamps. The row with the later timestamp prevails.
If the timestamps are identical, the row with the lower database number survives the collision.
In this mode, all collisions are resolved automatically.