Re: A strange locking issue with parent-child relation

> create table a (a number primary key);
> create table son(a number);
> create index son on son(a);
> ALTER TABLE son ADD ( CONSTRAINT son FOREIGN KEY (a) REFERENCES a (a));
> lock table son in exclusive mode;
>
> -- from other session try to insert in to A
> -- session is locked until
> commit;

It looks like different versions have different behavior. On an 8.1.7.4 database, there's indeed no lock required on the child for the second session therefore the insert goes through fine. But on 9.2.0.8, 10.2.0.4 and 11.1.0.6, the second session requires a lock on child in mode 2 (9i to 10g) or 3 (11g), so it blocks.

Note:223303.1 as pointed out by Tao Wang is clear about the behavior change. But Oracle needs to update the note about the change in 11g.

Because of this lock, having an index on the child table foreign key column is less useful now; it only makes a little difference in case of delete on parent and the delete must be cascaded.

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l