强制 Oracle 返回带有 SKIP LOCKED 的 TOP N 行

2022-01-21 00:00:00 queue sql oracle sql-server

有少数问题关于如何在 Oracle 和 SQL Server 中实现类似队列的表(锁定特定行，选择一定数量的行，跳过当前锁定的行).

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

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

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

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

甲骨文

CREATE TABLE QueueTest (ID NUMBER(10) 非空，锁定 NUMBER(1) NULL，优先级 NUMBER(10) NOT NULL);ALTER TABLE QueueTest 添加约束 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);

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

选择 qt.IDFROM QueueTest qtqt.ID 在哪里(选择编号从(从 QueueTest 中选择 ID WHERE Locked IS NULL ORDER BY Priority)其中 ROWNUM = 1)对于更新跳过锁定

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

会话 1

<上一页>ID----4

第 2 节

<上一页>ID----

<小时>

SQL 服务器

CREATE TABLE QueueTest (ID INT IDENTITY NOT NULL,锁定 TINYINT NULL，优先级 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);

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

开始交易选择前 1 个 qt.IDFROM QueueTest qtWITH (UPDLOCK, ROWLOCK, READPAST)WHERE 锁定为 NULL按优先顺序排序；

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

会话 1

<上一页>ID----4

第 2 节

<上一页>ID----3

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

解决方案

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

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

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

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

声明光标 c_1 是选择/*+FIRST_ROWS_1*/qt.IDFROM QueueTest qtWHERE 锁定为 NULL按优先顺序排列对于更新跳过锁定；开始打开 c_1;将 c_1 提取到 ....如果 c_1% 找到 那么...万一;关闭 c_1;结尾;

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

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:

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

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.

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

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;

相关文章