Oracle选择更新行为 [英] Oracle select for update behaviour

问题描述

我们试图解决的问题看起来像这样.

The problem we try to solve looks like this.

• 我们有一张充满代表卡片的行的表格.预订交易的目的是将卡分配给客户
• 一张卡不能属于多个客户
• 过了一段时间(如果没有购买)，必须将卡退还到可用的资源库中.
• 许多客户可以同时预订
• 我们使用Oracle数据库存储数据，因此解决方案至少必须在Oracle 11上运行

• We have a table full of rows which represent cards. The purpose of reservation transaction is to assign a card to a client
• A card can not belong to many clients
• After some time (if it is not bought) a card has to be returned to the pool of available resurces
• Reservation can be done by many clients at the same time
• We use Oracle database for storing the data, so solution has to work at least on Oracle 11

我们的解决方案是为卡分配状态，并存储其预订日期.预订卡时，我们使用选择更新"语句来完成.该查询将查找可用的卡和很久以前保留的卡.

Our solution is to assign a status to the card, and store it's reservation date. When reserving a card we do it using "select for update" statement. The query looks for available cards and for cards which were reserved long time ago.

但是我们的查询无法正常工作.

However our query doesn't work as expected.

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

I have prepared a simplified situation to explain the problem. We have a card_numbers table, full of data - all of the rows have non-null id numbers. Now, let's try to lock some of them.

-- first, in session 1
set autocommit off;

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

我们不在此处提交交易，该行必须被锁定.

We don't commit the transaction here, the row has to be 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;

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

The expected behaviour is that in both sessions we get a single, different row which satisfies query conditions.

但是，这种方式行不通.取决于我们是否使用查询的跳过锁定"部分-行为更改:

However it doesn't work that way. Depending on whether we use the "skip locked" part of the query or not - the behavious changes:

• 没有跳过锁定"状态-第二个会话被阻止-等待会话一中的事务提交或回滚
• 跳过锁定"-第二个查询立即返回空结果集

• without "skip locked" - second session is blocked - waiting for transaction commit or rollback in session one
• with "skip locked" - second query returns immediately empty result set

因此，在漫长的介绍之后出现了问题.

So, after this long introduction comes the question.

Oracle中可能会发生那种所需的锁定行为吗?如果是，那么我们在做什么错呢?正确的解决方案是什么?

Is the kind of desired locking behaviour possible in Oracle? If yes, then what are we doing wrong? What would be the correct solution?

推荐答案

The behaviour you've encountered for FOR UPDATE SKIP LOCKED has been described in this blog note. My understanding is that the FOR UPDATE clause is evaluated AFTER the WHERE clause. The SKIP LOCKED is like an additional filter that guarantees that among the rows that would have been returned, none are locked.

您的语句在逻辑上等效于:从card_numbers查找第一行，如果未锁定则返回第一行.显然这不是您想要的.

Your statement is logically equivalent to: find the first row from card_numbers and return it if it is not locked. Obviously this is not what you want.

这里有一个小测试用例，可以重现您描述的行为:

Here is a little test case that reproduces the behaviour you describe:

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

第二个选择不返回任何行.您可以使用光标解决此问题:

No row is returned from the second select. You can use a cursor to work around this issue:

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

自从我显式地获取了游标以来，将仅返回一行(并且只有一行将被锁定).

Since I've explicitely fetched the cursor, only one row will be returned (and only one row will be locked).

这篇关于Oracle选择更新行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！