Re: How do you prevent Deadlocks

In article <D056Av.331_at_lanier.com> dvick_at_lanier.com (Don Vick) writes:

>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.
 

>>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.

This is not the point. See pages 6-9 and 6-10 of the Application Developer's Guide. If there isn't an index, insertion into a child table requires a lock on the WHOLE parent table. It there is an index to locks are needed on the parent table.

It is the huge chnage in lock granularity which is significant, not the small change in spped of transaction.

ALWAYS INDEX ALL FOREIGN KEYS! Charles Jardine, University of Cambridge (UK). Received on Fri Dec 02 1994 - 12:51:06 CET