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

  • From: "Reardon, Bruce (RTABBAY)" <Bruce.Reardon@xxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 3 Jul 2009 16:39:34 +1000

We have recently upgraded from 8.1.7.4 to 11.1.0.7 on Windows 2003R2 SP2
32 bit.

Since the upgrade, we have experienced a blocking lock scenario due to
an insert into a parent table blocking updates on rows in the child
table.
Forms 6i is being used, and is currently updating all fields in the
child table, including the FK.
Modifying to update only changed fields does remove the problem for that
particular form, but we are trying to understand more about what is
happening.

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:

----------------------------------------------------------
CREATE TABLE PARENT_TBL (  PK_COL                  NUMBER(10)
NOT NULL )  ;

CREATE UNIQUE INDEX PARENT_TBL_PK ON PARENT_TBL (PK_COL);

ALTER TABLE PARENT_TBL ADD (
  CONSTRAINT PARENT_TBL_PK 
  PRIMARY KEY (PK_COL));
----------------------------------------------------------
CREATE TABLE CHILD1_TBL (
  PK_COL                  NUMBER(10)            NOT NULL,
  NON_PK_COL              VARCHAR2(1 )      NOT NULL,
  FK_PARENT_COL           NUMBER(10)            NOT NULL );

ALTER TABLE CHILD1_TBL ADD (
  CONSTRAINT PARENT1_FK 
  FOREIGN KEY (FK_PARENT_COL) 
  REFERENCES PARENT_TBL (PK_COL));
 
CREATE INDEX PARENT_CHILD1_FK_I ON CHILD1_TBL (FK_PARENT_COL) ;
----------------------------------------------------------
CREATE TABLE CHILD2_TBL(
  PK_COL                  NUMBER(10)            NOT NULL,
  NON_PK_COL              VARCHAR2(1 )      NOT NULL,
  FK_PARENT_COL           NUMBER(10)            NOT NULL);

ALTER TABLE CHILD2_TBL ADD (
  CONSTRAINT PARENT2_FK 
  FOREIGN KEY (FK_PARENT_COL) 
  REFERENCES PARENT_TBL (PK_COL));

CREATE INDEX PARENT_CHILD2_FK_I ON CHILD2_TBL (FK_PARENT_COL);
----------------------------------------------------------
INSERT INTO parent_tbl (pk_col ) VALUES (1);


Then look at the locks - say via the script at
http://jkstill.blogspot.com/2009/04/querying-vlock.html.

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.
--
//www.freelists.org/webpage/oracle-l


Other related posts: