Are Deadlocks inevitable ?

[John Rivers]

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 ...

[Serge Rielau]

John Rivers wrote:

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 wouldn't go as far as claiming "any".

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

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

[Serge Rielau]

Serge Rielau wrote:

John Rivers wrote:

>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 wouldn't go as far as claiming "any".

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

If you have a complex transaction with many concurrent users I think I

I meant complex _application_ ...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[John Rivers]

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!

[Erland Sommarskog]

John Rivers (fi*****@btinternet.com) writes:

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

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, es****@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