RE: A strange locking issue with parent-child relation

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Wed, 18 Mar 2009 11:29:35 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA453_at_EXCNYSM0A1AJ.nysemail.nyenet>



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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amihay Gonen Sent: Wednesday, March 18, 2009 10:55 AM To: oracle-l_at_freelists.org
Cc: Yossi Itzkovich; moti_at_pcentra.com 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;  

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 18 2009 - 10:29:35 CDT

Original text of this message