Very Computer

As the title says, I want to be able to trap for the deadlock error from
within a stored procedure.  Any suggestions?
--
Glenn Travis
DBA
Circuit City Stores, Inc.
Richmond, VA  USA

What you probably want to do is turn deadlock detection on in the
server.  Here is what Bret Halford (SYBASE) has posted to this group
in the past.

~Subject: Tracking deadlocks

If you start SQL Server with trace flags -T3605 and -T1204 (not 1205),
you will get messages in the errorlog file regarding deadlocks similar to
the one shown below.

The 1204 trace flag should only be used for debugging deadlocks in your
application as it can seriously degrade performance.

====================== errorlog file ========================
*** Deadlock detected - process 16 trying to wait on process 41
Deadlock chain -->
LOCK REQUEST INITIATING DEADLOCK:  LOGICAL:
EX_PAGE at 0xaac690
lockid=107001 spid=16 dbid=6
Process 41 waiting on Process 16 for resource: LOGICAL. Lock requested by
spid 41:
SH_PAGE at 0xac549c
lockid=106991 spid=41 dbid=6
BLOCKED by spid 16 with the following lock:
EX_PAGE Blocking at 0x20be7c0
lockid=106991 spid=16 dbid=6
    pstat=0x0100 [ ]
    VICTIM: process 41 ; pstat 0x0100 [ ] ; cputime = 26
Process 41 was selected as victim
    Waking up victim process 41 (110 : 0x0100)
=============================================================
page 106991
shared lock already hold by spid 16

exclusive lock requested by spid 41

Quote:>>>> spid 41 is waiting on spid 16 to finish

page 107001
exclusive lock requested by spid 16

Quote:>>>> it looks like that spid 41 already holds a lock
>>>> on page 107001, thus the deadlock can't be
>>>> avoided.

See the 10.0 Troubleshooting Guide 6-27 for how to identity object and index
from the page number.

Hope this helps,

Mark

--