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:0x193e340 0 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:LockOwn er Stype:'OR' Mode: U
SPID:55 ECID:0 Ec:(0x1A0DF5A0) Value:0x193e180 0 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:0x193e336 0 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_PS M_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:LockOwn er Stype:'OR' Mode: U
SPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e8 0 Cost:(0/94)
2003-08-20 15:15:45.35 spid4 Victim Resource Owner:
2003-08-20 15:15:45.35 spid4 ResType:LockOwn er Stype:'OR' Mode: U
SPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e8 0 Cost:(0/94)

[Erland Sommarskog]

Re: Help with Deadlock

Steve Thorpe (stephenthorpe@ nospam.hotmail. com) writes:[color=blue]
> 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 ?[/color]

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:p age_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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp