When running a refresh on tables that have a high number of records, in some cases it is possible to get primary key violation errors during the process. Normally this condition shouldn’t happen if the primary key settings between source and target table match (and the source doesn’t allow duplicate values). However, in cases where it does, there are some ways to prevent the issue from occurring.
1. Enable Transactions
If the replication is using bulk insert and specifically the Array Binding type of Bulk Insert, not enabling transactions may cause the following behavior:
- A bulk block is being processed.
- Some records of the block are correctly inserted, some are not (for any reason). The bulk insert process attempts to run a Rollback of the whole block and switches temporarily to Single Insert to try inserting records singularly.
- Since transactions are not enabled, the Rollback cannot be invoked and therefore the status of the block is that some of the records are committed to the target, some are not.
- When switching to Single Insert, the process attempts to re-insert all records of the block, even the ones that were previously committed in the block operation.
- Result: duplicate key errors are generated.
To enable transactions in the replication, open the Replication Properties dialog, Advanced tab and set the Refresh Isolation Level to something other than Unspecified:
Depending on the database, some Isolation Level values may or may not be supported. Check the User's Guide from the Help menu in the Management Center on how to set the Refresh Isolation Level.
2. Use an ORDER BY clause
To process the refresh, Syniti DR/DBMoto opens a read-only cursor on the source table and reads through the records without applying any lock. This factor is important, as the refresh may take a long time and the entire table or portions of it cannot be locked indefinitely. As a consequence, if database users are running operations on the table’s data while the refresh is running, the cursor could be reflecting these changes while it completes the refresh operation.
In a few verified cases, for instance, if an UPDATE is executed on a record that has already been returned by the cursor, the same record can be fetched again during the select with the updated values. When this happens:
- The cursor reads the record the first time and the record is replicated to the target
- A user runs an update on the record
- The cursor reads the same record with the updated values and attempts to insert it to the target
- Result: duplicate key errors are generated.
To overcome this limitation, a property called Order By Clause Source has been introduced. By setting an ORDER BY clause on the source table, the condition does not occur. Note that using an ORDER BY clause may have consequences on the performance of the reading task, because records are being ordered before being fetched.
To enable this property, open the Replication Properties dialog, Advanced tab and edit the Order By Clause Source property by setting a list of columns to use (likely, the primary key columns of the source table, in the order of definition of the primary key constraint):
NOTE: this property was introduced in version 9.0.7. It is still possible to apply an ORDER BY clause to previous versions of DBMoto, by using the Refresh Filter Source property. In this case, since the Refresh Filter expects a WHERE condition, use a fake filter, such as “1 > 0” followed by the order by clause:
0 ORDER BY ID1, ID2, ID3
Make sure to set Skip Record Count to true as well (otherwise the ORDER BY would be used in the record count statement generating a syntax error).
In both situations analyzed here – transactions not set or record ordering issue – the result of the replication is always correct and the data are synchronized, even if errors are shown in the log. Eventually, changes applied during refresh will be processed by Syniti DR/DBMoto in the mirroring phase. The recommendations shown here prevent the replication from getting errors in the log that could mislead the user and suggest a possible problem.