oracle - 我可以用来防止行被插入到 Oracle 表中的侵入性最小的锁定机制是什么？

Question

我定义了一个父/子关系，不幸的是，它不能通过外键维护。 parent 和 child 存储在同一张表中。父/子关系由列“ITEM_ID”标识。子项 ITEM_ID 由其父项 ITEM_ID 以及我们可以有效地认为是该父项中子项的唯一标识符组成。

我正在执行一个 PL/SQL 过程来从这个 ITEM 表中删除记录。该过程的第一部分检查是否存在任何 child ；如果是这样，它会引发应用程序错误(模拟外键)。

我想知道如何防止在删除过程中的游标被填充并锁定“FOR UPDATE”与父记录实际删除之间的时间点之间的另一个进程插入子记录。

我需要在这个过程中锁定整个表吗？

或者我应该更改插入过程以选择父记录“FOR UPDATE”吗？

更新:我创建上面的示例只是为了描述一般情况，但为了证明我的外键/约束问题的合理性，我将在下面给出我的实际/更复杂的结构:

远程数据库中有几个表:COMPANY、BUILDING、FLOOR。在我们的组织中，楼层属于建筑物，建筑物属于公司。

我正在处理的应用程序将角色与员工相关联。将雇员(雇员 ID)与角色(角色 ID)联系起来的表也有一个“位置”列。 location列对应远程数据库表的一个ID，我们根据Role表中的Type列来识别它属于哪个表。

例如，这是我表中的几条记录:

Role = Janitor
Type = BUILDING
Location = COMPANY1-BUILDING1
Parent Role = Manager


Role = Manager
Type = COMPANY
Location = COMPANY1
Parent Role = CEO

您可以猜到，楼层标识符在远程数据库表中的格式为 company-building-floor。

Janitor 与 BUILDING 级别相关联，因此其 Location 列是一个 BUILDING 标识符(实际上是公司标识符后跟 BUILDING 表中的建筑物标识符)。

Answer 1

让删除过程在父记录上获得普通锁不会阻止插入子记录。锁定整个表会严重序列化您的应用程序。

因此，强制插入过程在父记录上选择... FOR UPDATE 并在删除过程中锁定父记录是您唯一的选择。这种实现的主要问题是它很容易被规避。或者换句话说，与表交互的每个进程都将不得不发出这些额外的锁。