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