Error Handling: SQL Server Transaction Locks 
Author Message
 Error Handling: SQL Server Transaction Locks

This could be an atypical error received very rarely, but all too often,
from a current application I have going:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID
#8) was deadlocked with another process and has been chosen as the deadlock
victim. Rerun your transaction.

I have determined that it only occurs at high traffic times. The DB itself
is somewhat 'light' and optimized to keep locks down to a minimum.

However, what I'd like to do here is trap for this error .asp side and
immediately re-run the transaction should it occur.

Bearing in mind that my experience with error handling is sinfully shallow,
what is the best way to do something like this:

-----------
' some db connection
' some db instructions

If conn.errors.count > 0 then
    if conn.Errors(0).Number = xxx then
        ' retry
    else
        'log error
    end if
end if

--------------

or ...

-------------
' do db instruction
Do until conn.Errors(0).Number <> xxx
    redo instruction
Loop

------------

Is this even theoretically possible? My gut instinct is 'yes', but the best
way to come at it is eluding me at the moment. I'd like to log all errors,
except the transaction lock, which is a cue to simply resubmit the command
until it goes through (or resubmit x-amount of times, as a safety against
locking the system up).

Whatever advice you guys can give is valued.

~ Jen



Thu, 02 Dec 2004 22:38:01 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. SQL Server Database locks during Transactions

2. multiuser handling ( locking ) in SQL Server

3. Why to use COM+ transaction and not just SQL Server transaction

4. Why to use COM+ transaction and not just SQL Server transaction

5. SQL Server ADO Transaction Error

6. SQL Server ADO Transaction Error

7. SQL Server ADO Transaction Error

8. Distributed transaction error from SQL Server ODBC Driver

9. Get Username or Computername after lock (or deadlock) errors from MS SQL server (7 or 6.5)

10. Error handling between SQL Server and VB

11. Error Handling Between SQL Server and VB

12. SQL Server sp Error Handling & ADO

 

 
Powered by phpBB® Forum Software