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

deadlock due to transactions within a single SPID(syslockinfo table)

I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

---

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000

---

For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.

I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.

it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while

the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607

If some body could please help me it would be really helpful for me.

Thanks in advance!!

have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?

either would give alot more diagnostic info

|||

Hi, Yes this the exact situation i am facing here as well.

Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).

To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000

As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.

Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.

Is it right to say:

- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?

Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.

- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?

- Is this something Microsoft is aware of?

Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.

I have attached the error log below:

Wait-for graph

Lockeadlock Chain Deadlock Chain SPID = 112
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec0x33705528) Value:0x47908120 Cost51/B87FB18)

deadlock due to transactions within a single SPID(syslockinfo table)

I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

---

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000

---

For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.

I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.

it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while

the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607

If some body could please help me it would be really helpful for me.

Thanks in advance!!

have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?

either would give alot more diagnostic info

|||

Hi, Yes this the exact situation i am facing here as well.

Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).

To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000

As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.

Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.

Is it right to say:

- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?

Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.

- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?

- Is this something Microsoft is aware of?

Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.

I have attached the error log below:

Wait-for graph

Lockeadlock Chain Deadlock Chain SPID = 112
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec0x33705528) Value:0x47908120 Cost51/B87FB18)