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.