Re: 9i Referential Integrity Locking

In article <_6Kq8.25970$VQ2.8478861_at_twister.socal.rr.com>, Richard says...
>
>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? Is there a document
>somewhere that describes the new enforcement strategy they are using?
>Are there different side effects caused by it?
>
>
>thanks,
>Richard Kuhler
>

not 100% correct.

the duration of the lock is significantly reduced but still there.

the lock is taken exactly as before but only during the execution of the statement -- NOT the transaction. So the lock is released much quicker. However if you have something like:

session 1: insert into child
session 2: delete from parent (and child is unindexed on the fkey) -- this will

             BLOCK on session 1, cannot lock child session 3: insert into child, this will block on session 2 which is waiting

             for the table lock
session 4: does same as 3 (and so on --- other sessions will wait for child) session 1: commits -- this frees session 2 -- it gets its lock, does the delete

             and releases it. sessions 3, 4, etc are freed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp