RE: A strange locking issue with parent-child relation (snipped to fit in list limit)
- From: Amihay Gonen <Amihay.Gonen@xxxxxxxxxxx>
- To: "twang@xxxxxxxxxx" <twang@xxxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Mar 2009 21:01:25 +0200
The question is why ? what is the logic beyond that ?
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Wang, Tao
Sent: Wednesday, March 18, 2009 8:49 PM
To: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: A strange locking issue with parent-child relation (snipped to fit
in list limit)
When inserting into the parent table, it needs to hold a TM RS (row share
lmode 2) on the child table. RS is the the least restrictive mode of table
lock, which prevents other transactions from exclusive write access to the same
table. Since TM RS lock is incompatible with the TM X (lmode 6) , which held by
" lock table son in exclusive mode", the insert into parent statement is
blocked until the TM exclusive lock is released from child table.
I think it is an expected behavior....
Tao
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mark W. Farnham
Sent: Wednesday, March 18, 2009 1:34 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: FW: A strange locking issue with parent-child relation (snipped to fit
in list limit)
________________________________
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Wednesday, March 18, 2009 12:57 PM
To: 'Thomas.Mercadante@xxxxxxxxxxxxxxxxx'; 'Amihay.Gonen@xxxxxxxxxxx';
'oracle-l@xxxxxxxxxxxxx'
Subject: RE: A strange locking issue with parent-child relation
He is locking table "son" and inserting into table a (a is the parent). The
parent is not locked and has no foreign keys itself.
I have reproduced his test case in 11.1.0.6. An insert into table "a" cannot
jeopardize the validity of the foreign key in son, so an insert should not
require reference to "son".
(I actually used table names parent and child so I could keep things straight
in my head and because I have those tables laying around.)
Looks like a retrograde bug to me. He is not attempting to do anything to
"son", so the fact that it is locked should have nothing to do with the insert
into "a."
If I just had a brain cramp, please explain it to me, too. Regardless of
possible application design problems, Oracle shouldn't be waiting behind locks
on objects it does not have to reference.
Regards,
mwf
<snip>
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
<snip>
Hi ,
When I to insert !! to a parent table and other session is lock table in
exclusive mode . The session hangs.
<snip>
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:
