4

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

2
  • This might be a good question to Ask Tom Nov 3, 2016 at 15:50
  • Show us the relevant rows from v$lock when this is happening. It might be worth checking what kind of locks are held. Nov 3, 2016 at 16:22

1 Answer 1

2

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.

3
  • Thank you for your response. So far I understand. But the TM lock on TBL_TASK apparently is needed to the end of the transaction of the first statement, even though Oracle should be able to see that there is no change in VAN_TASK that could render the insert in TBL_ActionHistory invalid. I'm surprised by that, and I would have expected Oracle to release that lock right after the insert statement even before the transaction ended.
    – GolezTrol
    Nov 3, 2016 at 15:45
  • 2
    @GolezTrol : I think the explanation is that prior to locking a particular row, Oracle locks the table in the appropriate mode. So the first insert implicitly "issues " LOCK TABLE IN ROW EXCLUSIVE MODE , even though no rows actually locked. The lock remains until transaction is completed. The second query , DELETE attempts to lock table in SHARED mode (if you had index, it would also lock in row exclusive mode!) which is incompatible with Row Exclusive Table Lock. I agree it looks more like a bug in case of nullable FK (no need to lock parent table at all ).
    – a1ex07
    Nov 3, 2016 at 19:51
  • Yes. Well, maybe not a bug, but at least a big more rigid than it needs to be. Maybe some Oracle guys may read this thread and optimize this in the future. :-) Thanks for your answer!
    – GolezTrol
    Nov 4, 2016 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.