Re: Table Locking problem

> saii_at_crash.cts.com (Systems Integrated) writes:
>
> Hi,
> I'm having some unexpected trouble with locking.
> I have two tables, B and I. There is a foreign key on I that
> references the primary key on B. When this foreign key is
> added, then I cannot delete a record from B one transaction, and
> delete another record from B from another transaction. The first
> goes through, and the second hangs until the first is committed.
> The records in I do not point to the records in B that I wish to
> delete.
>
> There are many foreign key constraints on B, but only a couple
> exhibit this behavior.
>
> Any ideas on this?
>
>
> Thanks in advance for any ideas.
>
>
> Gene Plagge
> System Integrated, Inc.
> 619-277-0700
> email: gplagge_at_siwest.cts.com
>
>
>
>
>>>>
Look at pages 6-9 and 6-10 of the ORACLE7 Server Applications Developer's Guide... If you don't create an index on the foreign key, a share lock (reading allowed, writing not allowed) of the entire parent table is required until the transaction on the child table is committed. The solution is to create an index on the foreign key...

good luck,

Charles Wheeler (713) 282-1776
cwheeler_at_isc901.jsc.nasa.gov
DBA
SAIC Received on Tue Dec 19 1995 - 00:00:00 CET