Insert in child table prevents lock on parent table

foreign keylockingoracleoracle-11g

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, Action history)
-- Action history (since only recently, by the way) has an FK to Task, which is propertly indexed.
create sequence SEQ_ActionHistoryID;

create table TBL_ActionHistory(
  ActionHistoryID integer,
  TaskID integer,
  Message varchar2(4000)
);
alter table TBL_ActionHistory
  add constraint PK_ActionHistory 
    primary key (ActionHistoryID);

alter table TBL_ActionHistory
  add constraint FK_ActionHistory_Task 
    foreign key (TaskID) 
    references TBL_Task(TaskID);

create index IDX_ActionHistory_TaskId on TBL_ActionHistory(TaskID);

-- Insert a bunch of orderlines
insert into TBL_OrderLine values (1);  
insert into TBL_OrderLine values (2);
insert into TBL_OrderLine values (3);

-- Insert a bunch of (unrelated) tasks.
insert into TBL_Task values (101, null);
insert into TBL_Task values (102, null);
insert into TBL_Task values (103, null);

commit;

The FK on TBL_Task.OrderLineID isn't indexed.
The FK on TBL_ActionHistory.TaskID does have a proper index.

In session 1, I add something (completely unrelated) to my action history. It doesn't even have a TaskID.

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

Then, in session 2, I try to remove an order line. 

delete from TBL_OrderLine where OrderLineId = 2;

This second statement will wait until the transaction of the first statement is closed. This indicates that the insert into ActionHistory is blocking the other session from getting a lock on the Task table.

This surprised me, because nothing is being modified in the Task table at all. It may be used just for checking, but after that, it could be done. The new ActionHistory record is not referencing any row in the Task table, so no data lock needed there, so why is this happening?

I hope someone can provide not only the theory, but also a reference to Oracle documentation that explains this.
And of course I'm open to suggestions to tweak this even more apart from just adding the index.

We're using Oracle 11g (11.2.0.4.0).

Best Answer

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.

Related Question