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

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

但是，它不是那样工作的。取决于我们是否使用查询的“跳过锁定”部分 - 行为变化:

没有“跳过锁定” - 第二个 session 被阻止 - 在 session 一中等待事务提交或回滚

使用“跳过锁定” - 第二个查询立即返回空结果集

所以，在这个冗长的介绍之后，问题来了。

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

Answer 1

您遇到的 FOR UPDATE SKIP LOCKED 行为已在 this blog note 中描述。 .我的理解是 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

因为我已经明确地获取了游标，所以只会返回一行(并且只有一行会被锁定)。