Code Level for Deadlock Detection

I've got a VB app that calls an SQL stored procedure that calls
another SQL stored procedure that updates multiple tables. I want
deadlock detection and recovery. Where can it be done?
The inherited code attempts to check for error 1205 inside the inner
sql stored procedure - an error code check after every update
statement. Will that work? I thought the deadlock victim would kill
the entire stored procedure, not just the blocked update statement.
If the inherited code is wrong, will it work to put the deadlock test
in the outer stored procedure? Will the deadlock victim kill only the
inner stored procedure and return error code 1205 to the outer stored
procedure? Or does it kill both the inner and outer stored
procedures?The transaction is rolled back and the entire back is aborted when you are
chosen as the deadlock victim
(http://www.sommarskog.se/error-handling-I.html). If you want to retry the
operation, you'll need to handle the error in your VB code.
Hope this helps.
Dan Guzman
SQL Server MVP
"William Lipp" <w.b.(MyLastNameHere)@.ieee.org> wrote in message
news:t6sv32d0vahsjme8olhjc90l7dnqu1a9gj@.
4ax.com...
> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?|||Try testing it.
A deadlock will terminate the connection so there's nothing that can be done
in the stored procedures.
Put the retry in the VB app.
I use an interface class to access the database so it's easy to put code
like this in all calls.
see
http://www.nigelrivett.net/VB/VB6DataAccessLayer.html
"William Lipp" wrote:

> I've got a VB app that calls an SQL stored procedure that calls
> another SQL stored procedure that updates multiple tables. I want
> deadlock detection and recovery. Where can it be done?
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
> If the inherited code is wrong, will it work to put the deadlock test
> in the outer stored procedure? Will the deadlock victim kill only the
> inner stored procedure and return error code 1205 to the outer stored
> procedure? Or does it kill both the inner and outer stored
> procedures?
>|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> The inherited code attempts to check for error 1205 inside the inner
> sql stored procedure - an error code check after every update
> statement. Will that work? I thought the deadlock victim would kill
> the entire stored procedure, not just the blocked update statement.
Up to SQL 2000 this is correct.
In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
procedure. Since you say "error check after every update statement",
it does not sound like TRY-CATCH, and thus that code is not really
working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sat, 15 Apr 2006 12:23:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>William Lipp writes:
> Erland Sommarskog writes
>In SQL 2005 you can use TRY-CATCH to trap deadlock errors in a stored
>procedure. Since you say "error check after every update statement",
>it does not sound like TRY-CATCH, and thus that code is not really
>working.
Yes, the existing code just checks @.@.error after the insert - I was
pretty sure that wasn't working. I had hoped that the outer stored
procedure could that method to detect deadlocks within the inner
stored procedure. I believe these responses say that won't work
either - that a deadlock victim in the inner stored procedure will
also kill the outer stored procedure, and the only place I can detect
the deadlock victim and reattempt is from the VB code that called the
outer procedure. Have I got that right?|||William Lipp (w.b.(MyLastNameHere)@.ieee.org) writes:
> Yes, the existing code just checks @.@.error after the insert - I was
> pretty sure that wasn't working. I had hoped that the outer stored
> procedure could that method to detect deadlocks within the inner
> stored procedure. I believe these responses say that won't work
> either - that a deadlock victim in the inner stored procedure will
> also kill the outer stored procedure, and the only place I can detect
> the deadlock victim and reattempt is from the VB code that called the
> outer procedure. Have I got that right?
Yes, for SQL 2000 your assumption is entirely accurate. (Well, there is a
trick you can play with linked servers, but it's fairly obscure.)
For SQL 2005, as I said, it is possible to catch the error in a stored
procedure with the new TRY-CATCH construct.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx