Q replication conflict detection
This section looks at conflict detection, what it is, when it occurs and how we deal with it.
What is conflict detection?
Let's start by defining what we mean by a conflict. A conflict occurs in bidirectional replication when the same record is processed at the same time on the two servers. We then have to decide which server is the winner, which we do when we set up the Q subscription for the table.
When do conflicts occur?
In unidirectional replication, the only time we need conflict detection is if we are updating the target table outside of Q replication, which is not recommended! Q subscription for unidirectional replication section of Chapter 5, covers scenarios where the target table is updated outside of Q replication.
There is no conflict detection with Event Publishing.
We need conflict detection in multi-directional replication. Let's first look at bidirectional replication and then move on to peer-to-peer replication.
Bidirectional replication uses data values (which we can choose) to detect and resolve conflicts. The choice of data values is determined by the CONFLICT RULE parameter we specify when we create a Q subscription. The process is that "before" values at the source server are compared against the "current" values at the target server, and based on the level of conflict detection Q Capture sends a different combination of before and/or after values to Q Apply. The CONFLICT RULE options are:
Key column values only: Q Apply attempts to update or delete the target row by checking the values in the key columns. Q Apply detects the following conflicts:
A row is not found in the target table
A row is a duplicate of a row that already exists in the target table
With this conflict rule, Q Capture sends the least amount of data to Q Apply for conflict checking. No before values are sent, only the after values for any changed columns are sent.
Key and changed column values: Q Apply attempts to update or delete the target row by checking the key columns and the columns that changed in the update. Q Apply detects the following conflicts:
A row is not found in the target table
A row is a duplicate of a row that already exists in the target table
A row is updated at both servers simultaneously and the same column values changed
If a row is updated at both servers simultaneously and the different column values changed, then there is no conflict. With this conflict rule, Q Apply merges updates that affect different columns into the same row. Because Q Apply requires the before values for changed columns for this conflict action, Q Capture sends the before values of changed columns.
All column values: Q Apply attempts to update or delete the target row by checking all columns that are in the target table. With this conflict rule, Q Capture sends the greatest amount of data to Q Apply for conflict checking.
Note
If we replicate the LOB columns, then conflicts are not detected. This is because Q replication does not replicate "before" values for LOB data types. See the Replicating large objects section for more information.
In a peer-to-peer configuration, conflict detection and resolution are managed automatically by Q Apply in a way that assures data convergence. We do not need to set anything up, as was discussed in the Peer-to-peer replication section.
The Conflict detection examples section of Appendix A walks through a couple of conflict detection examples.
