RE: A strange locking issue with parent-child relation
- From: Amihay Gonen <Amihay.Gonen@xxxxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Mar 2009 17:50:27 +0200
This is application we have migrated from a db called velcois (birdstep) and we
try to keep the same logic in the old db for now.
I told oracle to lock the son table , not parent table (a) .
From: Mercadante, Thomas F (LABOR) [mailto:Thomas.Mercadante@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, March 18, 2009 5:30 PM
To: Amihay Gonen; oracle-l@xxxxxxxxxxxxx
Subject: RE: A strange locking issue with parent-child relation
Amihay,
Why are you locking the table in exclusive mode?
You told Oracle to lock the table and then you are surprised when it did it?
It looks like this is working as it should.
Tom
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Amihay Gonen
Sent: Wednesday, March 18, 2009 10:55 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Yossi Itzkovich; moti@xxxxxxxxxxx
Subject: A strange locking issue with parent-child relation
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;
Other related posts:
