0

The setup:

A push transaction replication is configured for about 20 tables/articles at SQL2019 CU31 Standard. Some of the tables are permanently under a load of thousends of inserts per second. In general replication works fine except the moment when a new table is added to the replication. Initially we execute sp_addarticle to add a new article at the publisher. Then, we have to call either sp_refreshsubscriptions or sp_addsubscription, to add a subscription to this article at the subscriber.

The issue:

The stored procedure sp_refreshsubscriptions puts an SCH-M lock not only on the table that is added. It also put the lock on other tables belonged to the same publication, including the ones highly loaded with inserts. As SCH-M is not compatible with any other locks inserts start to wait for the SCH-M session while the SCH-M session itself waits for other concurrent inserts resulting in deadlocks and locks for 15..20 seconds. This is a downtime for business and unacceptable.

An alternative approach with sp_addsubscription brings no releif. Even though the new article is explicitly specified in parameters still the procedure puts SCH-M locks on all tables in the same publication.

In contrast when inserts are paused (at a Stage environment) the same operation executes instantly.

The question:

Why the locks of unrelated tables and is it possible to workaround them besides pausing inserts?

2
  • 2
    Sounds like the sort of thing you should be doing during a maintenance window. Sep 22, 2023 at 11:13
  • Of course it can be done during a maintenance window. No questions about it. I've already mentioned that if the inserts [to the existing articles] are paused, the operation [of adding of a new article] executes instantly. But the question was: Why adding of a new article to a subscription causes SCH-M locks on all existing articles in the related publication and how to workaround without pausing the inserts aka without a maintenance window? I think this locking is counterintuitive and definitely worse mentioning in the documentation. Sep 25, 2023 at 12:54

1 Answer 1

0

Transaction replication is designed to work in this way. That is how it works. If you need to add very large tables, better to create a new Publication subscription for large articles.

1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Nov 2, 2023 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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