Re: Deadlock between Distribution Agent and Distribution Agent Cle
- From: "Andrew Pike" <AndrewPike@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 1 Jan 2006 07:33:01 -0800
I'm back in the office on Wednesday; I'll try to run a trace then.
However, returning to the original SQL involved in the deadlock as given
below:
select @max_xact_seqno = max(xact_seqno) from MSrepl_commands
(READPAST)
where
publisher_database_id = @publisher_database_id and
command_id = 1 and
type <> -2147483611
Assuming this is executed at the default isolation level of READ COMMITTED
(I don't have access to a server at the moment to check), the shared locks it
obtains will be released at the end of its execution. Therefore why would
long running replicated transactions/stored procedure executions have
an impact? This part of the stored procedure sp_MSget_repl_commands is
near to the top of the code and is used to find the most recent command
written by the Log Reader for replication to subscribers. This is then used
as an upper bound for further queries.
Kind Regards
Andrew Pike
--
SQL Server DBA
Accenture UK
"Hilary Cotter" wrote:
> The complexity of the procs on both sides (they can be different, they just
> have to have the same name) and the indexes in place on both sides will have
> an impact on performance, and hence the amount of time it takes to apply the
> transactions/commands/execute the stored procedures.
>
> You might want to run profiler and watch for recompiles, duration, CPU and
> IO.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Andrew Pike" <AndrewPike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:91AC92FD-A6BA-4E61-9A72-7E82F83AAEA4@xxxxxxxxxxxxxxxx
> > Our current replication topology involves a single subscriber - in fact
> > the
> > subscription database is located on the same instance as the publication
> > database.
> > All of the articles contained within the publication are stored procedure
> > executions.
> > This is a component of a 3rd party application that I cannot alter,
> > although
> > I may be
> > able to suggest improvements to the vendor.
> >
> > Kind Regards
> > Andrew Pike
> > --
> > SQL Server DBA
> > Accenture UK
> >
> >
> >
> > "Hilary Cotter" wrote:
> >
> >> The commands are shared by multiple subscribers. So when the commands are
> >> distributed to all subscribers they are marked for deletion. For a single
> >> subscriber your conclusions are accurate.
> >>
> >> Note that the delivered commands in distribution db can vary widely as
> >> some
> >> transactions affect more rows than others. Then some transactions take
> >> longer to be applied on the subscriber, especially text ones. You might
> >> want
> >> to look at replicating the execution of stored procedures. If a large
> >> part
> >> of your DML affects a large number of rows (in other words a single proc
> >> will affect more than a singleton) you can get substantial performance
> >> improvements by doing so.
> >>
> >> --
> >> Hilary Cotter
> >> Looking for a SQL Server replication book?
> >> http://www.nwsu.com/0974973602.html
> >>
> >> Looking for a FAQ on Indexing Services/SQL FTS
> >> http://www.indexserverfaq.com
> >>
> >> "Andrew Pike" <AndrewPike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:7FE114CB-2234-4053-B5D1-CBD47EBF0484@xxxxxxxxxxxxxxxx
> >> > Thanks for the speedy reply Hilary,
> >> >
> >> > The subscription in question is a named push subscription - anonymous
> >> > pull
> >> > subscriptions are not enabled.
> >> >
> >> > I ran SELECT * FROM distribution.dbo.MSdistribution_status as
> >> > requested.
> >> > There were two rows in the result set, both of which had a value of 0
> >> > for
> >> > UndelivCmdsInDistDB, while the values of DelivCmdsInDistDB were 2 and
> >> > 7.
> >> > A
> >> > couple of minutes later this query returned no rows - the Cleanup job
> >> > had
> >> > run
> >> > I presume. Would I be correct in stating that the sum of the
> >> > DelivCmdsInDistDB column will correlate with the number of commands
> >> > purged
> >> > by
> >> > the Cleanup job?
> >> > The job history of the last execution reads:
> >> >
> >> > Removed 9 replicated transactions consisting of 9 statements in 0
> >> > seconds
> >> > (0
> >> > rows/sec)
> >> >
> >> > It certainly looks that way.
> >> > These don't look to be particularly high numbers to me, although it is
> >> > just
> >> > past midnight here! The deadlock does occur at many different times
> >> > though.
> >> > The job history that relates to the last occurrence of the deadlock is
> >> > as
> >> > follows:
> >> >
> >> > Removed 31 replicated transactions consisting of 9669 statements in 2
> >> > seconds (4850 rows/sec)
> >> >
> >> > You're right about the READPAST hint, it's there to prevent blocking
> >> > caused
> >> > by the Log Reader Agent. I suppose the PAGLOCK hint has the
> >> > alternative
> >> > of
> >> > ROWLOCK, although as you suggest Microsoft have almost certainly tested
> >> > this
> >> > to the nth degree. ROWLOCK would undoubtedly incur much greater lock
> >> > resources given very large volumes of replicated commands.
> >> >
> >> > Kind Regards,
> >> > Andrew Pike
> >> > --
> >> > SQL Server DBA
> >> > Accenture UK
> >> >
> >> >
> >> >
> >> > "Hilary Cotter" wrote:
> >> >
> >> >> Do you have anonymous subscribers or named. With named subscribers the
> >> >> distribution clean up agent cleans up more aggressively and you may
> >> >> see
> >> >> problems like this when a subscriber has been offline for some time.
> >> >>
> >> >> First off issue a select * from distribution.dbo.MSdistribution_status
> >> >> to
> >> >> see how many undelivered vs delivered commands there are. If there are
> >> >> a
> >> >> high number of delivered commands, I would stop the SQL Server Agent
> >> >> and
> >> >> run
> >> >> the distribution clean up agent manually.
> >> >>
> >> >> I can't comment on why the decision was made to implement the two
> >> >> types
> >> >> of
> >> >> locks, but in general MS has done a lot of research to deliver optimal
> >> >> performance. For example the 27 in sp_MSadd_repl_commands27 comes
> >> >> from
> >> >> tests that they did to find the optimal number of commands to send to
> >> >> the
> >> >> distribution database in a batch from the log reader agent. And yes,
> >> >> they
> >> >> tested a range of commands to find which offered best performance.
> >> >>
> >> >> It looks like the readpast is to prevent locking, and the page lock is
> >> >> to
> >> >> prevent a table lock.
> >> >>
> >> >> --
> >> >> Hilary Cotter
> >> >> Looking for a SQL Server replication book?
> >> >> http://www.nwsu.com/0974973602.html
> >> >>
> >> >> Looking for a FAQ on Indexing Services/SQL FTS
> >> >> http://www.indexserverfaq.com
> >> >>
> >> >> "Andrew Pike" <AndrewPike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:CFB322D7-1D17-4064-AB36-6338A49C90A4@xxxxxxxxxxxxxxxx
> >> >> > This is occurring regularly on SQL Server 2000 build 878.
> >> >> >
> >> >> > The problem is a deadlock in the Distribution database. The
> >> >> > Distribution
> >> >> > Agent spid is executing the SELECT statement below:
> >> >> >
> >> >> > select @max_xact_seqno = max(xact_seqno) from MSrepl_commands
> >> >> > (READPAST)
> >> >> > where
> >> >> > publisher_database_id = @publisher_database_id and
> >> >> > command_id = 1 and
> >> >> > type <> -2147483611
> >> >> >
> >> >> > which is found in sp_MSget_repl_commands. It holds an Intent Shared
> >> >> > page
> >> >> > lock on a data page in the MSrepl_commands table.
> >> >> >
> >> >> > The Distribution Agent Cleanup spid is found to be running the
> >> >> > command
> >> >> > below:
> >> >> >
> >> >> > DELETE MSrepl_commands WITH (PAGLOCK) where
> >> >> > publisher_database_id = @publisher_database_id and
> >> >> > xact_seqno <= @max_xact_seqno
> >> >> >
> >> >> > located in the stored procedure sp_MSdelete_publisherdb_trans. This
> >> >> > spid
> >> >> > holds an exclusive page lock on another data page in
> >> >> > MSrepl_commands.
> >> >> >
> >> >> > Both spids then attempt to obtain the same lock type on the page
> >> >> > which
> >> >> > is
> >> >> > locked by the other.
> >> >> >
> >> >> > The Distribution Agent runs continuously and the Cleanup job is
> >> >> > scheduled
> >> >> > for every 10 minutes. The Publication, Distribution and
> >> >> > Subscription
> >> >> > databases are all on the same instance (3rd party vendor solution,
> >> >> > not
> >> >> > mine!)
> >> >> > in an active/active Win2003 cluster configuration. The articles are
> >> >> > all
> >> >> > stored procedure executions.
> >> >> >
> >> >> > Has anybody else seen this deadlock? Is it just a timing issue?
> >> >> > Why
> >> >> > is
> >> >> > the
> >> >> > PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
> >> >> > (I can't find any articles which correlate exactly to this problem)
> >> >> >
> >> >> > Kind Regards
> >> >> > Andrew Pike
> >> >> > --
> >> >> > SQL Server DBA
> >> >> > Accenture UK
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.
- Follow-Ups:
- Re: Deadlock between Distribution Agent and Distribution Agent Cle
- From: Hilary Cotter
- Re: Deadlock between Distribution Agent and Distribution Agent Cle
- From: Patrick Ikhifa
- Re: Deadlock between Distribution Agent and Distribution Agent Cle
- Prev by Date: Re: merge replication data validation
- Next by Date: Re: Adding a table, stored procedure, or view in push tranaction repl
- Previous by thread: Re: merge replication data validation
- Next by thread: Re: Deadlock between Distribution Agent and Distribution Agent Cle
- Index(es):
Relevant Pages
|
Loading
