强制Oracle通过“跳过锁定"返回TOP N行 [英] Force Oracle to return TOP N rows with SKIP LOCKED

问题描述

很少 <一个href ="https://stackoverflow.com/questions/3166615/how-to-work-around-ora-02014-cannot-select-for-update-from-view-with-distinct-g">问题关于如何在Oracle和SQL Server中实现类似队列的表(锁定特定行，选择特定数目的行，并跳过当前锁定的行).

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.

假设至少有N行符合条件，我如何保证我检索到一定数量(N)行?

How can I guarantee that I retrieve a certain number (N) rows, assuming there are at least N rows eligible?

据我所知，Oracle在确定要跳过哪些行之前应用了WHERE谓词.这意味着如果我要从表中拉出一行，并且两个线程同时执行同一SQL，则一个线程将接收该行，而另一个线程将接收一个空的结果集(即使有更多合格的行).

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

这与SQL Server似乎处理UPDLOCK，ROWLOCK和READPAST锁定提示的方式相反.在SQL Server中，TOP似乎神奇地限制了 成功获得锁之后的记录数.

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.

请注意，此处和此处.

ORACLE

CREATE TABLE QueueTest (
    ID NUMBER(10) NOT NULL,
    Locked NUMBER(1) NULL,
    Priority NUMBER(10) NOT NULL
);

ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY (ID);

CREATE INDEX IX_QueuePriority ON QueueTest(Priority);

INSERT INTO QueueTest (ID, Locked, Priority) VALUES (1, NULL, 4);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (2, NULL, 3);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (3, NULL, 2);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (4, NULL, 1);

在两个单独的会话中，执行:

In two separate sessions, execute:

SELECT qt.ID
FROM QueueTest qt
WHERE qt.ID IN (
    SELECT ID
    FROM
        (SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority)
    WHERE ROWNUM = 1)
FOR UPDATE SKIP LOCKED

请注意，第一个会话返回一行，而第二个会话不返回一行:

Note that the first returns a row, and the second session does not return a row:

会议1

 ID
----
  4

会议2

 ID
----

---

SQL SERVER

CREATE TABLE QueueTest (
    ID INT IDENTITY NOT NULL,
    Locked TINYINT NULL,
    Priority INT NOT NULL
);

ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY NONCLUSTERED (ID);

CREATE INDEX IX_QueuePriority ON QueueTest(Priority);

INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 4);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 3);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 2);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 1);

在两个单独的会话中，执行:

In two separate sessions, execute:

BEGIN TRANSACTION
SELECT TOP 1 qt.ID
FROM QueueTest qt
WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE Locked IS NULL
ORDER BY Priority;

请注意，两个会话都返回不同的行.

Note that both sessions return a different row.

会议1

 ID
----
  4

会议2

 ID
----
  3

如何在Oracle中获得类似的行为?

How can I get similar behavior in Oracle?

推荐答案

据我所知，Oracle在确定要跳过的行之前应用了WHERE谓词."

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

答案仅仅是不限制SELECT语句返回的行数.您仍然可以使用FIRST_ROWS_n提示来指示优化程序您不会获取完整的数据集.

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.

调用SELECT的软件应仅选择前n行.在PL/SQL中，应该是

The software calling the SELECT should only select the first n rows. In PL/SQL, it would be

DECLARE
  CURSOR c_1 IS  
    SELECT /*+FIRST_ROWS_1*/ qt.ID
    FROM QueueTest qt
    WHERE Locked IS NULL
    ORDER BY PRIORITY
    FOR UPDATE SKIP LOCKED;
BEGIN
  OPEN c_1;
  FETCH c_1 into ....
  IF c_1%FOUND THEN
     ...
  END IF;
  CLOSE c_1;
END;

这篇关于强制Oracle通过“跳过锁定"返回TOP N行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！