INF: Understanding Merge Replication Conflict Detection and Resolution (315514)

SUMMARY

In order for SQL Server 2000 merge replication to allow multiple autonomous sites to participate in a replicated topology, situations exist where a change made to a particular row at one site (or replica) may conflict with a change made to the same row at another replica. In other situations, the Merge Agent cannot propagate a change made at a particular replica to another replica. These events are known as merge replication conflicts.

CONFLICT DETECTION, RESOLUTION, AND LOGGING ARCHITECTURE

How the Merge Agent Detects and Resolves Conflicts

The Merge Agent detects conflicts by using the lineage and COLV1 columns of the MSmerge_contents system table. Note that the Merge Agent only uses the COLV1 column when column-level change tracking is turned on for the merge article in question. These columns in the MSmerge_contents system table contain data about when a row or column is inserted or updated, and about which server in a merge replication topology made the corresponding version of the change. You can use the sp_showrowreplicainfo system stored procedure to view this meta data. For more information about sp_showrowreplicainfo, see SQL Server 2000 Books Online.

As the Merge Agent identifies changes that must be merged or reconciled, it compares the meta data for the rows on both the source and the destination servers. This meta data lets the Merge Agent know that the row has changed at multiple sites in the topology, which resulted in a merge replication conflict. When the Merge Agent detects a conflicting change, it invokes the resolver that is defined for the article; based on the logic in that resolver it chooses a winner. The conflict winner is the version of the row that the Merge Agent chooses as the one to keep.

Note that unless you have chosen the "interactive conflict viewer" as a conflict-resolution method for the article, conflicts are resolved automatically and immediately by the Merge Agent. If you use the merge replication Conflict Viewer to go back and manually change the winning row for a conflict, the Merge Agent applies the winning version of the row to the losing server during the next merge session.

Delayed Conflict Notification

Another salient point here is a phenomenon known as "delayed conflict notification." This occurs with global subscribers that have different conflict priorities, if typical 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. Consider the following scenario:

The publisher and a low-priority subscriber, named LowPrioritySub, exchange several sessions worth of changes without conflict.
A higher-priority subscriber, named HighPrioritySub, has not synchronized with the publisher in a while, and has made changes to the same rows that the LowPrioritySub subscriber has made.
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.
The LowPrioritySub subscriber then merges with the publisher and (seemingly) suddenly 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.

Logging Resolved Conflicts

After the Merge Agent has resolved the conflict according to logic in the article's conflict resolver, the Merge Agent then records the losing version of the row in the article's conflict table for UPDATE and INSERT conflicts, or in the MSmerge_delete_conflicts table for DELETE conflicts.

The Merge Agent creates a conflict table for each article in the publication in either the publication database (when the @centralized_conflicts parameter to the sp_addmergearticle stored procedure is set to "true"), or in the publication database and in the subscribing database (when the @centralized_conflicts parameter is set to "false"). This conflict tables has a name of the following form:

conflict_PublicationName_ArticleName

The article-based conflict tables have the same DDL structure as the articles on which they are based, and include the following additional columns:

Column Name        Description
-----------------  ------------------------------------------------------------------------

origin_datasource  The server or device at which the conflict originated.
conflict_type      The type of conflict experienced.
reason_code        A context-sensitive error code, depending on the cause of the conflict.
reason_text        A context-sensitive description of the conflict.
pubid              The ID of the publication to which the article belongs.

Instead of relying on these system tables directly, Microsoft recommends that you use the sp_helpmergeconflictrows system stored procedure or the merge replication Conflict Viewer to view INSERT and UPDATE conflicts for a particular article.

When the Merge Agent experiences a delete conflict, it logs the conflict to a separate system table named MSmerge_delete_conflicts. The MSmerge_delete_conflicts system table contains similar information to the per-article conflict tables; however, SQL Server reserves the MSmerge_delete_conflicts system for delete conflicts. Use the sp_helpmergedeleteconflictrows system stored procedure to view delete conflicts.

The Merge Agent purges rows in the conflict table if the row is older than the publication's conflict retention period. This is specified as the conflict_retention parameter to the sp_addmergepublication stored procedure. The default is 14 days.

FACTORS THAT AFFECT CONFLICT RESOLUTION

Two major factors affect how the merge reconciler will resolve a conflict it has detected:

Whether the subscriber is local or global.

-and-
Whether the article is using row or column-level tracking.

Local versus Global Subscriptions

You determine whether a subscription is going to be local or global when you create the subscription. When creating the subscription, specify either "local" or "global" for the @subscriber_type parameter to either the sp_addmergesubscription or the sp_addmergepullsubscription stored procedure. When you create global subscriptions, you must also specify a value for the @subscription_priority parameter.

The concept of subscription priority is important in understanding the difference between local subscribers and global subscribers. Subscription priority is a weighting value provided to a subscriber that the Merge Agent uses when it reconciles one replica's change with another replica's change. This weighting value gives the Merge Agent the ability to prevent a change made by a lower-priority replica from overwriting a change made by a higher-priority replica.

In the case of local subscribers, SQL Server does not assign an explicit priority value to a subscriber. Instead, SQL Server logically assigns a priority to the change when the change arrives at the publisher. At that time, the publisher takes ownership of the change, making it appear to the Merge Agent that the change actually originated at the publisher. This behavior permits the first subscriber to merge with the publisher to win all subsequent conflicts with other subscribers.

On the other hand, the Merge Agent assigns global subscribers an explicit priority value. When the Merge Agent experiences conflicting changes, it compares this priority value between the replicas to determine the winner of the conflict. By default, the change made by the subscriber whose priority value is highest wins the conflict.

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. If you are using republishing topology, subscribers to republishers must have one of the following:

A global priority value less than their relative-publisher (and less than the top-level publisher).

-or-
If they are not republishing data themselves, they must be local subscribers.

In SQL Server 2000, to configure an article in such a way that its subscribers always win conflicts with the publisher, configure the article to use Microsoft SQL Server Subscriber Always Wins Conflict Resolver.

When creating a republishing topology, topology nodes that republish data must be global subscriptions. Local subscribers are only possible for down-level leaf subscribers.

For more information about this topic and some change-resolution examples, see the "Subscriber Types and Conflicts" and "Set Subscription Priority" topics in SQL Server 2000 Books Online.

Row Versus Column-Level Change Tracking

Changing the article's conflict tracking method controls how the Merge Agent recognizes a conflicting change. This option is specified in the @column_tracking parameter to the sp_addmergearticle system stored procedure. When you use column-level tracking for an article, the Merge Agent will identify changes to the same column of the same row in an article as a conflict. On the other hand, when you use row-level tracking, the Merge Agent identifies any changes to the same row in an article as a conflict, even if the change is made to different columns in the row. Column-level tracking is the default tracking mechanism for native SQL Server articles. Note that column-level tracking is not available for SQL Server CE-based subscribers.

As an example, consider an article, named A1, with two columns, C1 and C2. With column-level tracking turned on, the Merge Agent detects an UPDATE-UPDATE conflict only when both subscribers update column C1 for the same row:

-- The following update sequence results in a column-level conflict

-- Subscriber 1
UPDATE A1 SET C1 = 'north'

-- Subscriber 2
UPDATE A1 SET C1 = 'south'

However, when row-level tracking is turned on for an article, two replicas that make a change to any column on the same row will result in a conflict:

-- The following update sequence results in a row-level conflict

-- Subscriber 1
UPDATE A1 SET C1 = 'north'

-- Subscriber 2
UPDATE A1 SET C2 = 'washington'

Be aware that business logic, or other application code, can result in column-level tracking being converted into what seems to be row-level tracking. This can occur if the application updates the same column for any update. For example, the user may only explicitly update column 2 in a table; however, the application may update another column for each user modification. For example, the application may update an auditing column. This results in the Merge Agent detecting all changes to the auditing column as conflicting. This can make it appear that changes to disparate columns are resulting in conflicts, where conflicts would typically not occur.

While business logic generally dictates the type of conflict resolution behavior you choose for your article, keep in mind that while column-level tracking requires more storage overhead at the publisher (an additional 2049 bytes for each row stored in the MSmerge_contents table), less data is sent over the network. On the other hand, row-level tracking might result in the detection of more conflicts in your topology; this sometimes increases the processing load and network traffic of the Merge Agent, but requires less storage overhead at the publisher. Also note that this option is specified on a per-article basis, which permits you to use different resolution methods in a particular publication.

Another important thing to note about conflict tracking and resolution is that no matter what kind of conflict resolution you have defined for the article, conflict resolution always occurs at the row-level. For example, with column-level change tracking turned on, the Merge Agent detects a conflict if the publisher updates column C1, and the subscriber updates columns C1 and C2 for the same row, because column C1 has been updated for the same row at two replicas. However, because the conflict is resolved at the row level, the winning version of the row entirely overwrites the losing version of the row. This means that the Merge Agent overwrites the change to column C2 made by the subscriber with the version of column C2 that exists at the publisher.

For more information about this option, see the "Row-Level Tracking and Column-Level Tracking" topic in SQL Server 2000 Books Online.

MORE INFORMATION

CONFLICT TYPES

While the majority of conflicts you experience will be restricted to a particular update at one replica conflicting with a disparate update at another replica, or possibly with an update at one replica conflicting with a delete at another replica, this is not necessarily the case. While these are the most common conflict types, they are not the only ones.

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 download processing. Conflicts during this phase of processing are referred to as download conflicts.

Update-Update Conflicts

These types of conflicts are probably the most common. In this case, an update to a row (or column) at one replica conflicts with another update to the same row somewhere else in the topology. The behavior of the default resolver in this case is to send the winning version of the row to the losing replica and log the losing row version in the article's conflict table.

Update-Delete Conflicts

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

Failed Change Conflicts

These kinds of conflicts are not immediately recognizable as conventional conflicts because there is no actual change to conflict with the source change at the destination. The Merge Agent raises these conflicts when it cannot apply a particular change (INSERT, UPDATE, or DELETE) at the destination replica. This typically occurs because of constraint definitions between the publisher and subscriber, and the use of the NOT FOR REPLICATION (NFR) property on the constraint. Here are some examples of failed change conflict situations:

A foreign key conflict at the subscriber can occur when the subscriber-side constraint is not marked as NOT FOR REPLICATION. The merge reconciler's behavior in this case is documented in the following Microsoft Knowledge Base article:
307482 PRB: Foreign Key Conflicts at Merge Subscriber Result in DELETE During Next Upload
Conflicts can occur when the publication's join filtering logic does not match its referential integrity definitions, the primary key (PK) or foreign key (FK) constraints. This can occur when the SQL Server relational engine tries to honor a certain constraint (as defined by the PK-FK relationship between the tables), but the Merge Agent is honoring the join filter definition between the articles. The Merge Agent cannot apply the change at the destination replica because of the table-level constraints, which results in a conflict.
A lookup table that serves as the primary key data for a PK-FK relationship is not published. Generally, from a performance perspective, you ought not publish lookup tables in a merge publication if their data does not frequently change. This prevents the Merge Agent from having to consider those tables in the change-enumeration phase of processing. However, the Merge Agent does detect conflicts if changes to the parent table are not replicated to the subscriber; however, replicated changes to the child table are dependent on those parent-table changes.
If constraints are not marked as NOT FOR REPLICATION, and are not defined the same at the publisher and the subscriber, the Merge Agent can detect conflicts during the application of changes. Because of the mismatched constraint definitions, the Merge Agent may not be able to apply the destination change. If you have defined constraints at your subscribers, and the Merge Agent is going to evaluate the defined constraints, make sure that they are the same as the constraints defined at the publisher.
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.
Program logic can also result in failed change conflicts if trigger logic prevents the Merge Agent from inserting a row from in the destination table. For example, a "trgUpdate" trigger is defined at the subscriber (and not marked as NFR) that performs some business logic on the incoming update. If a failure occurs, the trigger's business logic issues a ROLLBACK of the transaction, which results in the Merge Agent detecting a failed change conflict.

For all types of conflicts, the conflict_type is defined as one of ten (10) possible values. These conflict types map directly to the conflict_type in the conflict table:

Description                       conflict_type
----------------------------      -------------

Update Conflict                   1
Column Update Conflict            2
Update Delete Wins Conflict       3
Update Wins Delete Conflict       4
Upload Insert Failed              5
Download Insert Failed            6
Upload Delete Failed              7
Download Delete Failed            8
Upload Update Failed              9
Download Update Failed           10

In the case of UPDATE-UPDATE and UPDATE-DELETE conflicts, the reason_code used in the conflict table is the same as the conflict_type. However, for failed change conflicts, the reason code is the actual server-side error that prevented the Merge Agent from being able to apply the change. For example, if the Merge Agent cannot apply a publisher-initiated INSERT at the subscriber because of a primary key violation, it logs a conflict_type of 6 ("download insert failed") and a reason_code of 2627, which is the SQL Server internal error message for a primary key violation:

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.