Re: How do you prevent Deadlocks
Date: Thu, 1 Dec 1994 17:08:54 GMT
Message-ID: <D056Av.331_at_lanier.com>
In article <jmboivin.23.000C201C_at_cti.ulaval.ca>,
Jean-Marc Boivin <jmboivin_at_cti.ulaval.ca> wrote:
>In article <1994Nov4.205358.6790_at_tin.monsanto.com> djharr_at_ccmail.monsanto.com writes:
>
>>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.
. . .
>>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?
>
>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...
I was about to suggest a more extreme solution, but this has the ring of truth. Transactions cannot deadlock if their executions do not overlap, and indexing the foreign keys will greatly speed up insertion of child records or deleting of parents, thus reducing the incidence of overlap.
The extreme solution is to ensure that *all* applications access shared objects in a uniform order. E.g., suppose you have 100 tables, program A uses tables 20, 40, and 50; program B uses tables 10, 15, 20, 40, and 70. A & B can deadlock because they both use tables 20 and 40. But they *cannot* deadlock if they access them in the same order. This is a theoretical result from computer science and has nothing to do with what database system you are using; it also works for any number of programs and tables. The catch is that it is at best *very dificult* and perhaps impossible to enforce. But if you can do it, you will never have another deadlock.
Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Thu Dec 01 1994 - 18:08:54 CET