A strange locking issue with parent-child relation

From: Amihay Gonen <Amihay.Gonen_at_ecitele.com>
Date: Wed, 18 Mar 2009 16:55:01 +0200
Message-ID: <5BB479729855824B9A8B9724F455F70EEEDB896DD5_at_ILPTMAIL02.ecitele.com>

Hi ,
When I to insert !! to a parent table and other session is lock table in exclusive mode . The session hangs.

Although , according to oracle note 33453.1 , it seems no lock is required when doing insert in parent key .


  AN INSERT/DELETE/UPDATE ON THE PARENT TABLE CAUSES THE CHILD TABLE TO GET    LOCKED. A share lock (LMODE=4) of the entire child table is required    until the transaction containing the insert/delete/update statement    for the parent table is committed, thus preventing any modifications to the    child table. It even can be a SSX (LMODE=5) lock when deleting from the    parent table with a delete cascade constraint.

NOTE: In 7.1.6 and higher, INSERT into the parent table do not lock the child table. In Oracle 9.0.1 or higher, those looks became temporal: they are only needed during the execution time of the UPDATE/DELETE statements. Those locks are downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. In 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no longer required except when deleting from a parent table with a 'delete cascade' constraint.

Here is test case

Drop table son;
Drop table a;
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;
Received on Wed Mar 18 2009 - 09:55:01 CDT

Original text of this message