How Merge Replication Detects and Resolves Conflicts

Merge replication allows multiple nodes to make autonomous data changes, so situations exist in which a change made at one node may conflict with a change made to the same data at another node. In other situations, the Merge Agent encounters an error such as a constraint violation and cannot propagate a change made at a particular node to another node. This topic describes types of conflicts, how conflicts are detected and resolved, and factors that affect detection and resolution.

Detecting and Resolving Conflicts

The Merge Agent detects conflicts by using the lineage column of the MSmerge_contents system table; if column-level tracking is enabled for an article, the COLV1 column is also used. These columns contain metadata about when a row or column is inserted or updated, and about which nodes in a merge replication topology made changes to the row or column. You can use the system stored procedure sp_showrowreplicainfo (Transact-SQL) to view this metadata.

As the Merge Agent enumerates changes to be applied during synchronization, it compares the metadata for each row at the Publisher and Subscriber. The Merge Agent uses this metadata to determine if a row or column has changed at more than one node in the topology, which indicates a potential conflict. After a conflict is detected, the Merge Agent launches the conflict resolver specified for the article with a conflict and uses the resolver to determine the conflict winner. The winning row is applied at the Publisher and Subscriber, and the data from the losing row is written to a conflict table.

Conflicts are resolved automatically and immediately by the Merge Agent unless you have chosen interactive conflict resolution for the article. For more information, see Interactive Conflict Resolution. If you manually change the winning row for a conflict using the merge replication Conflict Viewer, the Merge Agent applies the winning version of the row to the losing server during the next synchronization.

Logging Resolved Conflicts

After the Merge Agent has resolved the conflict according to the logic in the conflict resolver, it logs conflict data according to the type of conflict:

• For UPDATE and INSERT conflicts, it writes the losing version of the row to the conflict table for the article, which is named in the form conflict_<PublicationName>_<ArticleName>. The general conflict information, such as the type of conflict, is written to the table MSmerge_conflicts_info.
• For DELETE conflicts, it writes the losing version of the row to the MSmerge_conflicts_info table. When a delete loses against an update, there is no data for the losing row (because it was a delete), so nothing is written to conflict_<PublicationName>_<ArticleName>.

The conflict tables for each article are created in the publication database, subscription database, or both (the default), depending on the value specified for the @conflict_logging parameter of sp_addmergepublication. Each conflict table has the same structure as the article on which it is based, with the addition of the origin_datasource_id column. The Merge Agent deletes data from the conflict table if it is older than the conflict retention period for the publication, which is specified using the @conflict_retention parameter of sp_addmergepublication (the default is 14 days).

Replication provides the Replication Conflict Viewer and stored procedures (sp_helpmergearticleconflicts, sp_helpmergeconflictrows, and sp_helpmergedeleteconflictrows) to view conflict data. For more information, see How to: View and Resolve Data Conflicts for Merge Publications (SQL Server Management Studio) and How to: View Conflict Information for Merge Publications (Replication Transact-SQL Programming).

Factors that Affect Conflict Resolution

There are two factors that affect how the Merge Agent resolves a conflict it has detected:

• The type of subscription: client or server (whether the subscription is a pull subscription or a push subscription does not affect conflict resolution).
• The type of conflict tracking used: row-level, column-level, or logical record-level.

Subscription Types

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

Delayed conflict notification can occur with server subscriptions that have different conflict priorities. Consider the following scenario, in which non-conflicting changes are exchanged between the Publisher and a lower-priority Subscriber that result in conflicting changes when a higher-priority Subscriber synchronizes with the Publisher:

1. The Publisher and a low-priority Subscriber, named LowPrioritySub, exchange changes over several synchronizations without conflict.
2. A higher-priority Subscriber, named HighPrioritySub, has not synchronized with the Publisher in some time, and has made changes to the same rows that the LowPrioritySub Subscriber has made.
3. The HighPrioritySub Subscriber synchronizes with the Publisher and wins the conflicts between its changes and the LowPrioritySub Subscriber because it has a higher priority than the LowPrioritySub Subscriber. The Publisher now contains the changes made by the HighPrioritySub Subscriber.
4. The LowPrioritySub Subscriber then merges with the Publisher and downloads a large number of changes because of the conflicts with the HighPrioritySub Subscriber.

This situation can become problematic when the lower-priority Subscriber has made changes to the same rows that are now conflict losers. This can result in a loss of all of the changes made by this Subscriber. A potential solution to this problem is to make sure that all the Subscribers have the same priority, unless business logic dictates otherwise.

Tracking Level

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.

Conflict Types

Although the majority of conflicts are related to updates (an update at one node conflicts with an update or delete at another node), there are other conflict types. Each type of conflict discussed in this section can occur during the upload phase or the download phase of merge processing. Upload processing is the first reconciliation of changes performed in a particular merge session, and is the phase during which the Merge Agent replicates changes from the Subscriber up to the Publisher. Conflicts detected during this processing are referred to as upload conflicts. Download processing involves moving changes from the Publisher to the Subscriber, and occurs after upload processing. Conflicts during this phase of processing are referred to as download conflicts.

For more information about conflict types, see MSmerge_conflicts_info (Transact-SQL), especially the conflict_type and reason_code columns.

Update-Update 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.

Update-Delete Conflicts

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.

Failed Change Conflicts

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.

See Also

Concepts

Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
How Merge Replication Works
Advanced Merge Replication Conflict Detection and Resolution
Republishing Data

Other Resources

MSmerge_conflicts_info (Transact-SQL)
MSmerge_contents (Transact-SQL)
sp_addmergepublication (Transact-SQL)
sp_helpmergearticleconflicts (Transact-SQL)
sp_helpmergeconflictrows (Transact-SQL)
sp_helpmergedeleteconflictrows (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance