Did you know... Merge Replication Fails with Error 'Unable to synchronize the row because the row because the row was updated by a different process outside of replication.'

  • Article

Recently we have encountered a case where Merge Agent was failing to apply a transaction at the subscriber with the following error

Error Message
==============

The change for the row with article nickname 2336003 (test), rowguidcol {D46DF7AF-38C5-4B74-B07D-CF46A4E3B7EB} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.

The merge process is retrying a failed operation made to article 'test' - Reason: 'Unable to synchronize the row because the row was updated by a different process outside of replication'.

Observation
==========

When we perform an update on any row on the table for the first time the update gets replicated to the subscriber for the first time. However when we update the same rows again at the publisher, the row does not get replicated in the subscriber but instead goes into the conflict table viz test_conflict_info

We checked and confirmed that there were no changes performed on the rows at the subscriber at that point of time and hence we were sure that there is no conflict occurring at the subscriber but still the Merge Agent detects it as a conflict.

We enabled the Output Verbose Logging for the Merge Agent and found the following information

OLE DB Subscriber 'rsisql': {?=call [sp_upd_D3BFB5D242A6400627EC421E10AD49C7] (?,?,?,?,?,?,?,?,?,?,?,NULL,NULL,?,?,?,?,?,?,NULL)}

The merge process is retrying a failed operation made to article 'FOR_Restaurant_Orders_Intl' - Reason: 'Unable to synchronize the row because the row was updated by a different process outside of replication.'

Repl Agent Status: 3

OLE DB Subscriber 'rsisql': {?=call [sp_upd_D3BFB5D242A6400627EC421E10AD49C7] (?,?,?,?,?,?,?,?,?,?,?,NULL,NULL,?,?,?,?,?,?,NULL)}

OLE DB Distributor 'MIA-DBM': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

OLE DB Subscriber 'rsisql': {?=call [sp_upd_D3BFB5D242A6400627EC421E10AD49C7] (?,?,?,?,?,?,?,?,?,?,?,NULL,NULL,?,?,?,?,?,?,?)}

Percent Complete: 0

OLE DB Subscriber 'rsisql': {?=call [sp_upd_D3BFB5D242A6400627EC421E10AD49C7] (?,?,?,?,?,?,?,?,?,?,?,NULL,NULL,?,?,?,?,?,?,?)}

The merge process is retrying a failed operation made to article 'FOR_Restaurant_Orders_Intl' - Reason: 'Unable to synchronize the row because the row was updated by a different process outside of replication.'

Repl Agent Status: 3

OLE DB Subscriber 'rsisql': {?=call [sp_upd_D3BFB5D242A6400627EC421E10AD49C7] (?,?,?,?,?,?,?,?,?,?,?,NULL,NULL,?,?,?,?,?,?,?)}

CAUSE

======

For the Merge Agent to detect a conflict there should be a change in Lineage or colv column in the MSmerge_contents for a given tablenick.

In our case we were sure that there is no change in the Lineage and colv column so the only suspected change was the change in tablenick.

So we used the following query to confirm this

select objid,nickname,insert_proc from SYSMERGEARTICLES WHERE NAME ='Test'

objid nickname insert_proc

--------------- ----------------- ---------------------------------------------------------

2092026684 2336003 sp_ins_D3BFB5D242A6400627EC421E10AD49C7

2092026684 76394001 sp_ins_7B04E697254D4B2C2D2EFDB747674335

When the Merge Replication is created and Merge Agent is ran for the first time it creates these insert and update stored procedure which contains the values of the tablenick which are hardcoded in the stored procedure code.

So we checked the insert stored procedure code using the following command

SP_HELPTEXT ‘sp_ins_D3BFB5D242A6400627EC421E10AD49C7’

@tablenick=76394001

We found that stored procedure had a wrong value of the tablenick which was hardcoded and that wrong value of tablenick was nothing but the tablenick of the same article from the previous uncleaned merge replication.

So there was a previous uncleaned merge replication which was causing a false conflict detection. This caused the Merge Agent to fail.

Resolution

==========

To manually clean up the previous merge replication using the following KB article and reinitialize the subscribers

https://support.microsoft.com/kb/324401

More Information
============

In order for us to understand the cause of the issue we need to understand how Merge Agent detects conflicts

As per the BOL

Conflict Detection & Resolution
======================

Whether a data change qualifies as a conflict or not depends on the type of conflict tracking you set for an article:

· The Merge Agent detects conflicts by using the lineage column of the MSmerge_contents system table; if column-level tracking is enabled for an article, the COLV1 column is also used. These columns contain metadata about when a row or column is inserted or updated, and about which nodes in a merge replication topology made changes to the row or column. You can use the system stored procedure sp_showrowreplicainfo (Transact-SQL) to view this metadata.

· As the Merge Agent enumerates changes to be applied during synchronization, it compares the metadata for each row at the Publisher and Subscriber. The Merge Agent uses this metadata to determine if a row or column has changed at more than one node in the topology, which indicates a potential conflict. After a conflict is detected, the Merge Agent launches the conflict resolver specified for the article with a conflict and uses the resolver to determine the conflict winner. The winning row is applied at the Publisher and Subscriber, and the data from the losing row is written to a conflict table.

After the Merge Agent has resolved the conflict according to the logic in the conflict resolver, it logs conflict data according to the type of conflict:

For further information on the above you can refer to the below links,

https://support.microsoft.com/?id=315514

The above KB article is applicable for SQL 2000 but gives us a clear understanding of Conflict Detection and Resolution in the Merge Replication.

https://msdn.microsoft.com/en-us/library/ms151749(SQL.90).aspx

https://msdn.microsoft.com/en-us/library/ms151257(SQL.90).aspx

 

 

Parikshit Savjani,

SE, Microsoft SQL Server

Reviewed By Anurag Sharma & Gaurav Mathur, SQL Server Escalation Services