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:
ID int PRIMARY KEY
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.
Microsoft SQL Server Maximum Conflict Resolver
Name of the column to be used to determine the conflict winner. It must have an arithmetic data type (such as int, smallint, numeric, and so on).
Column with the larger numeric value determines the conflict winner. If one is set to NULL, the row containing the other is the winner.
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.
-- 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
update [Subscriber1].[dbo].[OtherTable] set [Desc] = 3 where ID = 5
update [Subscriber1].[dbo].[OtherTable] set [CHANGESEQ] = 3 where ID = 5
-- 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