Re: How do you prevent Deadlocks

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!!!
>
> David Harrier
> Monsanto Agricultural Group
> djharr_at_ccmail.monsanto.com

Yvonne Baumert       LL     TTTTTTTTTT  UU    UU   The statements made 
LTU GmbH             LL     TT  TT  TT  UU    UU   represent my own opinion 
Airport, Hangar 8    LL         TT      UU    UU   and do not necessarily 
D-40474 Düsseldorf   LL         TT      UU    UU   correspond with my 
Germany              LL         TT      UU    UU   employers opinion.
y_baumert_at_decus.ch   LLLLLL    TTTT      UUUUUU     

PSI%(0262)45211013068::baumert Some countries don't need the leading 0!!