在子表中插入可防止鎖定父表

我是一名軟體開發人員，雖然我寫了一兩個查詢，但我不是 DBA，所以如果我使用了錯誤的術語或聽起來像 n00b，請多多包涵。

我們最近遇到了一些大問題，這些問題被追踪到沒有支持索引的外鍵。從父表中刪除時，需要對子表加鎖。這是有道理的，而且描述得很好。

在深入研究這一點時，我還發現插入子項（孫子項）將以這樣的方式使用子項，在事務關閉之前無法獲取表鎖。對於這種行為，我找不到太多文件。我得到的最接近的是這個論壇文章，它基本上說“它就是這樣”，但沒有提供太多背景資訊。

所以，讓我分享一個例子來重現這個。我有一個任務表，其中包含員工要執行的任務（例如致電客戶、查找產品）。這些任務可以連結到許多實體，包括在 OrderLine 上。OrderLine，顯然包含帶有產品、數量等的訂單行。然後是一個記錄表，ActionHistory，其中包含有關發生在各種實體（如產品、訂單、客戶和任務）上的各種更改的資訊。

所以基本上，ActionHistory 是 Task 的子代，Task 又是 OrderLine 的子代。創建這種情況的腳本：

-- 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;

TBL_Task.OrderLineID 上的 FK 未編入索引。

TBL_ActionHistory.TaskID 上的 FK確實有一個適當的索引。

在會話 1 中，我在我的操作歷史記錄中添加了一些（完全不相關的）內容。它甚至沒有TaskID。

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

然後，在會話 2 中，我嘗試刪除訂單行。

delete from TBL_OrderLine where OrderLineId = 2;

這第二條語句將等到第一條語句的事務關閉。這表明對 ActionHistory 的插入正在阻止其他會話獲得對 Task 表的鎖定。

這讓我感到驚訝，因為 Task 表中根本沒有修改任何內容。它可能僅用於檢查，但在此之後，它可以完成。新的 ActionHistory 記錄沒有引用 Task 表中的任何行，因此那裡不需要數據鎖，那麼為什麼會發生這種情況？

我希望有人不僅可以提供理論，還可以提供解釋這一點的 Oracle 文件參考。當然，除了添加索引之外，我也願意接受更多調整這一點的建議。

我們使用的是 Oracle 11g (11.2.0.4.0)。

第一個說法，

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

將 TM 鎖放在 TBL_TASK 和 TBL_ACTIONHISTORY 表上以強制執行 FK 約束。第二個DELETE成功鎖定了 TBL_ORDERLINE 中的行，但它還必須鎖定整個 TBL_TASK 表（因為您沒有 TBL_TASK.OrderLineID 上的索引）。來自文件：

鎖和未索引的外鍵 當以下兩個條件都為真時，數據庫在子表上獲取全表鎖：

子表的外鍵列上不存在索引。

會話修改父表中的主鍵（例如，刪除行或修改主鍵屬性）或將行合併到父表中。對父表的插入不會獲取子表上的表鎖。

假設 hr.departments 表是 hr.employees 的父表，其中包含未索引的外鍵 department_id。圖 9-3 顯示了修改部門表中部門 60 的主鍵屬性的會話。

引用自：https://dba.stackexchange.com/questions/154145