Re: How do you prevent Deadlocks

In <1994Nov15.193915.2902_at_decus> y_baumert_at_decus.ch (YVONNE BAUMERT, DECUS EUROPE AD-SIG CHAIR) writes:

>
>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.
>>
>> 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?
>
>
>I would suppose that it is your transaction design but I would need to
 know
>more about your transaction concept to answer question two.
>
>
>>
>> 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!!!

Perhaps this is overly simplistic,and not knowing your transaction design it may well be, but the simplest solution would seem to be to have all applications lock tables in the same order. For example; if one application is locking ORDER_HEADER, then ORDER_DETAIL; and another does it in the reverse order, you've got the potential for a deadlock. But if All applications do it the same way, you've reduced the possibility considerably (if not eliminating it entirely).

Again, not knowing the exact design of the transactions it's hard to say and with 50 tables invloved, it may not be quite so simple. Received on Thu Nov 17 1994 - 15:14:55 CET