Re: 9i Referential Integrity Locking

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
============================================