Conflict Resolution

Merge replication provides an elaborate and extensible conflict resolution system. To begin with, you can specify what actually constitutes a conflict—a change made to the same article row by two different parties in the merge replication scenario, or a change made to the same article column in the same row by two different parties. If you employ column-based conflict detection, two parties in the replication scenario can change different columns in the same row without causing a conflict. If you employ row-based conflict detection, changes made by two different parties to the same row cause a conflict that must be resolved.

Merge replication detects conflicts by inspecting rowguidcol values, generations, lineage values, and, in the case of column-based detection, the colvl column in the MSmerge_contents table. We'll talk more about rowguidcol and generation values in just a moment; let's talk about the role of the lineage and colvl columns now. The lineage column exists in both the MSmerge_ contents and MSmerge_tombstone tables and is a varbinary(249). A lineage value provides a history of changes to a row and consists of publisher and subscriber nickname/version number pairs that have been involved in previous changes to the row. The colvl column tracks similar information for individual columns and is a varbinary(2048). It's used when column-based conflict detection is employed.

The system provides a default conflict resolver (the publisher wins all conflicts with a subscriber; higher-priority subscribers win conflicts with lower-priority subscribers) as well as several others you can use depending on your business needs. For example, you can stipulate that the first or last publisher/subscriber to make a change wins. You can specify that the minimum or maximum of two conflicting values wins. You can also create your own conflict resolvers either as COM objects or as stored procedures. You can list the currently installed resolvers using the sp_enumcustomresolvers stored procedure.

When a publisher and subscriber are synchronized, the changes made on the subscriber are uploaded to the publisher first, then the changes on the publisher are downloaded to the subscriber. This allows for early conflict detection since conflict resolution always occurs from the vantage point of the publisher and the default conflict resolver stipulates that the publisher wins all conflicts.

During the synchronization process, deletes are processed first, followed by inserts and updates. This means that changes recorded in the subscriber's MSmerge_tombstone table are uploaded to the publisher when the synchronization process first begins. After deletes are uploaded, inserts and updates are then applied. As I said above, these are logged in MSmerge_ contents on the publisher and subscribers.

Each time a publisher is synchronized with one of its subscribers, it effectively takes ownership of the changes made by the subscriber to the published data. If the subscriber made changes that did not conflict with those on the publisher (or if a conflict resolver is being used that allows the subscriber to win), the changes are applied to the publisher. When the publisher then synchronizes with other subscribers, these changes are applied to the subscribers provided there are no conflicts. If there are conflicts, they are resolved, and changes are made to the publisher or subscriber accordingly.

If a publisher synchronizes with a lower-priority subscriber and receives a change from it that is later reversed when the publisher synchronizes with a higher-priority subscriber, the data on the lower-priority subscriber will differ from both the publisher and the higher-priority subscriber until the Merge Agent runs to synchronize it again. This means that it's entirely possible for different versions of the same row to exist at different places in a replication scenario temporarily, even after all subscribers have been synchronized once. Eventually, all parties will end up with the same data, but it may take multiple synchronizations for that to occur.