"Mladen Gogala" <mgogala_at_earthlink.net> wrote in message news:<pan.2001.11.21.01.51.35.922.3997_at_earthlink.net>...
> In article <e7ca2986.0111202037.6f6a1618_at_posting.google.com>, "Dean Taylor"
> <deantaylor31_at_hotmail.com> wrote:
>
> > I have a foreign key on table A which references table B. If change a row
> > in B but do not commit it then change the row in A which references it,
> > then the second statement will hang waiting for the first to commit or
> > rollback. That is expected. However if I then try to change a different
> > row in A it also hangs even though it should not be affected. Somone told
> > me that adding an index to the referencing rows in A would solve the
> > problem is it does not seem to. Does anyone know a way to avoid locking
> > the whole table when waiting for a foreign key check to complete?
>
> If you don't have an index on the table A, then the whole table A is
> locked in the shared mode during the updates to the table B (to ensure
> refrential integrity). You do have an index on the referencing column, so
> that is not the problem. To find out what exactly are you waiting for get
> the sid of "hanging" session and go int v$session_wait to see what
> exactly are you waiting for. You could be waiting for archiver, free
> block, ITL , a free list, any number of things. Without knowing exactly
> what event are you waiting on (and events are described in oracle
> reference for 8i and Anjo Kolk's paper for Oracle7) you are pretty much
> left wih a prayer as the only option. Oh yes, and welcome to the world of
> an Oracle DBA in his natural habitat.
Thanks Mladen, worked it out now.
BTW I did a script to create missing indexes for foreign keys which is
quite handy. I hope I didn't just duplicate an existing script that
was out there already.
Received on Fri Nov 23 2001 - 01:05:20 CST
