从具有多个会话的同一张表中进行选择

发布于 2024-12-10 03:54:14 字数 1047 浏览 0 评论 0原文

可能的重复:
强制 Oracle 返回带有 SKIP LOCKED 的 TOP N 行

我在 Oracle 上的 FOR UPDATE 子句中遇到一些问题。我想要做的是从表中选择多个值(例如 1000,但该值可能会在运行时由用户更改)并处理它们。但是,我的应用程序可能在多个节点上运行,并且所有节点都将使用同一个表,因此我必须锁定记录以防止多个节点获取相同的记录。

为了演示它,让我们创建一个示例表:

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

这就是我选择记录的方式。这里我尝试获取 3 条记录。

SELECT rownum r, a.ID i
  FROM (SELECT * FROM t ) a 
 WHERE  rownum <= 3
 FOR UPDATE skip locked 

此查询返回 3 条记录

+---+---+
+ R + I +
+---+---+
+ 1 + 1 +
+---+---+
+ 2 + 2 +
+---+---+
+ 3 + 3 +
+---+---+

当我从另一个会话运行相同的查询(未提交会话 1)时,我得到一个空结果集。但我真正想要的是获取接下来的 3 个项目(在本例中为 4、5、6)。我知道这是预期的行为,但无法找到适当的解决方案。如何在不创建 Oracle 过程的情况下解决这个问题?

Possible Duplicate:
Force Oracle to return TOP N rows with SKIP LOCKED

I am experiencing some problems with FOR UPDATE clause on Oracle. What I want to do is select a number of values (say 1000, but this value might be changed by the user at runtime) from a table and process them. But, my application may run on multiple nodes and all of them will use the same table, so I have to lock the records to prevent same records to be fetched by multiple nodes.

To demonstrate it, lets create a sample table:

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

This is how I select the records. Here I tried to fetch 3 records.

SELECT rownum r, a.ID i
  FROM (SELECT * FROM t ) a 
 WHERE  rownum <= 3
 FOR UPDATE skip locked 

This query returns 3 records

+---+---+
+ R + I +
+---+---+
+ 1 + 1 +
+---+---+
+ 2 + 2 +
+---+---+
+ 3 + 3 +
+---+---+

When I ran the same query from another session (without commiting the session 1), I get an empty resultset. But what I really want is fetch the next 3 items (4, 5, 6 in this case). I understand that this is the expected behaviour, but cannot find an appropriate solution to it. How can I solve this without creation an Oracle procedure?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

南…巷孤猫 2024-12-17 03:54:14

这个问题之前出现过,然后我给出了一个相当详细的答案:

强制 Oracle 返回 SKIP LOCKED 的前 N ​​行

除了给出的信息之外,我真的想不出其他方法(除了使用队列)在上面的线程中。

This came up before, and I gave a fairly detailed answer then:

Force Oracle to return TOP N rows with SKIP LOCKED

I cannot really think of another way (short of using a queue) than the information that is given in the thread above.

南汐寒笙箫 2024-12-17 03:54:14

您实际要求的是 READ UNCOMMITTED,或者 Oracle 不喜欢的“脏读”,而不是示例中的 SKIP LOCKED

我读过这个问题/答案: Oracle 是否允许未提交的读取选项?

Instead of the SKIP LOCKED in your example, what you're actually asking for is READ UNCOMMITTED, or a 'dirty read' which Oracle's not keen on.

I'd have a read of this question/answer: Does oracle allow the uncommitted read option?

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文