1

I have transactional replication setup. Let's say three databases on server A replicate to corresponding three databases on Server B. The databases are identical (same structure for different clients). Server B also acts as a distributor. Everything is working fine. Now I need to change one of the tables (which is also a publication article). I need to do it in all databases at the same time. I follow this process (it is scripted):

  1. Drop article for the table I need to modify from the publication
  2. Make the change to my table (it has to be dropped and re-created)
  3. Add article back
  4. Generate snapshot (it possible to have partial snapshot for just the modified article, see good explanation here)

This is where I run into deadlocks. The deadlock takes place in the distribution database when this stored procedure is executed: sp_MSdrop_subscription. I don't call this procedure directly, but apparently it gets executed when I run sp_dropsubscription (see step #1 above). Once again, I modify all publication databases at the same time and they all use the same distribution database, so this is where the problem lies. The two conflicting statements are captured by the deadlock trace (1222) and apparently come from inside sp_MSdrop_subscription.

First connection runs:

if not exists (select * from dbo.MSsubscriptions    where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or @publication_id is NULL or @independent_agent = 0 ) and
independent_agent = @independent_agent and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
subscription_type = @subscription_type)

Second connection runs:

delete from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or
@publication_id is NULL) and
(@article_id is NULL or
article_id = @article_id) and
Message
((subscriber_id = @subscriber_id and
(subscriber_db = @subscriber_db or @subscriber_id = @virtual)) or
-- Delete virtual anonymous subscription
-- if deleting virtual subscription for a anonymous publication
(@subscriber_id = @virtual and subscriber_id = @virtual_anonymous))

and here is the resource list of what's being locked:

resource-list

keylock hobtid=72057594039959552 dbid=34 objectname=distribution.dbo.MSsubscriptions indexname=ucMSsubscriptions id=lock13563b300 mode=X associatedObjectId=72057594039959552

owner-list
owner id=process2d7241498 mode=X
waiter-list
waiter id=process212b58188 mode=S requestType=wait

keylock hobtid=72057594040025088 dbid=34 objectname=distribution.dbo.MSsubscriptions indexname=iMSsubscriptions id=lock17ff3af80 mode=X associatedObjectId=72057594040025088

owner-list
owner id=process212b58188 mode=X
waiter-list
waiter id=process2d7241498 mode=U requestType=wait

I appreciate any pointers on how to avoid this. The only work-around I can think of right now is to have separate distribution databases per publication database, but I can't believe that replication doesn't support modifying multiple publishers at the same time.

Thank you!

2 Answers 2

2

It has been quite a while since I personally used replication, so I am not current on practical problems. A publisher with multiple subscribers is pretty common. However, Microsoft suggests using multiple Distribution databases when you have multiple Publishers. The following post has direction:

http://technet.microsoft.com/en-us/library/ms183524.aspx

"However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct."

It sounds like a recommendation to me.

1
  • Thank you for your suggestion. I have actually looked into this as a way to avoid the deadlock situation described above. In reality we host a large number of customer databases on our servers and unfortunately it is not possible to have a distributor per publication database, only a distributor per publishing server instance. Once again, the problem happens when making changes to the multiple publishing databases, which share a distributor. I still appreciate the feedback.
    – SQL_Guy
    Mar 31, 2014 at 0:20
1

I just wanted to share what I did to work-around this problem in case someone else runs into it. Once again, the fundamental issue is caused by simultaneous changes being made to multiple publications, which share the same distribution database. After analyzing the code of the Microsoft queries, which are reported by the deadlock trace (see my original post above), I found out that MSsubscriptions table is missing an index, so I went ahead an added it:

    CREATE NONCLUSTERED INDEX [iMSsubscriptions3] ON [dbo].[MSsubscriptions]
(
    [publisher_id] ASC,
    [publisher_db] ASC,
    [publication_id] ASC,
    [independent_agent] ASC,
    [article_id] ASC,
    [subscriber_id] ASC,
    [subscriber_db] ASC,
    [subscription_type] ASC,
    [publisher_database_id] ASC
)

You can check the execution plan of the queries and verify that this index is actually being leveraged.

The index helps, but since I have to make changes to dozens of publications at once, it still was not enough. So the second part of the fix was to simply introduce a random delay. I added up to 3 min delay to my deployment script and this scattered the queries enough to significantly lower probability of deadlocks (but not eliminate it completely :( Something like this:

DECLARE     @Delay VARCHAR(8),
    @Delay_Sec VARCHAR(2),
    @Dealay_Min VARCHAR(2)

SET @Delay_Sec = RIGHT('0' + CAST(1 + FLOOR(59 * RAND(CONVERT(varbinary, NEWID()))) AS VARCHAR(2)), 2) -- 1 to 59
SET @Dealay_Min = RIGHT('0' + CAST(0 + FLOOR(3 * RAND(CONVERT(varbinary, NEWID()))) AS VARCHAR(2)), 2) -- 0 to 2

SET  @Delay = '00:' + @Dealay_Min + ':' + @Delay_Sec
WAITFOR DELAY @Delay

I hope this helps somebody at some point.

1
  • Thank you for sharing your approach to getting this to mostly work.
    – RLF
    Apr 8, 2014 at 18:42

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.