Deadlocks during synchronization

We use SQL Server 2000 SP4 with merge replication enabled. The master site
replicates all changes to at multiple subscribers, but we encounter some
problems due to deadlocking issues.
Some actions produce a lot of new data that must be replicated. The table,
in which the data is inserted, has a trigger defined that must be enabled for
replication. Inserting a record into this table takes 70-350ms during normal
operation. It goes through several complex calculations that have been
optimized pretty well.
When the merge agent starts replicating it often receives a deadlock when
inserting data in these tables. After this deadlock it becomes very slow. It
enlists all further records for retrying (Unable to synchronize row due to
unknown reason) and inserts that do get through have durations of over
35.000ms! Of course, this severly hurts replication performance.
I think the triggers for replication are the real probleme due to locking
issues. These calculations need to be performed even when data is replicated
and I don't know another method then using a trigger. Does anyone have a
suggestion?
Greetings,
Ramon de Klein
The replication triggers do cause increased latency of operations on
replicated tables.
Do you have real time requirements for the data that these triggers
calculate? You may want to evaluate having this calculation being performed
in a batch.
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
"Ramon de Klein" <RamondeKlein@.discussions.microsoft.com> wrote in message
news:2820F1E2-B4B2-4140-BABC-26DC753D69EB@.microsoft.com...
> We use SQL Server 2000 SP4 with merge replication enabled. The master site
> replicates all changes to at multiple subscribers, but we encounter some
> problems due to deadlocking issues.
> Some actions produce a lot of new data that must be replicated. The table,
> in which the data is inserted, has a trigger defined that must be enabled
> for
> replication. Inserting a record into this table takes 70-350ms during
> normal
> operation. It goes through several complex calculations that have been
> optimized pretty well.
> When the merge agent starts replicating it often receives a deadlock when
> inserting data in these tables. After this deadlock it becomes very slow.
> It
> enlists all further records for retrying (Unable to synchronize row due to
> unknown reason) and inserts that do get through have durations of over
> 35.000ms! Of course, this severly hurts replication performance.
> I think the triggers for replication are the real probleme due to locking
> issues. These calculations need to be performed even when data is
> replicated
> and I don't know another method then using a trigger. Does anyone have a
> suggestion?
> --
> Greetings,
> Ramon de Klein
sql

deadlocked Merge agents

I have a deadlock problems with my merge agents.
Situation:
- 1 Publisher, 3 Subscribers
- All three merge agents are stopped (to simulate a network connection
failure, so temporary no replication)
- On each database a lot of items (for example 1200) are inserted in table X
- Then all three merge agents are started at the same time (to simulate
network connection is ok again)
Result is:
All merge agents seems to be uploading changes from the subsriber to
publisher. After a while, Enterprise Manager shows for each merge agent the
message 'The agent is suspect. No response within last 10 minutes'. And it
looks like that each merge agent is hanging and doesn't do anything anymore.
When I stop two merge agents, the third is doing its job again.
Is it possible that multiple merge agents running at the same time result in
a deadlock situation? How can I solve/prevent this?
Thanks in advance,
Marco Broenink
It doesn't mean you are getting deadlocks, what it means is that procs were
fired and they haven't returned any info back to the merge agent yet.
if you get deadlocks you will get a deadlock message. This is quite normal.
If the agent fails, restart it, chances are very good it will clear this the
second time.
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
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:53566E14-0AD1-4336-B271-00462325E166@.microsoft.com...
> I have a deadlock problems with my merge agents.
> Situation:
> - 1 Publisher, 3 Subscribers
> - All three merge agents are stopped (to simulate a network connection
> failure, so temporary no replication)
> - On each database a lot of items (for example 1200) are inserted in table
X
> - Then all three merge agents are started at the same time (to simulate
> network connection is ok again)
> Result is:
> All merge agents seems to be uploading changes from the subsriber to
> publisher. After a while, Enterprise Manager shows for each merge agent
the
> message 'The agent is suspect. No response within last 10 minutes'. And it
> looks like that each merge agent is hanging and doesn't do anything
anymore.
> When I stop two merge agents, the third is doing its job again.
> Is it possible that multiple merge agents running at the same time result
in
> a deadlock situation? How can I solve/prevent this?
> Thanks in advance,
> Marco Broenink
|||thanks for the reply.
My problem is that the performance of the merge agents has become very very
bad in this situation. As soon as two merge agents have been stopped, the
third is replicating normally again.
When three merge agents are trying to replicate changes of the same table, I
guess there should be two agents being blocked and waiting for the third
doing its job. In this situation ALL three merge agents are being blocked (or
seemed to block!). This block-situation can take more then 60 minutes! So the
merge agents are not really blocking eachother, but decreasing the
performance to very bad.
thanks,
Marco
"Hilary Cotter" wrote:

> It doesn't mean you are getting deadlocks, what it means is that procs were
> fired and they haven't returned any info back to the merge agent yet.
> if you get deadlocks you will get a deadlock message. This is quite normal.
> If the agent fails, restart it, chances are very good it will clear this the
> second time.
> --
> 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
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:53566E14-0AD1-4336-B271-00462325E166@.microsoft.com...
> X
> the
> anymore.
> in
>
>

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
>
>

deadlock errors

I'm setting up a push merge replication, after the initial merge agent completes without errors I move the subscriber from a wired connection to a wireless connection. At this point I get an error…

The schema script …Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\[table]_93.sch' could not be propagated to the subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

Cannot drop the table '[table]' because it is being used for replication.

(Source: dfdmfvltgh09 (Data source); Error number: 3724)

After this error the initial merge agent kicks off again trying to apply the initial snapshot where I then get the following deadlock errors.

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

(Source: dfdmfvltgh09 (Data source); Error number: 1205)

The process could not deliver the snapshot to the Subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

All are getting set up on sql 2000 sp4 on a push.We are trying to set up 10 units that all are pointing to the same dlink router model dwl-2100AP.5 are working the other 5 are coming up with this error.Anybody have any ideas what could be causing this.I don’t mind the getting cut off due to poor network connectivity, I really want to stop the initial merge agent from kicking off after it’s already completed.

Thank you in advanced,

Pauly C

Let's tackle the snapshot failure first. Are you saying merge is trying to apply the snapshot twice, once on the wired network and once on the wireless network? Are these two networks on the same domain?

|||

Yes, the snapshot completes without errors, and replication works, changes get propergated back and forth between the subscriber and publisher, then when they move the subscriber to a wireless connection the first error occurs and the initial merge agent starts again.After applying some of the snapshot it gets the deadlock error. and it keeps trying to apply the initial merge agaent over and over.

|||And yes they are on the same domain. Somebody told me they were able to ping a machine that wasn't connected and actually got results. I did not see this with my own eyes so I'm not sure how true it is.

Deactivated?

Merge replication.
Push New Subscription.
Immediately after I click "finish" in the wizard (push new subscription),
the subscription status shows deactivated. The merge agent runs and
completes then shows status has failed. error = "the subscription to the
publication pub_name is invalid. The remote-server is not defined as a
subscription server. 14010"
Your problem is that the remote server is not enabled as a subscriber. I'm
not trying to be flippant, but this might take a little back and forth to
fix.
First off - all publications/subscriptions are marked as deactivated until
the snapshot agent runs. So this status is not problematic in your case.
To enable your subscription server as a subscriber, go to tools,
Replication, Configure Distributor, Publishers, and Subscribers, click on
the subscriber tab, locate your subscriber or register it.
You might also want to review this pdf -
http://www.nwsu.com/lowres_replication_ch02.pdf
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:%23cKdOIoAFHA.904@.TK2MSFTNGP12.phx.gbl...
> Merge replication.
> Push New Subscription.
> Immediately after I click "finish" in the wizard (push new subscription),
> the subscription status shows deactivated. The merge agent runs and
> completes then shows status has failed. error = "the subscription to the
> publication pub_name is invalid. The remote-server is not defined as a
> subscription server. 14010"
>

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS