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)