How Merge Replication Detects and Resolves Conflicts

When you create a subscription, in addition to specifying whether it is a push or pull subscription, you specify whether it is a client or server subscription; after a subscription is created, the type cannot be changed (in previous versions of Microsoft SQL Server, client and server subscriptions were referred to, respectively, as local and global subscriptions).

A subscription with an assigned priority value (from 0.00 to 99.99) is called a server subscription; a subscription using the priority value of the Publisher is called a client subscription. Additionally, Subscribers with server subscriptions can republish data to other Subscribers. The following table summarizes the main differences and uses of each Subscriber type.

If a row is changed in a client subscription, no priority is assigned to the change until the subscription is synchronized. During synchronization, the changes from the Subscriber are assigned the priority of the Publisher and retain that priority for subsequent synchronizations. In a sense, the Publisher assumes ownership of the change. This behavior permits the first Subscriber to synchronize with the Publisher to win subsequent conflicts with other Subscribers for a given row or column.

When you change a row in a server subscription, the subscription priority is stored in the metadata for the change. This priority value travels with the changed row as it merges with changes at other Subscribers. This assures that a change made by a higher priority subscription does not lose to a subsequent change made by a subscription with a lower priority.

A subscription cannot have an explicit priority value that is higher than its Publisher. The top-level Publisher in a merge replication topology always has an explicit priority value of 100.00. All subscriptions to that publication must have a priority value less than this value. In a republishing topology:

• If the Subscriber is republishing data, the subscription must be a server subscription with a priority value less than the Publisher above the Subscriber.

• If the Subscriber is not republishing data (because it is at the leaf-level of the republishing tree), the subscription must be a client subscription.

For more information about server subscriptions and priorities, see Example of Merge Conflict Resolution Based on Subscription Type and Assigned Priorities.

Delayed Conflict Notification

Whether or not a data change qualifies as a conflict depends on the type of conflict tracking you set for an article: row-level, column-level, or logical record-level. For more information on logical record-level tracking, see Detecting and Resolving Conflicts in Logical Records.

When conflicts are recognized at the row level, changes made to corresponding rows are judged a conflict, whether or not the changes are made to the same column. For example, suppose one change is made to the address column of a Publisher row, and a second change is made to the phone number column of the corresponding Subscriber row (in the same table). With row-level tracking, a conflict is detected because changes were made to the same row. With column-level tracking, no conflict is detected, because changes were made to different columns in the same row.

For row-level and column-level tracking, resolution of the conflict is the same: the entire row of data is overwritten by data from the conflict winner (for logical record-level tracking, resolution depends on the article property logical_record_level_conflict_resolution).

The application semantics usually determine which tracking option to use. For example, if you are updating customer data that is generally entered at the same time, such as an address and phone number, row-level tracking should be chosen. If column-level tracking were chosen in this situation, changes to the customer address in one location and to the customer phone number in another location would not be detected as a conflict: the data would be merged on synchronization and the error would be missed. In other situations, updating individual columns from different sites may be the most logical choice. For example, two sites may have access to different types of statistical information on a customer, such as income level and total dollar amount of credit card purchases. Selecting column-level tracking ensures that both sites can enter the statistical data for different columns without generating unnecessary conflicts.

The Merge Agent detects update-update conflicts when an update to a row (or column, or logical record) at one node conflicts with another update to the same row at another node. The behavior of the default resolver in this case is to send the winning version of the row to the losing node and log the losing row version in the article conflict table.

The Merge Agent detects update-delete conflicts when an update of data at one node conflicts with a delete at another. In this case, the Merge Agent updates a row; however, when the Merge Agent searches for that row at the destination, it cannot find the row because it has been deleted. If the winner is the node that updated the row, the delete at the losing node is discarded and the Merge Agent sends the newly updated row to the conflict loser. The Merge Agent logs information about the losing version of the row to the MSmerge_conflicts_info table.

The Merge Agent raises these conflicts when it cannot apply a particular change. This typically occurs because of a difference in constraint definitions between the Publisher and Subscriber, and the use of the NOT FOR REPLICATION (NFR) property on the constraint. Examples include:

• A foreign key conflict at the Subscriber, which can occur when the Subscriber-side constraint is not marked as NFR.

• Differences in constraints between the Publisher and Subscribers, and the constraints are not marked as NFR.

• Unavailability of dependent objects at the Subscriber. For example, if you publish a view, but not the table on which that view depends, a failure will occur if you attempt to insert through that view at the Subscriber.

• Join filtering logic for a publication that does not match the primary key and foreign key constraints. Conflicts can occur when the SQL Server relational engine tries to honor a constraint but the Merge Agent is honoring the join filter definition between the articles. The Merge Agent cannot apply the change at the destination node because of the table-level constraints, which results in a conflict.

• Conflicts because of unique index or unique constraint violations or primary key violations can occur if identity columns are defined for the article, and automated identity management is not used. This can be a problem if two Subscribers were to use the same identity value for a newly inserted row. For more information about identity range management, see Replicating Identity Columns.

• Conflicts due to trigger logic preventing the Merge Agent from inserting a row in the destination table. Consider an update trigger that is defined at the Subscriber; the trigger is not marked as NFR and includes a ROLLBACK in its logic. If a failure occurs, the trigger issues a ROLLBACK of the transaction, which results in the Merge Agent detecting a failed change conflict.