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 replication update and NOLOCK hint question

Hi all,
I have an interesting situation. We have the following scenario:
1. Server A, Database A, table A as the replication publisher
2. Server B, Database B, table B as the replication subscriber.
3. Server B, Database C
Database A was replicating an update to Server B, Database B, table
B
At the same time, a stored procedure on Server B, Database C attempted
to perform the following type of query on Server B, Database B, table
B:
insert into table B
select ...
from database D (NOLOCK)
inner join other tables all with (NOLOCK) hints
Server B, Database C's stored procedure was the deadlock victim.
My understanding of a deadlock is when two queries are competing for
the same resources and the resource with the least cost or work done
is the victim.
I would think that the NOLOCK hint would not allow Server B, Database
C's stored proc to hold resources and therefore I could see blocking
occuring with the replication update but not deadlocking since I would
think that NOLOCK would not hold on to resources.
Could the insert select with the NOLOCK have held a page lock that
caused it to hold the same resources that the update replication
statement needed and vice-versa?
Btw, I am aware of the dirty reads for the NOLOCK statements and we
use them for business purposes for our DML statements.
Any ideas would be helpful.
Thanks in advance!Hi,
I suggest you try out the tool called SQL Deadlock Detector. It monitors
your database for locks and deadlocks and provides complete information on
captured events. It tells you everything you need to know (locked objects,
blocked statements, blocking statements, etc.) to solve your
blocking/deadlock problems. The great thing about this tool is it's event
diagram which makes it exremely easy to see what exactly is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
HTH.
"techgrl" <lfischmar@.yahoo.com> wrote in message
news:1188404072.124101.68920@.k79g2000hse.googlegroups.com...
> Hi all,
> I have an interesting situation. We have the following scenario:
> 1. Server A, Database A, table A as the replication publisher
> 2. Server B, Database B, table B as the replication subscriber.
> 3. Server B, Database C
> Database A was replicating an update to Server B, Database B, table
> B
> At the same time, a stored procedure on Server B, Database C attempted
> to perform the following type of query on Server B, Database B, table
> B:
> insert into table B
> select ...
> from database D (NOLOCK)
> inner join other tables all with (NOLOCK) hints
>
> Server B, Database C's stored procedure was the deadlock victim.
> My understanding of a deadlock is when two queries are competing for
> the same resources and the resource with the least cost or work done
> is the victim.
> I would think that the NOLOCK hint would not allow Server B, Database
> C's stored proc to hold resources and therefore I could see blocking
> occuring with the replication update but not deadlocking since I would
> think that NOLOCK would not hold on to resources.
> Could the insert select with the NOLOCK have held a page lock that
> caused it to hold the same resources that the update replication
> statement needed and vice-versa?
> Btw, I am aware of the dirty reads for the NOLOCK statements and we
> use them for business purposes for our DML statements.
> Any ideas would be helpful.
> Thanks in advance!
>|||Hi,
I suggest you try out the tool called SQL Deadlock Detector. It monitors
your database for locks and deadlocks and provides complete information on
captured events. It tells you everything you need to know (locked objects,
blocked statements, blocking statements, etc.) to solve your
blocking/deadlock problems. The great thing about this tool is it's event
diagram which makes it exremely easy to see what exactly is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
HTH.
"techgrl" <lfischmar@.yahoo.com> wrote in message
news:1188404072.124101.68920@.k79g2000hse.googlegroups.com...
> Hi all,
> I have an interesting situation. We have the following scenario:
> 1. Server A, Database A, table A as the replication publisher
> 2. Server B, Database B, table B as the replication subscriber.
> 3. Server B, Database C
> Database A was replicating an update to Server B, Database B, table
> B
> At the same time, a stored procedure on Server B, Database C attempted
> to perform the following type of query on Server B, Database B, table
> B:
> insert into table B
> select ...
> from database D (NOLOCK)
> inner join other tables all with (NOLOCK) hints
>
> Server B, Database C's stored procedure was the deadlock victim.
> My understanding of a deadlock is when two queries are competing for
> the same resources and the resource with the least cost or work done
> is the victim.
> I would think that the NOLOCK hint would not allow Server B, Database
> C's stored proc to hold resources and therefore I could see blocking
> occuring with the replication update but not deadlocking since I would
> think that NOLOCK would not hold on to resources.
> Could the insert select with the NOLOCK have held a page lock that
> caused it to hold the same resources that the update replication
> statement needed and vice-versa?
> Btw, I am aware of the dirty reads for the NOLOCK statements and we
> use them for business purposes for our DML statements.
> Any ideas would be helpful.
> Thanks in advance!
>