Insert in child table prevents lock on parent table

I'm a software developer, and although I've written a query or two, I'm not DBA, so please bear with me if I use the wrong terminology or plainly sound like a n00b.

We've recently had some big issues, which was tracked down to a foreign key which didn't have a supporting index. When deleting from the parent table, it needs to get a lock on the child table.
This makes sense, and it well described.

When diving in to this, I also found out that inserts on a child of the child (grandchild) will use the child in such a way, that no table lock can be aquired until the transaction is closed. For this behaviour I can't find much documentation. The closest I get is this forum post which basically says 'it is how it is', without providing much context.

So, let me share an example to reprocuce this. I got a Task table, which contains tasks for employees to perform (like call a customer, find a product). These tasks can be linked to a number of entities, including on OrderLine. OrderLine, obiously contains lines of orders with a products, quantity and so on.
Then there is a logging table, ActionHistory, which contains information about all kinds of changes which happened on various entities, like product, orders, customers, and tasks.

So basically, ActionHistory is a child of Task, which in turn is a child of OrderLine. Script to create this situation:

```
-- CREATE Parent table (orderline)
create table TBL_OrderLine(
OrderLineID integer
);
alter table TBL_OrderLine
add constraint PK_OrderLine primary key (OrderLineID);

-- CREATE Child table (Task)
-- Task gets a new column, orderlineid, which has an FK, but is not indexed.
-- Because of this, the table is locked
create table TBL_Task(
TaskID integer,
OrderLineID integer
);
alter table TBL_Task
add constraint PK_Task
primary key (TaskID);
alter table TBL_Task
add constraint FK_Task_OrderLine
foreign key (OrderLineID)
references TBL_OrderLine(OrderLineID);

-- CREATE Log table (child of child

The first statement,

insert into TBL_ActionHistory(ActionHistoryID, Message) 
    values (201, 'Something');

puts TM locks on TBL_TASK and TBL_ACTIONHISTORY tables to enforce FK constraint.
The second DELETE successfully locks row in TBL_ORDERLINE, but it also has to lock the whole TBL_TASK table (because you don't have index on TBL_TASK.OrderLineID) . From docs :


Locks and Unindexed Foreign Keys When both of the following conditions
are true, the database acquires a full table lock on the child table:


No index exists on the foreign key column of the child table.


A session modifies a primary key in the parent table (for example,
deletes a row or modifies primary key attributes) or merges rows into
the parent table. Inserts into the parent table do not acquire table
locks on the child table.


Suppose that hr.departments table is a parent of hr.employees, which
contains the unindexed foreign key department_id. Figure 9-3 shows a
session modifying the primary key attributes of department 60 in the
departments table.

StackExchange Database Administrators Q#154145, answer score: 2