sql - 强制 Oracle 使用 SKIP LOCKED 返回前 N 行

Question

有一个few questions关于如何在 Oracle 和 SQL Server 中实现类似队列的表(锁定特定行、选择其中一定数量的行以及跳过当前锁定的行)。

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

据我所知，Oracle 在确定要跳过哪些行之前应用 WHERE 谓词。这意味着，如果我想从表中提取一行，并且两个线程同时执行相同的 SQL，一个将收到该行，另一个将收到空结果集(即使有更多符合条件的行)。

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

注意，两篇有趣的文章here和 here .

甲骨文

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

在两个单独的 session 中，执行:

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

请注意，第一个 session 返回一行，第二个 session 不返回行:

第 1 节

 ID----  4

Session 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);

在两个单独的 session 中，执行:

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

请注意，两个 session 都返回不同的行。

第 1 节

 ID----  4

第二节

 ID----  3

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

Answer 1

“据我所知，Oracle 在确定要跳过哪些行之前应用 WHERE 谓词。”

是的。这是唯一可能的方法。在确定结果集之前，您不能跳过结果集中的行。

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

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

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;