
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