Example of Merge Replication Fake Conflicts

José Moreira Neto
Microsoft Escalation Engineer

I ran into an issue where the customer was complaining that Merge Replication was generating “fake” conflicts.

This customer has a straight SQL Server Merge Replication with no filters where the only non-default setting is the use of the Microsoft Maximum Conflict Resolver a very specialized feature in Merge Conflict Resolution.  You can learn more about this specialized feature by clicking Microsoft COM-Based Resolvers.

The repro is simple; just create a simple Merge Replication with one publisher, one subscriber and a single table. Select the Microsoft Maximum Conflict Resolver instead of the default conflict resolver.

Here is the structure of my sample table:

Othertable:

Column_name    Type              

————– ——————-

ID             int                PRIMARY KEY

Desc           nchar             

ChangeSeq      int               

rowguid        uniqueidentifier  

This conflict resolver uses a single field from the underlying table (ChangeSeq in my repro) to automatically select the winner based on the row with the highest number in this selected field.  For example, the highest number may represent the person who changed the data last or site with a higher-priority setting ChangeSeq to a higher value.

From BOL:

When you have a conflict where a row is modified on different replicas, the row with the highest value in the control field will win the conflict. However if the control field has the same value coming from different replicas a conflict will exist.

This customer was complaining of false conflicts i.e. the conflict viewer was showing conflicting rows with different numbers in the CHANGESEQ field.

After investigation I found that if you defer the update of the CHANGESEQ field a conflict will happen. This occurs because Merge Replication will replicate the first batch to modify the row across to the replicas even before the control field is modified. This will cause conflicts as the rows will have the same value in the control field.

Example:

Othertable:

Column_name    Type              

————– ——————-

ID             int                PRIMARY KEY

Desc           nchar             

ChangeSeq_Id        int               

rowguid        uniqueidentifier  

— PUBLISHER or OTHER SUBSCRIBER

update [PublisherDB].[dbo].[OtherTable] set [Desc] = 2 where ID = 5 

go  — BATCH 1

update [PublisherDB].[dbo].[OtherTable] set [CHANGESEQ] = 2 where ID = 5 

go  — BATCH 2

— At this point the row ID 5 looks correct as CHANGESEQ = 2

— SUBSCRIBER

update [Subscriber1].[dbo].[OtherTable] set [Desc] = 3 where ID = 5

go  — BATCH 1

update [Subscriber1].[dbo].[OtherTable] set [CHANGESEQ] = 3 where ID = 5

go  — BATCH 2

— At this point the row ID 5 looks correct as CHANGESEQ = 3

— Synchronize now (CHANGESEQ values are different and no conflict would be expected).

— Replica1 has CHANGESEQ = 2

— Replica2 has CHANGESEQ = 3

— However merge replication will send 2 SEPARATE updates for the replicas, and the first will generate a conflict.

— Merge understands the modification above as 2 separate transactions, and at the time the first row is applied CHANGESEQ is equal on both replicas therefore generating the conflict.

The conclusion is that if an application defers the update of the control field we run into false fake conflicts.

We can also prove this by using audit triggers. See the deferred CHANGESEQ update for row 6C0D3C9E-DD39-E011-84D6-00265534C360 below:

posting by: Chris Skorlinski