强制 Oracle 返回带有 SKIP LOCKED 的 TOP N 行
问题描述
有 少数 问题 关于如何在 Oracle 和 SQL Server 中实现类似队列的表(锁定特定行,选择一定数量的行,跳过当前锁定的行).
假设至少有 N
行符合条件,我如何保证检索到一定数量 (N
) 行?
据我所知,Oracle 在确定要跳过哪些行之前应用了 WHERE
谓词.这意味着,如果我想从表中提取一行,并且两个线程同时执行相同的 SQL,一个将接收该行,另一个将接收一个空结果集(即使有更多符合条件的行).
这与 SQL Server 处理 UPDLOCK
、ROWLOCK
和 READPAST
锁定提示的方式似乎相反.在 SQL Server 中,TOP
神奇地出现在 成功获得锁之后限制记录数.
注意,两篇有趣的文章这里和这里.
甲骨文
如何在 Oracle 中获得类似的行为?
据我所知,Oracle 在确定要跳过哪些行之前应用了 WHERE 谓词."
是的.这是唯一可能的方法.在确定结果集之前,您不能从结果集中跳过一行.
答案就是不限制 SELECT 语句返回的行数.您仍然可以使用 FIRST_ROWS_n 提示来指示优化器您不会抓取完整的数据集.
调用 SELECT 的软件应该只选择前 n 行.在 PL/SQL 中,它会是
There are a few questions on how to implement a queue-like table (lock specific rows, selecting a certain number of them, and skipping currently locked rows) in Oracle and SQL Server.
How can I guarantee that I retrieve a certain number (N
) rows, assuming there are at least N
rows eligible?
From what I have seen, Oracle applies the WHERE
predicate before determining what rows to skip. This means that if I want to pull one row from a table, and two threads concurrently execute the same SQL, one will receive the row and the other an empty result set (even if there are more eligible rows).
This is contrary to how SQL Server appears to handle the UPDLOCK
, ROWLOCK
and READPAST
lock hints. In SQL Server, TOP
magically appears to limit the number of records after successfully attaining locks.
Note, two interesting articles here and here.
ORACLE
In two separate sessions, execute:
Note that the first returns a row, and the second session does not return a row:
Session 1
ID ---- 4
Session 2
ID ----
SQL SERVER
In two separate sessions, execute:
Note that both sessions return a different row.
Session 1
ID ---- 4
Session 2
ID ---- 3
How can I get similar behavior in Oracle?
"From what I have seen, Oracle applies the WHERE predicate before determining what rows to skip."
Yup. It is the only possible way. You can't skip a row from a resultset until you have determined the resultset.
The answer is simply not to limit the number of rows returned by the SELECT statement. You can still use the FIRST_ROWS_n hints to direct the optimizer that you won't be grabbing the full data set.
The software calling the SELECT should only select the first n rows. In PL/SQL, it would be
这篇关于强制 Oracle 返回带有 SKIP LOCKED 的 TOP N 行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!