Re: 9i Referential Integrity Locking

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:a8g5oo$i0i$2_at_lust.ihug.co.nz...
> Richard Kuhler wrote:
>
> > It appears that Oracle 9i no longer acquires a share level table lock on
> > a child table when rows are deleted from the parent table and no
> > supporting index exists on the child table. This was the locking
> > strategy used in earlier versions. Am I wrong?
>
> Yes. It most definitely DOES acquire the lock on the child table. What's
> new in 9i is that it then immediately releases it. So you are right that
> the locking problems have been reduced. However (as Thomas Kyte pointed
out
> to me a while ago) it still needs to acquire the lock before it can
release
> it.... and if it can't acquire the lock in the first place (because
someone
> is doing a trivial piece of DML on the child table, for example) then the
> session trying to update the parent table hangs until it can. What's
rather
> worse is that every new session that decides it wants to update the child
> table starts queueing up behind the parent-DML session... they all end up
> hanging until the first child transaction commits or rolls back.
>
> >Is there a document
> > somewhere that describes the new enforcement strategy they are using?
> > Are there different side effects caused by it?
>
> You can visit my site, download the 9i new features document from the
Books
> link, and look for the discussion on this very issue. However, it says
> basically what I've already told you, and as a result the old advice
> stands: if parent table DML on the primary key is ever likely to occur,
> slap an index on the child table's foreign key.
>
> --
> Regards
> HJR
> --------------------------------------------
> Resources for Oracle : http://www.hjrdba.com
> ============================================

As ever, the advice from Thomas and Howard is excellent. Slight digression though:
In a well designed system, how often does DML on primary keys occur? (Note: I'm *not* suggesting that it is unnecessary to index FK columns).

Regards,
Paul Received on Thu Apr 04 2002 - 12:51:38 CST