trapping for deadlock errors within stored procedure?

trapping for deadlock errors within stored procedure?

Post by Glenn Trav » Thu, 18 May 1995 04:00:00



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


 
 
 

trapping for deadlock errors within stored procedure?

Post by Mark Abe » Sat, 20 May 1995 04:00:00


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


>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



--


 
 
 

1. Trapping Errors Raised Within Stored Procedures

Does anyone know how to retrieve error information when an error has
been raised using RAISERROR in a stored procedure?
I am performing a Data Driven Query and the Insert is achieved by
calling a stored procedure.
Under certain conditions, the SP code raises an error (which results in
the step failing as intended).
The problem is that I can't find a way of obtaining the Error Code /
Description and thus pass suitable information on to the user.

Any ideas would be gratefully received.

John Hussey.

Sent via Deja.com
http://www.deja.com/

2. Can the Transaction log file be reduced in any way?

3. Trapping and processing errors within stored procedures (SQL 7.0)

4. Get Available SQL Server with SQL Server 7 on Win2K

5. Trapping Errors within a Stored Procedure

6. IRIS: visual data exploration in WWW

7. SQL Server 2000 Query Analyzer

8. Capture deadlock error within stored procedure

9. Trapping Return Codes within a Stored Procedure.

10. Deadlocks within stored procedures

11. Error Trapping within a procedure

12. How to trap errors within Stored Procs (SQL Server 6.5 SVCPK 3)