SQL blocking with IOT FK parent

I was asked to make a trivial data change to a reference table; update some non-indexed, non-key columns in two rows. While the table is referenced heavily by a core application the table is not subject to change. I scripted up the necessary updates, ran the them, verified the changes and committed them. Given what I know about how Oracle handles locking and blocking I was rather surprised to see a system alert shortly thereafter that a critical process had suffered a delay in its processing.

Diagnosing the cause of the delay was trivial. A quick query against Active Session History showed the critical processes had been waiting on acquiring a row lock and the blocking session was the one that I ran the update within. This puzzled me as the critical process did not modify the table that I had updated. Furthermore, the statement that had been blocked was a MERGE statement on a different table.

As it turned out, the table I had been updating was a indexed organised table (IOT) and the table the critical process was performing the MERGE on had a foreign key to the table. Given that my update had obtained exclusive locks on referenced rows the critical process was unable to obtain the necessary shared locks on those rows and therefore waited for my update to be committed.

We can easily demonstrate this behaviour with a simple test:

CREATE TABLE parent
   (parent_id    NUMBER(10)   NOT NULL
   ,parent_name  VARCHAR2(50) NOT NULL
   ,CONSTRAINT parent_pk PRIMARY KEY (parent_id))
ORGANIZATION INDEX
/

CREATE TABLE child
   (child_id    NUMBER(10)   NOT NULL
   ,parent_id   NUMBER(10)   NOT NULL
   ,child_name  VARCHAR2(50) NOT NULL)
/

ALTER TABLE child
   ADD CONSTRAINT child_pk
   PRIMARY KEY (child_id)
/

ALTER TABLE child
   ADD CONSTRAINT child_fk1
   FOREIGN KEY (parent_id)
   REFERENCES parent (parent_id)
/

CREATE INDEX child_i1
   ON child (parent_id)
/

INSERT INTO parent
SELECT ROWNUM
,      'Parent ' || TO_CHAR(ROWNUM)
FROM   dual
CONNECT BY level <= 10
/

INSERT INTO child
SELECT ROWNUM
,      CASE WHEN MOD(ROWNUM,10)=0 THEN 10 ELSE MOD(ROWNUM,10) END
,      'Child ' || TO_CHAR(ROWNUM)
FROM   dual
CONNECT BY level <= 100
/

COMMIT
/

EXEC dbms_stats.gather_table_stats ('','parent')
EXEC dbms_stats.gather_table_stats ('','child')

We have a PARENT and a CHILD table with a foreign key relationship between them. PARENT has 10 rows and CHILD has 100 rows. In one session we perform an update on a PARENT row:

UPDATE parent
SET    parent_name = 'PARENT ' || 5
WHERE  parent_id = 5
/

and in a separate session we run a series of DML statements:

-- STATEMENT 1
-- insert using unlocked parent row
INSERT INTO child
VALUES (101,1,'CHILD 101')
/

-- STATEMENT 2
-- insert using locked parent row
INSERT INTO child
VALUES (102,5,'CHILD 101')
/

-- STATEMENT 3
-- delete using locked parent
DELETE
FROM   child
WHERE  child_id = 55
/

-- STATEMENT 4
-- update using locked parent
UPDATE child
SET    child_name = 'NEW CHILD 65'
WHERE  child_id = 65
/

-- STATEMENT 5
-- merge using unlocked parent
MERGE INTO child dest
USING  (SELECT 103         AS child_id
        ,      7           AS parent_id
        ,      'CHILD 103' AS child_name
        FROM   dual) src
ON (src.child_id = dest.child_id)
WHEN MATCHED THEN UPDATE
SET dest.child_name = src.child_name
WHEN NOT MATCHED THEN INSERT
   (child_id
   ,parent_id
   ,child_name)
VALUES
   (src.child_id
   ,src.parent_id
   ,src.child_name)
/   

-- STATEMENT 6
-- merge using locked parent
MERGE INTO child dest
USING  (SELECT 104         AS child_id
        ,      5           AS parent_id
        ,      'CHILD 104' AS child_name
        FROM   dual) src
ON (src.child_id = dest.child_id)
WHEN MATCHED THEN UPDATE
SET dest.child_name = src.child_name
WHEN NOT MATCHED THEN INSERT
   (child_id
   ,parent_id
   ,child_name)
VALUES
   (src.child_id
   ,src.parent_id
   ,src.child_name)
/   

Both STATEMENT 2, insert into CHILD referencing the locked PARENT entry, and STATEMENT 6, merge into CHILD using the locked PARENT entry, are blocked by the transaction against the PARENT table. If we were to replace the PARENT IOT with a normal heap table then we would find that no operation against the CHILD table is blocked.

So why does an IOT parent table make such a difference? I would guess that performing INSERT and MERGE Oracle needs to take out a shared lock on the parent to ensure referential integrity. We certainly would not want to perform an insert into a child table only to have the referenced parent table entry deleted by another session whilst our insert transaction is still in progress. With an IOT there is no table structure and all data is stored in the primary key index so when we updates the PARENT “table” we are actually updating the primary key index entry. Our PARENT update would have to take an exclusive lock on that and therefore denies the CHILD operations the ability to obtain a shared lock.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s