Re: How do you prevent Deadlocks
Date: Wed, 30 Nov 1994 17:07:26 GMT
Message-ID: <jmboivin.23.000C201C_at_cti.ulaval.ca>
In article <1994Nov4.205358.6790_at_tin.monsanto.com> djharr_at_ccmail.monsanto.com writes:
>From: djharr_at_ccmail.monsanto.com
>Subject: How do you prevent Deadlocks
>Date: Fri, 4 Nov 1994 22:56:02 GMT
>We have an on-line application that is seeing significant
>deadlocks. The DBA team does know how to capture, find and
>detect when a deadlock occurs. However, the question being
>posted here is how does one correct and prevent deadlocks
>without going to some extreme measure of single-threading users
>through the application.
>Fortunately, we do have transaction designs, and the
>transactions involved in the deadlocking are large and complex
>(40 to 50 tables involved, with 10 to 15 insertes,deletes, or
>updates throughout the transaction). In addition, each
>transaction must remain as 1 logical unit of work to insure
>business rules are preserved.
>We DO understand:
>1) What is a deadlock, and how one is created.
>2) If you single-thread the users through the application,
>deadlocks will go away. However, this is usually not very
>desirable.
>What we are having difficulty with:
>1) What is the most likely reason for deadlocks?
> Is it the application's transaction design? If so, why?
> Is it the database's design? If so, why?
> Is it both? If so, why?
> Are we missing the boat?
>2) Based on the answer from question 1, how do you change, or
>what is a methodology one can use to fix the affected "object(s)"
>in order to prevent the deadlocks?
>Thank you for your feedback!!!
>David Harrier
>Monsanto Agricultural Group
>djharr_at_ccmail.monsanto.com
I experienced VERY VERY big problems with deadlock in a application last summer. We worked hard to find WHY there was so many deadlock until somebody on this group send us a message telling us to index ALL foreign keys. We had many foreign keys not indexed. As soon as we index those keys all deadlock just disappear...
Check this point, may be it could help you.
Jean-Marc Boivin
Universite Laval
Quebec Canada
jean.marc-boivin_at_sit.ulaval.ca
Received on Wed Nov 30 1994 - 18:07:26 CET