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

From: Reardon, Bruce (RTABBAY) <"Reardon,>
Date: Fri, 3 Jul 2009 16:39:34 +1000
Message-ID: <B1C87DCFE2040D41B6F46ADF9F8E4D9C01AD8BA4_at_CALBBEX01.cal.riotinto.org>

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 03 2009 - 01:39:34 CDT