How do you prevent Deadlocks

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 Received on Fri Nov 04 1994 - 23:56:02 CET