Note: This is a curiosity question about SQL Server.

I've been reading about SQL Server's Merge Replication feature, and there's one thing I just don't understand about it: How does Replication guarantee referential integrity without locking the database while replicating?

I know how msmerge_contents and msmerge_tombstone are used to enumerate changes. Let's assume row 1 of table A is listed in msmerge_contents, and has a foreign key pointing to row 2 of table B, which is also listed in msmerge_contents.

Now, both rows have been enumerated and replication has begun. What's to stop a user from creating a new row (row 3) in table B, and pointing the foreign key from row 1 of table A to this new row?

The new row will be saved in msmerge_contents as a new generation that has not been enumerated for this session and will therefore not be replicated. However, row 1 of table A will be replicated, because it belongs to the enumerated generations - and by the time replication gets to it, it may already be updated with the value of row 3 in table B!

Does anyone know how this problem is addressed, if it is addressed?

The following TechNet article does not answer the question, sadly.

How Merge Replication Tracks and Enumerates Changes


Review How Merge Replication Detects and Resolves Conflicts

A lineage column is used which contains the generation and the node that made the change. The actual winner decision during the conflict depends upon the conflict resolver used for the article. Using the default prority-based resolver, a priority value per node should be assigned per subscriber to determine the winner for all conflicts.

With regards to referential integrity and the concept of conflict resolution in mind, the processing order with which tables (articles) are processed is your only means of guiding the conflict resolution before your child tables. You can modify the order of the article processing in the merge publication via the @processing_order parameter for sp_addmergearticle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.