Merge Replication Conflict Detection v/s Conflict Resolution

One common misconception about how merge replication handles column-level tracking (detection) is that when a conflict is encountered, the winning row will include column values which did not conflict in addition to the "winning columns".

For example:

Originally Row100= col1=a, col2=b, col3=c, col4=d, col5=e

User1 updates Row100: col1=f,    col2=g,    col3=h    -- note: col4 and col5 are unchanged

User2 updates Row100: col4=i       -- note: col1, col2, col5 are unchanged

If we have column-level tracking enabled, we will not detect the above as a conflict when updating Row100, because the same column was not changed. With row-level tracking the above would be raised as a conflict.

Now, let us simulate a column-level tracking conflict:

Originally Row100= col1=a, col2=b, col3=c, col4=d, col5=e

User1 updates Row100: col1=f,    col2=g,    col3=h,    -- note: col4 and col5 are unchanged

User2 updates Row100: col3=y, col4=z   -- note: col1, col2,col5 are unchanged

Even with column-level tracking enabled, we will detect the above as a conflict when updating Row100, because conflicting changes were made to col3. If User2 is the winner, then at the end of the merge, both users will have the following values for Row100:

col1=a, col2=b, col3=y, col4=z, col5=e

Note that although, col1,col2 did not cause a conflict the end result is the changes made to col1,col2 by user1 were lost.

Similarly, if User1 is the winner, then at the end of the merge, both users will have the following values for Row100:

col1=f, col2=g, col3=h, col4=d, col5=e

This is because for row-level and column-level conflict tracking, resolution of the conflict is the same: the entire row of data is overwritten by data from the conflict winner.

To customize the behavior of how conflicts are resolved, you can write one of the following types of custom resolvers:

A business logic handler

The business logic handler framework allows you to write a managed code assembly that is called during the merge synchronization process. The assembly includes business logic that can respond to conflicts and a number of other conditions during synchronization.

A COM-based custom resolver

Merge replication provides an API for writing resolvers as COM objects in languages such as Microsoft Visual C++ or Microsoft Visual Basic.

In a later post, I will step through a sample custom resolver which will use sp_showrowreplicainfo to check each column version to determine which columns were changed. This way, we can choose to keep the changes to the columns which did not conflict.

References:

https://msdn.microsoft.com/en-us/library/ms151749.aspx

https://msdn.microsoft.com/en-us/library/ms151257.aspx

-   Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services

Comments

• Anonymous
  March 11, 2011
  I unfortunately fell into the "common misconception" camp, and now I'm starting to see what I might be able to do about actually merging the conflicting rows so that all non-conflicting column changes are preserved, and I'm fine if the "first to server" determines the winner for all the other values. Any chance you (Fany Carolina Vargas) ever did the follow-up post that you hinted at above in which you were going to "step through a sample custom resolver which will use  sp_showrowreplicainfo to check each column version to determine which columns were changed. This way, we can choose to keep the changes to the columns which did not conflict"?

• Anonymous
  April 09, 2011
  FYI, the followup post can be found here blogs.msdn.com/.../merge-replication-conflict-detection-vs-conflict-resolution-part-2-custom-business-logic-handler.aspx