Unindexed foreign keys again: and yes, it does matter!

Some months ago, I was investigating the classical “unindexed foreign key locking scenario”: what happens if, in a parent table/child table relationship, the parent table gets updated or deleted, including its primary key (see https://recurrentnull.wordpress.com/2013/12/17/unindexed-foreign-keys-considered-harmful-but-are-they/).
Basically, it turned out that as long as you’re not using on delete cascade, few spectacular things do happen …

However, in the real world, what can happen – and might even be a probable and common scenario – is the following:
The application has to deal with complex objects, combined by some form of composition, that on the database side turn up as parent/child relationships. Now if the application does not want to rely on the database to take care of dependencies (using on delete cascade), it has to clean up the dependent child entries before deleting the parent – a nice and clean approach…
However, if this is done concurrently, the following scenario may emerge:

Session wants to delete complex object 1:

SQL> delete from child where parent_id=1; -- session 1, object 1

1 row deleted.

SQL> select sid, type, object_name, id1, id2, lmode, request, block
    from gv$lock l left join dba_objects o
    on (l.id1=o.object_id)
    where type='TM' or type='TX'
    order by sid, type;

SID TY OBJECT_NAME          ID1        ID2      LMODE    REQUEST      BLOCK
-- -- -------------- ---------- ---------- ---------- ---------- ----------
280 TM PARENT            446690          0          3          0          2
280 TM CHILD             446692          0          3          0          2
280 TX                  1179664    1314092          6          0          2       

At the same time, another session needs to delete complex object 2:

SQL> delete from child where parent_id=1; -- session 2, object 2

1 row deleted.

SQL> select sid, type, object_name, id1, id2, lmode, request, block
    from gv$lock l left join dba_objects o
    on (l.id1=o.object_id)
    where type='TM' or type='TX'
    order by sid, type;

SID TY OBJECT_NAME          ID1        ID2      LMODE    REQUEST      BLOCK
-- -- -------------------- ---- ---------- ---------- ---------- ----------
278 TM CHILD              446692          0        3          0          2
278 TM PARENT             446690          0        3          0          2
278 TX                    1114138   1429973        6          0          2
280 TM PARENT             446690          0        3          0          2
280 TM CHILD              446692          0        3          0          2
280 TX                    1179664   1314092        6          0          2

So far, so good… Both sessions have the parent as well as child tables locked in row exclusive mode, no conflicts…
Now session 1 wants to continue, updating the corresponding parent row:

SQL> delete from parent where id=1;

And there we have it, session 1 hangs – it needs to upgrade its lock on the child table to mode 5 (share row exclusive), which is incompatible with the table being locked in row exclusive mode by session 2:

SQL> select sid, type, object_name, id1, id2, lmode, request, block
    from gv$lock l left join dba_objects o
    on (l.id1=o.object_id)
    where type='TM' or type='TX'
    order by sid, type;

SID TY OBJECT_NAME                 ID1        ID2      LMODE    REQUEST      BLOCK
--- -- -------------------- ---------- ---------- ---------- ---------- ----------
278 TM CHILD                    446692          0          3          0          1
278 TM PARENT                   446690          0          3          0          2
278 TX                         1114138    1429973          6          0          2
280 TM PARENT                   446690          0          3          0          2
280 TM CHILD                    446692          0          3          5          1
280 TX                         1179664    1314092          6          0          2

Looking at this scenario, it might be a bit of an unlucky timing, but certainly this course of events is not improbable at all! …Such that in the real world, it might make a lot of sense still to check for unindexed foreign keys …