Queued Updating Conflict Detection and Resolution
Because queued updating allows modifications to the same data at multiple locations, there may be conflicts when data is synchronized at the Publisher. Conflict detection and resolution is handled differently with queued updating than it is with merge replication. With queued updating, conflict detection and resolution is based on maintaining atomicity of the transaction. Because of this requirement, the number of conflict resolution policies that can be defined by the user is limited as compared with merge replication, which provides a more flexible framework for conflict resolution, but merge replication handles conflicts at the row level, not at the transaction level.
Microsoft® SQL Server™ 2000 detects the conflict when changes are synchronized with the Publisher. It then follows the resolution policy you selected when creating the publication.
Conflict detection and resolution can be a time-consuming and resource-intensive process, and it is best to minimize conflicts in the application by creating data partitions so that different Subscribers are modifying different subsets of data, and to prevent a user's work from being uncommitted if a conflict occurs.
Detecting Conflicts
When creating a publication and enabling queued updating, SQL Server 2000 adds a uniqueidentifier column (MSrepl_tran_version) with the default of newid() to the underlying table. When published data is changed at either the Publisher or the Subscriber, the row receives a new globally unique identifier (GUID) to indicate that a new row version exists. The Queue Reader Agent uses this column during synchronization to determine if a conflict exists.
A transaction in a queue maintains the old and new row version values. When the transaction is applied at the Publisher, the GUIDs from the transaction and the GUID in the publication are compared. If the old GUID stored in the transaction matches the GUID in the publication, the publication is updated and the row is assigned the new GUID that was generated by the Subscriber. By updating the publication with the GUID from the transaction, you have matching row versions in the publication and in the transaction.
If the old GUID stored in the transaction does not match the GUID in the publication, a conflict is detected. The new GUID in the publication indicates that two different row versions exist: one in the transaction being submitted by the Subscriber and a newer one that exists on the Publisher. In this case, another Subscriber or the Publisher updated the same row in the publication before this Subscriber transaction was synchronized.
Unlike merge replication, the use of a GUID column is not used to identify the row itself, but is used to check if the row has changed.
Resolving Conflicts
When you create a publication using queued updating, a conflict resolver instructs the Queue Reader Agent how it should handle different versions of the same row encountered during synchronization. By default, the Publisher wins conflict resolver is set. You can change the conflict resolution policy after the publication is created as long as there are no subscriptions to the publication.
The conflict resolver choices are:
- Publisher wins and the subscription is reinitialized
- Publisher wins
- Subscriber wins
These conflict resolvers maintain transactional consistency at the Subscriber to varying degrees. Reinitializing the Subscriber provides the highest degree of transactional consistency, and Subscriber wins provides the lowest degree of transactional consistency.
Conflicts are recorded and can be viewed using the Conflict Viewer. When using queued updating with snapshot replication, the conflict resolution policy is restricted to reinitializing the Subscriber or Publisher wins. The Subscriber wins conflict resolution policy is not available.
Reinitialize Subscriber
Reinitializing Subscriber to resolve conflicts maintains strict transactional consistency at the Subscriber, but it can be time consuming if the publication contains large amounts of data.
When the Queue Reader Agent detects a conflict, all remaining transactions in the queue (including the transaction in conflict) are rejected, and the Subscriber is marked for reinitialization. The next snapshot generated for the publication will be applied by the Distribution Agent to the Subscriber.
Publisher Wins
When the conflict resolution is set to Publisher wins, transactional consistency is maintained based on the data at the Publisher. The conflicting transaction is rolled back at the Subscriber that initiated it.
The Queue Reader Agent detects a conflict and compensating commands are generated and propagated to the Subscriber by posting them in the distribution database. The Distribution Agent then applies the compensating commands to the Subscriber that originated the conflicting transaction. The compensating actions update the rows on the Subscriber to match the row on the Publisher.
Until the compensating commands are applied, it is possible to read the results of a transaction that will eventually be rolled back to the Subscriber. This is equivalent to a dirty read (read uncommitted isolation level). There is no compensation for the subsequent dependent transactions that can occur. However, transaction boundaries are honored and all the actions within a transaction are either committed, or in the case of a conflict, rolled back.
Subscriber Wins
Conflict detection under the Subscriber wins policy means the last Subscriber transaction to update the Publisher wins. In this case, when a conflict is detected, the transaction sent by the Subscriber is still used and the Publisher is updated. This policy is suitable for applications where such changes do not compromise data integrity.
To set the queued updating conflict resolution policy