0

While programmatically setting up Push replication, I am noticing I am deadlocking when running sp_link_publication. The deadlock seems to be related to trigger order.

Here's what I think is going wrong:

var publisherSubscriptionParams = new Dictionary<string, object>
            {
                {"publication", publisherDatabase}, {"article", "all"}, {"subscriber", subscriberServer},
                {"destination_db", subscriberDatabase}, {"subscription_type", "Push"}, {"sync_type", "replication support only"}, {"update_mode", "queued failover"}
            };

with sp_addsubscription

var publisherPushSubscriptionAgentParams = new Dictionary<string, object> { { "publication", publisherDatabase }, { "subscriber", subscriberServer }, { "subscriber_db", subscriberDatabase }, { "subscriber_security_mode", 1 } };

with sp_addpushsubscription_agent

var subscriberParams = new Dictionary<string, object>
        {
            {"publisher", publisherServer}, {"publisher_db", publisherDatabase},
            {"publication", publisherDatabase}, {"distributor", distributorServer}, {"security_mode", 1}
        };

with sp_link_publication

sp_addsubscription adds triggers like [trg_MSsync_del_...] to my database table objects. However while this is occurring I think the command sp_link_publication clashes and forms a deadlock. Does anyone have any experience/advise with this?

I can see the triggers being added via the query:

select sys.tables.name,sys.triggers.name,sys.trigger_events.type
,sys.trigger_events.type_desc, is_first,is_last
,sys.triggers.create_date,sys.triggers.modify_date
from sys.triggers inner join sys.trigger_events
on sys.trigger_events.object_id = sys.triggers.object_id
inner join sys.tables on sys.tables.object_id = sys.triggers.parent_id
order by modify_date

After all the triggers get added, if I run sp_link_publication, no deadlock.

Any ideas?

Rawle
  • 199
  • 12
  • Are you running any of these in parallel? Configuring replication isn't something that needs to be highly performant since it's more-or-less a one-time operation, so it wouldn't surprise me if they didn't consider concurrency. – Ben Thul May 16 '14 at 15:09
  • I think part of the problem is sp_addsubscription will add mssync triggers asynchronously. – Rawle May 16 '14 at 18:01
  • That could be, but I've never had any problem in doing this all through T-SQL, so I asked myself "what's different here?"… – Ben Thul May 16 '14 at 19:40
  • Could it be: sync_type = replication support only, as oppose to automatic? I need replication support only though. – Rawle May 16 '14 at 19:46
  • It shouldn't be; that option boils down to the distribution agent figuring out where in the replication stream to start delivering commands. "Replication support only" says "right now", automatic says "after applying a snapshot". – Ben Thul May 16 '14 at 19:48

0 Answers0