Programming in sql: Force Oracle to return TOP N rows with SKIP LOCKED on newest questions tagged sql – Stack Overflow

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?

See Answers


source: http://stackoverflow.com/questions/6117254/force-oracle-to-return-top-n-rows-with-skip-locked
Programming in sql: programming-in-sql

Leave a Reply