从具有多个会话的同一张表中进行选择
可能的重复:
强制 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技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这个问题之前出现过,然后我给出了一个相当详细的答案:
强制 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.
您实际要求的是 READ UNCOMMITTED,或者 Oracle 不喜欢的“脏读”,而不是示例中的
SKIP LOCKED
。我读过这个问题/答案: Oracle 是否允许未提交的读取选项?
Instead of the
SKIP LOCKED
in your example, what you're actually asking for isREAD 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?