Deadlock Handling

[Charlie]

Hi,

I have written an application that recently, under heavy load,
encountered the deadlock. After considering the time issue, I decided
to live with them. However, I have trouble reproducing them in order
to trap and handle them properly. Could someone please tell me what's
the Number it returned in the SQLException object so that my following
exception handling code would work?

Q1: Is the Number = 1025 or 10255 for deadlock?
Q2: Is 1 second a good interval for the victim to wait before retry?

While completed = false

Try
commit transaction
success = true

Catch SQLDeadlock As SQLException
If ErrorCount < RetryCount Then
For ErrorIndex = 0 To SQLDeadlock.Errors.Count
// catch deadlock, no need to roll back for it's handled by
DB
If SQLDeadLock.Errors(ErrorIndex).Number = 10255 Then
System.Threading.Thread.Sleep(1000)
LogErrorEvent(...)
Else
// catch other sql exceptions and roll back transaction
roll back transaction
LogErrorEvent(...)
End If
Next
Else
LogErrorEvent(...)
Exit While
End If

Catch ex As Exception
MsgBox(ex.tostring)

Finally
If success Then
complete = true
dispose transaction
else
ErrorCount += 1
end if
End Try
End While

Thank you for your help.

Charlie Chang
(e-mail address removed)

 

[William Ryan eMVP]

You can set the Lock_Timeout property to a given time period you are willing
to wait. If that's exceeded you will get error message 1222 back. The thing
I'd recommend having run into this situation before is running trace 1204
and finding out who's the culprit. It may be a threading issue or may be
something that you can easily resolve. Responding to it is good but
preventing it is obviously the best way to go. Another thing I'd highly
recommend is handling your transactions server side. You've got a lot more
power this way as well as control and you can ensure things happen
regardless of what happens client side. If you lost your network connection
in the middle of a client side transaction, you'd obviously have some
issues. But the same thing could well be salvageable if done on the server.
Just a thought.

HTH,

Bill
--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[Charlie]

I have been tracing the deadlocks for awhile now and I do know exactly
where the deadlocks came from. However, to prevent it requires a lot
of structure changes and presently, time is an issue for me. Anyway,
structure change and migrate all transaction logic onto server side is
on my to do list for the near future.

I will set the lock_timeout to see if it helps with my current
situation.

Thank you

Charlie Chang

 

[Charlie]

Hi,

Tried increasing lock_timeout to 10000, the deadlock still happens.

My application handles the inventory for a store. The place where
deadlock occurs is when the terminals trying to update the item
quantities. The terminals first request the quantity from the item
quantity table (first lock) and then update (second lock) the quantity
according to how much quantity has been sold during transaction. There
is also a trigger that updates the quantity to help me know the
highest quantity I ever had. (I have removed this trigger before,
however, the deadlock still happens)

For the reason of accuracy and also because my front end flow is very
fast, I do not want to put no_lock for the first select command. I do,
however, use WITH ROWLOCK for all my update command.

I have thought about setting up temporary tables for each of the
terminals then replicate them with my item quantity table. However, I
do not trust the replications that much and it's a lot of overhead
just to maintain the database.

What would be the best solution for me?

Thanks.

Charlie Chang