Sql-server – How does SQL Server Merge Replication maintain referential integrity

merge-replicationreplicationsql server

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

Best Answer

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

Related Solutions

Sql-server – New foreign key and merge replication

So it did pretty much what it is described in Make Schema Changes on Publication Databases:

If a publication is set to allow the propagation of schema changes, schema changes are propagated regardless of how the related schema option is set for an article in the publication. For example, if you select not to replicate foreign key constraints for a table article, but then issue an ALTER TABLE command that adds a foreign key to the table at the Publisher, the foreign key is added to the table at the Subscriber. To prevent this, disable the propagation of schema changes before issuing the ALTER TABLE command.

To boot, your exact case is give as an example :) Also, the solution is given there: disable schema changes replication before issuing the ALTER TABLE.

As to why it behaves this way, I would say that a system that would make 'smart' decision what schema changes to replicate and what not would be not only very hard to design, but also very error prone and would cause a lot of surprises and break the principle of least astonishment a lot of times. The gist of it is the relations between objects that are published and objects that are not are often ambiguous or hard to detect (think why is the subject of dependency tracking so vast...). And even when detected, the semantics of a change that affects both a published article and a non-published object are ambiguous to say the least: allow the change only locally? Block the change? I can assure you that whichever answer you venture forward, someone will complain it breaks his case... because the answer is dependent on specific soft business rules, not hard language rules.

Sql-server – Deadlock on Merge replication

This lock compatibility matrix can shed some light. Note if granted mode is S (shared read), transaction requesting for U (can update) will be granted access. Also note same is true other way, i.e. U is existing lock and request for S is granted. X (Exclusive access )is not compatible with neither S not U. Also U is not compatible with U

i.e.

Existing Requested Status
S U Yes
U S Yes
U U No
S X No
U X No

In your two different transactions first acquire S or U on same resource. Then some time down the line they want to convert the current lock to more exclusive lock. Here is one possible order which can result in above deadlock.

T1 : Request S
SQL : Granted.
T2 : Request U
SQL : Granted (because U is compatible with S)
T1 : Convert from S to U. (lock escalation)
SQL : Wait. T2 is holding U.
T2 : Convert from U to X (lock escalation)
SQL : Wait. T1 is holding S. (DEADLOCK!!)

To resolve this deadlock don't do conversion of lock. Try to get most exclusive lock first. i.e T1 should straight go for U and T2 should go for X

In your case

<owner id="process8f31f9498" mode="S"/>
<owner id="process8f31f9498" mode="U" requestType="convert"/>

Where as

<owner id="process87f1e4188" mode="U"/>
<owner id="process87f1e4188" mode="X" requestType="convert"/>

Let me know if you have any questions.

Best Answer

Related Solutions

Sql-server – New foreign key and merge replication

Sql-server – Deadlock on Merge replication

Related Question