A strange locking issue with parent-child relation
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;
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 18 2009 - 09:55:01 CDT