Merge Replication Conflict Detection and Resolution

When Publisher and Subscribers are reconnected and synchronization occurs, the Merge Agent detects conflicts and then determines which data will be accepted and propagated to other sites based on a resolver specified when the merge publication was implemented.

In merge replication, a conflict exists when:

• Changes are made to the same column(s) in the same row (using INSERT, UPDATE or DELETE statements) in more than one copy, with column-level conflict tracking in effect.
  
  
• Changes are made to a row in both replicas, and row-level tracking is in effect (the columns affected in the corresponding rows need not be the same).

Note  Although a Subscriber is merging with the Publisher, a conflict typically occurs between updates made at different Subscribers and not necessarily updates made at a Subscriber and at the Publisher.

Conflict Detection

The Merge Agent detects conflicts through lineage values in the MSmerge_contents tables for the database of the article. Each entry in MSmerge_contents contains information about a row that has been updated. The lineage column in MSmerge_contents represents the history of changes in an updated row; its value is updated automatically by the Merge Agent whenever the row is synchronized.

When the Merge Agent is merging changes, it examines the lineage values of the version of the row at each site. The agent compares the lineage value for the updated row between MSmerge_contents tables (MSmerge_contents Publisher table, MSmerge_contents Subscriber table) to determine whether the row has been updated in multiple locations. If the row has not been updated in multiple locations, there is no conflict and the updated value is merged. If the row has been updated in multiple locations, a conflict has occurred, and the conflict resolution process is invoked.

If column-level tracking is enabled, the Merge Agent also needs to compare the COLV values in the MSmerge_contents table with the updated rows.

Resolving Conflicts

After a conflict is detected, the Merge Agent launches the selected conflict resolver. The winner of the conflict is chosen according to a user-specified priority scheme, a first wins solution (with the first to synchronize winning the conflict), or a custom resolver consisting of a COM object or stored procedure. Unless the interactive conflict resolver is used, conflicts are resolved immediately after the resolver executes. The losing row is written to a conflict table named conflict_<PublicationName>_<ArticleName>_usertablename (the winning row is applied at the Publisher and Subscriber).

Conflict Resolvers

Microsoft® SQL Server™ 2000 allows you to choose how to resolve merge conflicts. Options available include:

• The default priority-based conflict resolver supplied with SQL Server 2000. When using this resolver, you can assign priority values to individual Subscribers (global subscriptions), or use the default priority assignments (local subscriptions), where the Publisher takes ownership of the changes upon data synchronization. These changes then have priority over changes made at other local Subscribers on a first-merge basis.
  
  
• A custom resolver, which implements specific data or business-decision rules to resolve the conflict. Custom resolvers can be built either as stored procedures or as COM objects written in languages such as Microsoft Visual C++® or Microsoft Visual Basic®. A set of out-of-the-box custom conflict resolvers and examples of custom conflict resolvers are supplied with SQL Server 2000.
  
  
• Other Microsoft Resolvers including additive, averaging, DATETIME, maximum, merge text, minimum, and Subscriber Always Wins resolvers.

In addition, SQL Server 2000 supplies an Interactive Resolver that you can use in conjunction with either the priority-based resolver or a custom resolver. When performing an on-demand synchronization, the Interactive Resolver displays conflict data at run-time, and lets you choose which data to use to resolve the conflict. You can also use the Conflict Viewer, which has a similar user interface to the Interactive Resolver, to view the results of conflicts that have been resolved. This means that a user must be available to respond to the Interactive Resolver when a merge occurs. This would therefore not be appropriate for an application independent of human interaction.

In merge replication, conflict resolution takes place at the article level (property of an article) for a single row of data at a time. For publications composed of several articles, you can have different conflict resolvers serving different articles, or the same conflict resolver serving one article, several articles, or all the articles comprising a publication.

If you plan to use the default priority-based conflict resolver, you do not have to set the resolver property of an article. If you want to use a custom resolver instead of the default resolver, you must set the resolver property (by selecting an available custom resolver on the Publisher) for the article that will use it. Any specific information that needs to be passed to the custom resolver can also be specified in the resolver information property.

Viewing Conflicts

Replication creates several tables that can be used to review information on conflicts and their resolution. In addition, the Conflict Viewer displays conflicting rows and can be used as a conflict reviewing tool.

SQL Server 2000 creates a conflict table for each table in a merge article. For example, if there is a table named Customers that is published as an article named "Customer-Article" in the "Northwind-Customers" publication, the conflict table named conflict_Northwind-Customers_Customers-Article will be generated. 

Conflict tables have the same structure as the tables on which they are based. A row in one of these tables consists of a losing version of a conflict row (the winning version of the row residing in the actual user table). The sysmergearticles table identifies which user tables have conflict tables, and provides information about the conflict tables. SQL Server also provides stored procedures that allow the conflict tables to be queried.

Another conflict table generated during merge replication setup is MSmerge_delete_conflicts. The table is a log for deleted conflicts. It contains information for deleted rows that conflicted with an update and lost the conflict, or because a delete was undone to achieve data convergence.

Concepts necessary for understanding merge conflict resolution include:

• Row-level tracking versus column-level tracking, which specifies whether the Merge Agent identifies changes to any values in corresponding rows, or changes to the same columns in corresponding rows as a conflict.
  
  
• Subscriber type, which describes whether a user assigns a priority value to a Subscriber (global), or whether the Subscriber uses the priority value of the Publisher when the changes are synchronized (local).