Help with Deadlock

[Steve Thorpe]

Hi

I have a deadlock situation and I am trying to debug my Trace Log. How do I
find out what is the cause ?

I can see from the trace I have an exclusive lock on a RID, but how can I
find out what/where 'RID: 7:1:431830:13 ' is ?

Regards

Steve
:TraceLog

2003-08-20 15:15:45.28 spid4
Deadlock encountered .... Printing deadlock information
2003-08-20 15:15:45.29 spid4
2003-08-20 15:15:45.29 spid4 Wait-for graph
2003-08-20 15:15:45.29 spid4
2003-08-20 15:15:45.29 spid4 Node:1
2003-08-20 15:15:45.29 spid4 RID: 7:1:431830:13 CleanCnt:1
Mode: X Flags: 0x2
2003-08-20 15:15:45.29 spid4 Grant List 0::
2003-08-20 15:15:45.29 spid4 Owner:0x193e3400 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:52 ECID:0
2003-08-20 15:15:45.31 spid4 SPID: 52 ECID: 0 Statement Type: UPDATE
Line #: 44
2003-08-20 15:15:45.31 spid4 Input Buf: RPC Event: ams_Load_Stock;1
2003-08-20 15:15:45.31 spid4 Requested By:
2003-08-20 15:15:45.31 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:55 ECID:0 Ec:(0x1A0DF5A0) Value:0x193e1800 Cost:(0/E58)
2003-08-20 15:15:45.32 spid4
2003-08-20 15:15:45.32 spid4 Node:2
2003-08-20 15:15:45.32 spid4 RID: 7:1:431830:14 CleanCnt:1
Mode: X Flags: 0x2
2003-08-20 15:15:45.32 spid4 Grant List 1::
2003-08-20 15:15:45.32 spid4 Owner:0x193e3360 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:55 ECID:0
2003-08-20 15:15:45.32 spid4 SPID: 55 ECID: 0 Statement Type: UPDATE
Line #: 52
2003-08-20 15:15:45.34 spid4 Input Buf: Language Event: DECLARE @RC
int
DECLARE @strResult varchar(8)
DECLARE @strErrorDesc varchar(512)
EXEC @RC = [msmprim].[msm].[ams_Populate_PSM_Stockrequest] @strResult OUTPUT
, @strErrorDesc OUTPUT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: msmprim.msm
2003-08-20 15:15:45.34 spid4 Requested By:
2003-08-20 15:15:45.34 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e80 Cost:(0/94)
2003-08-20 15:15:45.35 spid4 Victim Resource Owner:
2003-08-20 15:15:45.35 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e80 Cost:(0/94)

[Erland Sommarskog]

Steve Thorpe (st***********@nospam.hotmail.com) writes:

I have a deadlock situation and I am trying to debug my Trace Log. How
do I find out what is the cause ?

I can see from the trace I have an exclusive lock on a RID, but how can I
find out what/where 'RID: 7:1:431830:13 ' is ?

In Books Online, in the Troubleshooting section, under Servers and Databases
there are a couple of topics devoted to deadlocking. There I find:

RID

Identifies the single row within a table on which a lock is held or
requested.

RID is represented in Trace Flag 1204 as RID:
db_id:file_id:page_no:row_no; for example, RID: 1:1:1253:0.

SELECT db_name(7) now gives you the name of where the deadlock occurred,
but I assumed you already knew which one. But what useful can you make
out from page_no? The undocumented command DBCC PAGE helps you out:

DBCC PAGE(7, 1, 431830, 0)

lists information about the page where the deadlock occurred. Look for
the field m_objid. Take this value and say:

SELECT object_name(@m_objid)

And know you know in which table the deadlock occurred.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp