Are Deadlocks inevitable ?

There are many references to deadlock handlers that retry the
transaction automatically.

But IMO a deadlock is the result of a design flaw that should be
fixed.

My applications treat deadlocks like any other runtime error - they
are logged and fixed.

There seems to be a an opinion that deadlocks are inevitable !

This is not true - any scenario resulting in a deadlock can be cured
by adjusting the application.

I have talked with database consultants working for banks that think
deadlocks are an acceptable every day occurrence.

Now that is scary ...

re: Are Deadlocks inevitable ?

John Rivers wrote:I wouldn't go as far as claiming "any".
If you have a complex transaction with many concurrent users I think I
agree that deadlocks are inevitable.
You cannot realistically sort data access/change in a way to always
guarantee lock waits over deadlocks at a reasonable expense.
So fault toleration is the more sensible way.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Are Deadlocks inevitable ?

Serge Rielau wrote:I meant complex _application_ ...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

re: Are Deadlocks inevitable ?

An interesting idea:

to swap a deadlock-proof design for a higher performing design that
suffers deadlocks

but by accepting deadlocks you risk one client being selected as the
deadlock victim repeatedly for a long period of time ... potentially
forever!

re: Are Deadlocks inevitable ?

John Rivers (first10@btinternet.com) writes:If you have that high deadlock frequency, you better do something about it.

It is possible that every deadlock that occurs in an application could be
address. But what would the cost be? And what would the risk be that you
introduce bugs or performance bottlenecks.

If you have a bunch of background processes that deadlocks with each
other, or at least willing to become the deadlock victim, then some 20-30
deadlocks per day is not a big issue. It becomes an issue, if users get
this message slapped in the face 20-30 times a day, or if you have deadlocks
every minute. Then again, if you have deadlocks every minute, the deadlocks
are likely to be a symptom of a general performance problem.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx