Oracle 选择更新行为

Question

我们试图解决的问题如下所示。

• 我们有一个满是代表卡片的行的表。预订交易的目的是将卡分配给客户
• 一张卡不能属于许多客户
• 一段时间后（如果未购买），一张卡必须返回到可用资源池
• 预订可以由许多客户完成同时
• 我们使用Oracle数据库来存储数据，所以解决方案必须至少在Oracle 11上工作

我们的解决方案是为该卡分配一个状态，并存储其预订日期。预订卡时，我们使用“选择更新”语句进行操作。该查询查找可用的卡和很久以前保留的卡。

然而我们的查询并没有按预期工作。

我准备了一个简化的情况来解释这个问题。 我们有一个 card_numbers 表，其中充满了数据 - 所有行都有非空 ID 号。 现在，让我们尝试锁定其中的一些。

-- first, in session 1
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

我们不在这里提交事务，该行必须被锁定。

-- later, in session 2
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

预期的行为是，在两个会话中，我们都会得到满足查询条件的单个不同行。

但事实并非如此。根据我们是否使用查询的“跳过锁定”部分 - 行为更改：

• 没有“跳过锁定” - 第二个会话被阻止 - 在会话一中等待事务提交或回滚，
• 并使用“跳过锁定” - 第二个查询返回立即清空结果集

那么，在漫长的介绍之后，问题来了。

Oracle 中是否可以实现所需的锁定行为？如果是的话，那么我们做错了什么？正确的解决方案是什么？

Answer 1

您所遇到的 FOR UPDATE SKIP LOCKED 行为已在此博客说明中进行了描述。我的理解是 FOR UPDATE 子句是在 WHERE 子句之后评估的。 SKIP LOCKED 就像一个附加的过滤器，保证在已返回的行中没有任何行被锁定。

您的语句在逻辑上相当于：从 card_numbers 中查找第一行，如果未锁定则返回它。显然这不是你想要的。

这是一个小测试用例，可以重现您所描述的行为：

SQL> CREATE TABLE t (ID PRIMARY KEY)
  2  AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;

Table created

SESSION1> select id from t where rownum <= 1 for update skip locked;

        ID
----------
         1

SESSION2> select id from t where rownum <= 1 for update skip locked;

        ID
----------

第二次选择没有返回任何行。您可以使用游标来解决此问题：

SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
  2     CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
  3     l_id NUMBER;
  4  BEGIN
  5     OPEN c;
  6     FETCH c INTO l_id;
  7     CLOSE c;
  8     RETURN l_id;
  9  END;
 10  /

Function created

SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
1

SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
2

由于我已显式获取游标，因此只会返回一行（并且只会锁定一行）。

Answer 2

虽然其他答案已经充分解释了数据库中各种 SELECT .. FOR UPDATE 变体的情况，但我认为值得一提的是，Oracle 不鼓励使用 FOR UPDATE SKIP LOCKED直接并鼓励使用 Oracle AQ 代替：

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346

我们在应用程序中使用 Oracle AQ我可以确认，经过一段陡峭的学习曲线后，这可能是一种非常方便的直接在数据库中处理生产者/消费者的方法

Answer 3

并不是说文森特的答案是错误的，但我会以不同的方式设计它。

我的第一反应是选择更新第一个可用记录并使用“reserved_date”更新该记录。经过 XXX 时间且交易尚未完成后，将记录的reserved_date 更新回 null，再次释放记录。

我尝试让事情尽可能简单。对我来说，这更简单。