How Replication Conflicts Occur

Tables in databases configured in a bidirectional replication scheme may be subject to replication conflicts. A replication conflict occurs when applications on bidirectionally replicated databases initiate an update, insert or delete operation on the same data item at the same time.

If no special steps are taken, each database can end up in disagreement with the last update made by the other database.

These types of replication conflicts can occur:

Update conflicts: This type of conflict occurs when concurrently running transactions at different databases make simultaneous update requests on the same row in the same table, and install different values for one or more columns.
Uniqueness conflicts: This type of conflict occurs when concurrently running transactions at different databases make simultaneous insert requests for a row in the same table that has the same primary or unique key, but different values for one or more other columns.
Delete conflicts: This type of conflict occurs when a transaction at one database deletes a row while a concurrent transaction at another database simultaneously updates or inserts the same row. Currently, TimesTen can detect delete/update conflicts, but cannot detect delete/insert conflicts. TimesTen cannot resolve either type of delete conflict.

See Reporting Conflicts for example reports generated by TimesTen upon detecting update, uniqueness, and delete conflicts.

Note:

TimesTen does not detect conflicts involving TRUNCATE TABLE statements.

Update and Insert Conflicts

Update and insert conflicts can occur under several circumstances.

Figure 12-1 shows the results from an update conflict, which would occur for the value of X under the following circumstances:

Steps	On Database A	On Database B
Initial condition	X is 1.	X is 1.
The application on each database updates X simultaneously.	Set X=2.	Set X=100.
The replication agent on each database sends its update to the other database.	Replicate X to database B.	Replicate X to database A.
Each database now has the other's update.	Replication says to set X=100.	Replication says to set X=2.

Note:

Uniqueness conflicts resulting from conflicting inserts follow a similar pattern as update conflicts, but the conflict involves the whole row.

Figure 12-1 Update Conflict

Description of "Figure 12-1 Update Conflict"

If update or insert conflicts remain unchecked, the master and subscriber databases fall out of synchronization with each other. It may be difficult or even impossible to determine which database is correct.

With update conflicts, it is possible for a transaction to update many data items but have a conflict on a few of them. Most of the transaction's effects survive the conflict, with only a few being overwritten by replication. If you decide to ignore such conflicts, the transactional consistency of the application data is compromised.

If an update conflict occurs, and if the updated columns for each version of the row are different, then the non-primary key fields for the row may diverge between the replicated tables.

Note:

Within a single database, update conflicts are prevented by the locking protocol: only one transaction at a time can update a specific row in the database. However, update conflicts can occur in replicated systems due to the ability of each database to operate independently.

TimesTen Classic replication uses timestamp-based conflict resolution to cope with simultaneous updates or inserts. Through the use of timestamp-based conflict resolution, you may be able to keep the replicated databases synchronized and transactionally consistent.

Delete/Update Conflicts

Figure 12-2 shows the results from a delete/update conflict, which would occur for Row 4 under the following circumstances:

Steps	On Database A	On Database B
Initial condition.	Row 4 exists.	Row 4 exists.
The applications issue a conflicting update and delete on Row 4 simultaneously.	Update Row 4.	Delete Row 4.
The replication agent on each database sends the delete or update to the other.	Replicate update to database B.	Replicate delete to database A.
Each database now has the delete or update from the other database.	Replication says to delete Row 4.	Replication says to update Row 4.

Figure 12-2 Delete/Update Conflict

Description of "Figure 12-2 Delete/Update Conflict"

Although TimesTen can detect and report delete/update conflicts, it cannot resolve them. Under these circumstances, the master and subscriber databases fall out of synchronization with each other.

Although TimesTen cannot ensure synchronization between databases following such a conflict, it does ensure that the most recent transaction is applied to each database. If the timestamp for the delete is more recent than that for the update, the row is deleted on each database. If the timestamp for the update is more recent than that for the delete, the row is updated on the local database. However, because the row was deleted on the other database, the replicated update is discarded. See Reporting Delete/Update Conflicts.

Note:

There is an exception to this behavior when timestamp comparison is enabled on a table using UPDATE BY USER. See Enabling User Timestamp Column Maintenance.