RE: Deadlock on merge agent

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@xxxxxxxxxxxxxxxxxx>
From: "Yong Zhang" <yongzhang@xxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxxx>
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!tk2msftngp13.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




.

Relevant Pages

  • Re: Data reader / Serverside cursor
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... When the first few rows are found, the agent tells the client that there is a resultset available to process. ... To "lock down" the rows, you're going to need a Transaction with a "repeatable read" option set. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Merge Agent is reporting deadlocks
    ... Agent locks records on the Publisher while it is synchronizing changes to ... Deadlock with the Update from the application. ... >>>Disconnecting from Publisher 'SQLBDG01PA' ...
    (microsoft.public.sqlserver.replication)
  • Re: replication deadlocked
    ... its likely the clean up agent deadlocking with the log reader ... Stop the clean up agent to see if it clears the problem. ... Also on sql 2005 you can use the deadlock trace or this (The text, ...
    (microsoft.public.sqlserver.replication)
  • Re: Agent Deadlock Errors
    ... contributes to your deadlock problems. ... queued agent to run on a staggered schedule to the distribution clean up, ... > subscribers-with queued updating as failover. ...
    (microsoft.public.sqlserver.replication)
  • Re: Agent Deadlock Errors
    ... Changed the History Cleanup Agent to run every 10 minutes, ... Changed the Distribution Cleanup Agent to run every 5 minutes, ... Changed the Queued Reader Agent to run every 5 minutes, ... I'm still getting the random deadlock failure of the Distribution Cleanup ...
    (microsoft.public.sqlserver.replication)
Loading