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).