Oracle - SELECT-INSERT 锁定模式

Question

我有一张 table Orders (包含列 orderId、orderType、userId、state、...)。我需要我的流程来执行以下操作:

1. 检查是否存在特定类型的订单，特定用户的特定状态 (SELECT)。
2. 如果不存在这样的订单 - 创建一个 (INSERT)。

所以基本上我想确保总是只有一个订单:

• 订单类型 = x
• 用户 ID = y
• 状态=z

但是我不能创建约束，因为 x1、y1、z1 可以存在多个顺序。

我必须说我没有使用 Oracle 的经验。我读过 this article关于 Oracle 中的锁，它似乎认为这里唯一有用的锁类型是:

LOCK TABLE Orders IN EXCLUSIVE MODE

但我认为只为某些数据子集锁定整个表是矫枉过正的。我试过 SELECT ... FOR UPDATE OF <update_column>对 <update_column> 使用不同的列但他们允许我插入新行。

这种并发有什么模式吗？它接缝 Oracle 创建了 SELECT ... FOR UPDATE OF ...对于 SELECT-UPDATE 模式。 SELECT-INSERT 有类似的东西吗？

Answer 1

您可以创建一个基于函数的唯一索引来实现这种约束。如果您想强制要求有一个 state 为“Done”的唯一行，但允许多个行的 state 为“Draft”。

CREATE UNIQUE INDEX idx_index_name
    ON( CASE WHEN state = 'Done' THEN orderType ELSE NULL END,
        CASE WHEN state = 'Done' THEN userId ELSE NULL END,
        CASE WHEN state = 'Done' THEN state ELSE NULL END );