Re: whole table locks when using foreign key constraint

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.

-- 
Mladen Gogala
I love the smell of napalm in the morning. It smells like victory.
Apocalypse Now