Re: REF column and index

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40e017e8$0$18190$afc38c87_at_news.optusnet.com.au>...
> "Kalmact" <kalmact_at_hotmail.com> wrote in message
> news:8007e332.0406280441.431784eb_at_posting.google.com...
> >It may not be desirable in case of high data volumes even though
> > you do not do any DML on the parent.
> > Also I think 9i onwards the child table is locked only for the
> > duration of the execution of the DML statement rather than the whole
> > transaction.
>
> Oh dear. Tell me you haven't bought into that hoary old myth that says the
> foreign key locking issue has gone away in 9i? It isn't true. Yes, the
> update on the parent will release the lock on the child table fairly
> immediately, *assuming it has managed to acquire it in the first place*. But
> if there is pre-existing DML on the child table, then the parent DML will
> not be able to acquire the lock it needs in the first place. It will
> therefore hang, waiting for it. And *every other piece of CHILD table DML
> will queue up behind it*. The locking issues in 9i are just as bad as they
> ever were in 8i or earlier, and it is only marketing hype that ever
> suggested otherwise.

I'm trying to deal with exactly this problem. I have a transaction which makes an insert into the parent table, and in the process acquires shared read locks on every child table that has a foreign constraint to it. Every one of those tables has an index on that foreign key so I'm not sure why this is happening. My best guess is that locks are acquired in the wrong order so that the inserting transaction can't insert because there's a read lock and the read lock won't be released till the insert completes... So I have a deadlock (which goes undetected, by the way).

Is there a workaround for this? The only things I find on this subject all imply that having the index on the foreign key column should make the problem go away.

Asya Received on Fri Jul 09 2004 - 12:34:09 CDT