By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Help with Deadlock

P: n/a
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)
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.