[thelist] [SQL 2000] Serialization failure

Steve Lewis slewis at macrovista.net
Thu Nov 21 13:56:01 CST 2002


Kyle Murphy wrote:
> I'm just curious as to what it is & how could it possibly affect our
> database....
>
> ------------------------------------------------------
> ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL
> Server Driver][SQL Server]Transaction (Process ID 54) was deadlocked on
> lock resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction.
> ------------------------------------------------------
I might take a crack at this and see if I can shed some light for someone.

WHAT:
A resource deadlock means two threads are trying to make a change to the
same thing (a table perhaps, an index perhaps, a log perhaps, it doesn't
say what) but one of them cannot resolve until the other resolves.  It
may seem hard to think of a circumstance where this can happen, but when
you add the complexity of a transaction which may fire triggers which
may call stored procedures etc... somehow a complex transaction and some
other thread of execution

Think of two men in a long narrow stairway carrying large awkward boxes.
They cannot see each other coming and their boxes crash into one
another.  Neither one can walk backwards even a step with the awkward
package, neither one can get past the other by going forward.  The
resource they both need (the next step in front of them) is not
available to either one of them because the boxes are hanging over the step.

Deadlock avoidance is the job of the thread manager.  In this case the
thread manager fails to avoid the deadlock, but does resolve it by
vaporizing one of the men, and the box he was carrying (Process ID 54).

EFFECT:
One of the transactions being attempted was never commited.  It was
rolled back.  The effects on your data must be left for you to
contemplate.  Sorry, but I don't have many suggestions here.

Hopefully you at least know which transaction was being processed when
the error occured, and with some sleuthing you may be able to determine
which process conflicted with it.  Given that, you may be able to modify
your transactions so that you can avoid the deadlock explicitly by
locking a table well before it is used in that transaction, so that the
resource is unavailable and the deadlock cannot occur again.

--Steve




More information about the thelist mailing list