Deadlock on merge agent

Hi,
I found deadlock error a week ago, and set up the 1024 traceflag as you
advised.
It caught one deadlock, but I'm really confused.
The two nodes of the deadlock are:
sp_MSmakegeneration
and one stored procedure which find a specific record by key and
update it.
This stored procedure is called in a batch process, but it will
commit the transaction after each call. And I have no any idea what is the
potential conficit with the sp_MSmakegeneration.
Please help.
Thanks
Yong
Quite often this deadlock is transient. If it is not you might want to limit
the number of concurrent merge agents.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Yong Zhang" <yongzhang@.newsgroup.nospam> wrote in message
news:eSTcrkBQGHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> I found deadlock error a week ago, and set up the 1024 traceflag as you
> advised.
> It caught one deadlock, but I'm really confused.
> The two nodes of the deadlock are:
> sp_MSmakegeneration
> and one stored procedure which find a specific record by key
> and update it.
> This stored procedure is called in a batch process, but it will
> commit the transaction after each call. And I have no any idea what is the
> potential conficit with the sp_MSmakegeneration.
> Please help.
> Thanks
> Yong
>
|||Hello,
As I know, SQL Server 2000 build 818 and later changed how the Merge Agent
locks records on the Publisher while it is synchronizing changes to the
subscriber. This new design change might cause the Merge Agent to Deadlock
with the Update from the application. We discovered we can "tune" the Merge
agent to lock a smaller number of records and hopefully avoid the
deadlocking.
The SQL Server help topic below describe how to create a new Merge Agent
Profile. In the new profile change the DownloadReadChangesPerBatch setting
from the default setting of 100 to 25. This will lock a smaller number of
records per batch while synchronizing. We believe 25 is a good compromise
but it may need to be adjusted.
See SQL Server Help Topics:
- Merge Agent Profile
- How to create a replication agent profile (Enterprise Manager)
Another the work around is to run the Merge Agent every minute instead of
continuously so when it fails with a deadlock, the Agent will automatically
restart.
Please rest assured this issue has been routed to the proper channel. If
there is any update on this, we will let you know. However, it may take
some time and we appreciate your patience.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Yong Zhang" <yongzhang@.usadiscounters.net>
>From: "Yong Zhang" <yongzhang@.newsgroup.nospam>
>Subject: Deadlock on merge agent
>Date: Sun, 5 Mar 2006 00:57:30 -0500
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eSTcrkBQGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: ip68-10-6-136.hr.hr.cox.net 68.10.6.136
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.replication:69732
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Hi,
> I found deadlock error a week ago, and set up the 1024 traceflag as
you
>advised.
> It caught one deadlock, but I'm really confused.
> The two nodes of the deadlock are:
> sp_MSmakegeneration
> and one stored procedure which find a specific record by key
and
>update it.
> This stored procedure is called in a batch process, but it will
>commit the transaction after each call. And I have no any idea what is the
>potential conficit with the sp_MSmakegeneration.
> Please help.
> Thanks
> Yong
>
>