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.
Mark Hoxey 