SQL Oracle 选择更新行为
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5847228/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle select for update behaviour
提问by mateusz.fiolka
The problem we try to solve looks like this.
我们试图解决的问题看起来像这样。
- 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
- 我们有一个充满代表卡片的行的表格。预订交易的目的是为客户分配一张卡
- 一张卡不能属于多个客户
- 一段时间后（如果没有购买）一张卡必须返回到可用资源池
- 可多人同时预约
- 我们使用 Oracle 数据库来存储数据，因此解决方案必须至少在 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.
但是我们的查询没有按预期工作。
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.
我准备了一个简化的情况来解释问题。我们有一个 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;
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.
所以，在这个冗长的介绍之后，问题来了。
Is the kind of desired locking behaviour possible in Oracle? If yes, then what are we doing wrong? What would be the correct solution?
在 Oracle 中可以实现这种所需的锁定行为吗？如果是，那么我们做错了什么？什么是正确的解决方案？
采纳答案by Vincent Malgrat
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.
您在 FOR UPDATE SKIP LOCKED 中遇到的行为已在此博客说明 中进行了描述。我的理解是 FOR UPDATE 子句是在 WHERE 子句之后计算的。SKIP LOCKED 就像一个额外的过滤器，它保证在本应返回的行中没有被锁定。
Your statement is logically equivalent to: find the first row from card_numbersand return it if it is not locked. Obviously this is not what you want.
你的语句在逻辑上等同于：找到第一行，card_numbers如果它没有被锁定，则返回它。显然这不是你想要的。
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).
由于我已经明确地获取了游标，因此只会返回一行（并且只会锁定一行）。
回答by Lukas Eder
While the other answers already sufficiently explained what's going on in your database with the various SELECT .. FOR UPDATEvariants, I think it's worth mentioning that Oracle discourages using FOR UPDATE SKIP LOCKEDdirectly and encourages using Oracle AQinstead:
虽然其他答案已经充分解释了数据库中各种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
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346
We use Oracle AQin our application and I can confirm that, after a somewhat steep learning curve, it can be a quite convenient way to handle producers/consumers directly in the database
我们Oracle AQ在我们的应用程序中使用，我可以确认，经过一段陡峭的学习曲线后，它可以是一种非常方便的方法来直接在数据库中处理生产者/消费者
回答by user734922
Not that Vincent's answer is wrong but I would have designed it differently.
并不是说文森特的答案是错误的，但我会以不同的方式设计它。
My first instinct is to select for update the first available record and updated the record with a "reserved_date". After XXX time has passed and the transaction is not finalized, update the record's reserved_date back to null freeing up the record again.
我的第一直觉是选择更新第一个可用记录并使用“reserved_date”更新记录。在 XXX 时间过去并且事务未完成后，将记录的 reserved_date 更新回 null 以再次释放记录。
I try to keep things as simple as possible. For me, this is simpler.
我尽量让事情变得简单。对我来说，这更简单。
