RE: Insert into parent table gives row exclusive locks on the child tables in 11g

Best guess based on your description would be some fix has caused this.
 
I think it would be because of the fix mentioned in
 
Bug 5909305  Change to DML lock modes for foreign key constraints

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:7625049910619345162::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,5909305.8,1,1,1,helvetica

 





Fairlie Rego
Senior Oracle Consultant
http://el-caro.blogspot.com/
M: +61 402 792 405
 

--- On Tue, 7/7/09, Reardon, Bruce (RTABBAY) <Bruce.Reardon@xxxxxxxxxxxx> wrote:


From: Reardon, Bruce (RTABBAY) <Bruce.Reardon@xxxxxxxxxxxx>
Subject: RE: Insert into parent table gives row exclusive locks on the child 
tables in 11g
To: oracle-l@xxxxxxxxxxxxx
Received: Tuesday, 7 July, 2009, 9:04 AM


I have now found
https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=223303.1
which describes that the Row-S locks on the child tables were introduced
in 9.2.0 to address problems with parallel DML hanging.

I have logged a SR with Oracle, but would also appreciate any comments
on why 11g shows Row-X locks on the child tables.
Or if others see this in 11.1.0.6 / their 11.1.0.7.

Thanks,
Bruce Reardon

-----Original Message-----
Sent: Friday, 3 July 2009 4:40 PM
To: oracle-l@xxxxxxxxxxxxx
<< paraphrased >>

We have recently upgraded from 8.1.7.4 to 11.1.0.7 on Windows 2003R2 SP2
32 bit and have experienced a block lock situation involve foreign keys.
We do not think it is due to missing indexes.

We don't yet have a full reproducer that does not rely on our full
database, but do have a cut down example that shows the exclusive locks
on the child tables.  In this case, updates to the child tables are not
blocked.

The question - why did 11g change the child locks to being row
exclusive?
This may help us understand why in the full example we get child updates
blocked.

The reproducer can be found at
http://www.freelists.org/post/oracle-l/Insert-into-parent-table-gives-ro
w-exclusive-locks-on-the-child-tables-in-11g

In 8.1.7.4, we see a TM   DML enqueue lock Row-X (SX) on the parent_tbl.
In 9.2 and 10.2 we see the addition of "TM   DML enqueue lock Row-S
(SS)" on the 2 child tables.
In 11.1.0.7, we see the lock on the child tables is now row exclusive -
"TM   DML enqueue lock Row-X (SX)".

Thanks,
Bruce Reardon

This email is confidential and may also be privileged.  If you are not the 
intended recipient, please notify us immediately and delete this message from 
your system without first printing or copying it. Any personal data in this 
email (including any attachments) must be handled in accordance with the Rio 
Tinto Group Data Protection Policy and all applicable data protection laws.
--
http://www.freelists.org/webpage/oracle-l





      
____________________________________________________________________________________
Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail

Other related posts: