Posted by 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 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.
Learning is not a spectator sport 
Hello Connor,
I slightly modified you test case:
1. maked LOC_CHILD table HOT (heap-organized)
2. changed merge to INSERT:
insert into loc_child( child_seq, loc_id, child_data) values( 500, 2, 1000);
Session 2 is requested TX lock in mode 4:
That is probably IOT/FOREIGN KEY/LOCKING issue, not a MERGE issue.
Gathered additional information: index block dumps, trace files of event 10704 (enqueues) and DTrace’d server process (using Tanel Poder qer_trace.sh modified for catching kcbgcur calls – current block gets) – still it is not clear why Oracle behave in this way.
We actively using IOTs in our applications and this issue can negatively affect us.
Do you plan to create Service Request?
Hi Mikhail,
Thanks for exploring the topic further.
For my client, we worked around the issue by modifying some of the processes that the application used, so have not logged an SR.
But I agree with you – I can’t see why Oracle has adopted such a stance when it comes to IOT’s
Cheers,
Connor
Connor,
I think it’s a necessary wait because of the potential for deadlocks in odd scenarios. The critical features is that you don’t have a table row for the parent, only an index entry – so the lock has to be on the index entry. In theory Oracle Corp could probably write some code to allow the child update to determine that the lock existed because a non-key column had been changed, in practice it simply says “the pk entry is locked, I have to wait for the commit/rollback”.
You can see the same effect with a pure heap-table example if you support the PK with an index that has a non-key column added to it and you update the non-key column.
Hello,
I’m apologize, but PK with non-key column behaves slightly differently.
Old index entry after update marked as deleted and new index entry is created:
SQL> create table t1(x int, y int, constraint t1_pk primary key(x) using index (create index t1_pk on t1(x,y))); Table created. SQL> insert into t1 values (1,1); 1 row created. SQL> commit; Commit complete. SQL> select object_id from obj where object_name='T1_PK'; OBJECT_ID ---------- 329184 SQL> select dbms_rowid.rowid_relative_fno(rid) file#, dbms_rowid.rowid_block_number(rid) block from (select sys_op_lbid( 329184, 'L', rowid) rid from t1 where x=1); FILE# BLOCK ---------- ---------- 12 834475 SQL> alter system checkpoint; System altered. SQL> alter system dump datafile 12 block 834475; System altered. SQL> update t1 set y=2 where x=1; 1 row updated. SQL> alter system checkpoint; System altered. SQL> alter system dump datafile 12 block 834475; System altered. -- index block dump row#0[8017] flag: ---D--, lock: 2, len=15 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 6; (6): 03 0c bb a3 00 00 row#1[8002] flag: ------, lock: 2, len=15 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 03 col 2; len 6; (6): 03 0c bb a3 00 00IOT after non-key update not create new index entry and marked index entry as deleted only after key update:
I’m dont know clearly why Oracle cannot check index entry flag to differentiate key/non-key updates in our case.
Hello,
I created SR 3-9505364811 : IOT FOREIGN KEY LOCKING 2 months ago with providing link to this blog post.
3rd of September support engineer opened new Bug 19552277 : UPDATE ON IOT PARENT TABLE BLOCKS DML ON CHILD TABLE.
After 2 weeks that bug was closed with status “32 – Not a Bug. To Filer”.
4th of November support engineer opened new documentation Bug 19949828 : NEED TO DOCUMENT THE LIMITATION OF IOT PARENT TABLE AND FOREIGN KEY CONSTRAINTS.
It reminds me of phrase “a documented bug is feature” :).
Still this’s not clear for me, why Oracle Corp could not improve their code as Jonathan suggested.
At least, documentation will be updated with IOT locking restrictions.
Hello,
Just FYI, Oracle Support documented this particular case of IOT locking in note:
Update on Parent Index-Organized Table Blocks DML on Child Table (Doc ID 1954787.1)
Thank Mikhail for keeping us up to date with this.
Cheers,
Connor