Anyone who has used Oracle for a while will be familiar with the Parent/Child locking "issue" when it comes to tables and indexes on foreign keys. For many years you’d hear people crying "bug" etc but thankfully most now know the reason, and accept it as sensible behaviour.
But lets take a look at a slight variation on that theme.
Lets start with a table called "LOC" which will be our parent table in this example. Note that it is an IOT, and we’ll also have a child table "LOC_CHILD", which is also an IOT.
SQL> CREATE TABLE LOC 2 ( 3 LOC_ID NUMBER(4) NOT NULL, 4 DATA NUMBER(6), 5 CONSTRAINT LOC_PK 6 PRIMARY KEY 7 ( LOC_ID) 8 ENABLE VALIDATE 9 ) 10 ORGANIZATION INDEX 11 / Table created. SQL> CREATE TABLE LOC_CHILD 2 ( 3 CHILD_SEQ NUMBER(12) NOT NULL, 4 LOC_ID NUMBER(4) NOT NULL, 5 CHILD_DATA NUMBER(15,6), 6 CONSTRAINT LOC_CHILD_PK 7 PRIMARY KEY 8 (CHILD_SEQ, LOC_ID) 9 ENABLE VALIDATE 10 ) 11 ORGANIZATION INDEX 12 / Table created. SQL> insert into LOC 2 select rownum,50 3 from dual 4 connect by level <= 5 5 / 5 rows created.
Now being a good DBA🙂 we’ve read all the "gloom and doom" nonsense about foreign keys being indexed, so just to be careful, we’ll add that index onto our child table before adding our foreign key back to LOC.
SQL> CREATE INDEX LOC_CHILD_IX ON LOC_CHILD 2 (LOC_ID) 3 / Index created. SQL> ALTER TABLE LOC_CHILD ADD ( 2 CONSTRAINT LOC_CHILD_FK 3 FOREIGN KEY ( LOC_ID) 4 REFERENCES LOC (LOC_ID) 5 ENABLE VALIDATE) 6 / Table altered. SQL> insert into LOC_CHILD 2 select rownum,mod(rownum,5)+1,dbms_random.value(1000,5000) 3 from dual 4 connect by level <= 2000 5 / 2000 rows created. SQL> commit; Commit complete.
So the scene is set..we’ve got our tables seeded with some data, and ready to go..
Lets update a row in the parent table LOC:
SQL> UPDATE loc 2 SET DATA = 99 3 WHERE LOC_ID = 2; 1 row updated.
Now we’ll pop into a new session and update the child table LOC_CHILD:
SQL> MERGE 2 INTO LOC_CHILD 3 USING (SELECT 500 CHILD_SEQ, 4 2 LOC_ID, 5 1000 CHILD_DATA 6 FROM DUAL) M 7 ON ( LOC_CHILD.CHILD_SEQ = M.CHILD_SEQ 8 AND LOC_CHILD.LOC_ID = M.LOC_ID) 9 WHEN MATCHED 10 THEN 11 UPDATE SET 12 LOC_CHILD.CHILD_DATA = NVL (LOC_CHILD.CHILD_DATA, 0) + M.CHILD_DATA 13 WHEN NOT MATCHED 14 THEN 15 INSERT (CHILD_SEQ, 16 LOC_ID, 17 CHILD_DATA) 18 VALUES (M.CHILD_SEQ, 19 M.LOC_ID, 20 M.CHILD_DATA); [stuck]
And splat…we’re stuck. (Without evidence to support it) I’d hypothesize its due to the flexibility of the merge command. A single merge can insert, update and even delete rows, so I’m guessing that to handle this flexibility (in particular, the DELETE option) then the locking errs on the side of safety.