Force Oracle to return TOP N rows with SKIP LOCKED

Question

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?

Answer 1

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

Answer 2

The solution Gary Meyers posted is about all I can think of, short of using AQ, which does all this for you and much more.

If you really want to avoid the PLSQL, you should be able to translate the PLSQL into Java JDBC calls. All you need to do is prepare the same SQL statement, execute it and then keep doing single row fetches on it (or N row fetches).

The Oracle documentation at http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1023642 gives some clue how to do this at the statement level:

To set the fetch size for a query, call setFetchSize() on the statement object prior to executing the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

So you could code up something in Java that looks something like (in Pseudo code):

stmt = Prepare('SELECT /*+FIRST_ROWS_1*/ qt.ID FROM QueueTest qt WHERE Locked IS NULL ORDER BY PRIORITY FOR UPDATE SKIP LOCKED');  stmt.setFetchSize(10); stmt.execute();  batch := stmt.fetch(); foreach row in batch {   -- process row } commit (to free the locks from the update) stmt.close; 

UPDATE

Based on the comments below, a suggestion was made to use ROWNUM to limit the results received, but that won't work in this case. Consider the example:

create table lock_test (c1 integer);  begin   for i in 1..10 loop     insert into lock_test values (11 - i);   end loop;   commit; end; / 

Now we have a table with 10 rows. Note that I have carefully inserted the rows in reverse order, the row containing 10 is first, then 9 etc.

Say you want the first 5 rows, ordered ascending - ie 1 to 5. Your first try is this:

select * from lock_test where rownum <= 5 order by c1 asc; 

Which gives the results:

C1 -- 6 7 8 9  10 

That is clearly wrong, and is a mistake almost everyone makes! Look at the explain plan for the query:

---

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |           |     5 |    65 |     4  (25)| 00:00:01 | |   1 |  SORT ORDER BY      |           |     5 |    65 |     4  (25)| 00:00:01 | |*  2 |   COUNT STOPKEY     |           |       |       |            |          | |   3 |    TABLE ACCESS FULL| LOCK_TEST |    10 |   130 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     2 - filter(ROWNUM<=5) 

Oracle executes the plan from the bottom up - notice that the filter on rownum is carried out before the sort, Oracle takes the rows in the order it finds them (order they were inserted here { 10, 9, 8, 7, 6}), stops after it gets 5 rows, and then sorts that set.

So, to get the correct first 5 you need to do the sort first and then the order by using an inline view:

select * from (   select *   from lock_test   order by c1 asc ) where rownum <= 5;  C1 -- 1 2 3 4 5 

Now, to finally get to the point - can you put a for update skip locked in the correct place?

select * from (   select *   from lock_test   order by c1 asc ) where rownum <= 5 for update skip locked; 

This gives an error:

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc 

Trying to move the for update into the view gives a syntax error:

select * from (   select *   from lock_test   order by c1 asc   for update skip locked ) where rownum <= 5; 

The only thing that will work is the following, which GIVES THE WRONG RESULT:

  select *   from lock_test   where rownum <= 5   order by c1 asc   for update skip locked; 

Infact, if you run this query in session 1, and then run it again in session two, session two will give zero rows, which is really really wrong!

So what can you do? Open the cursor and fetch how many rows you want from it:

set serveroutput on  declare   v_row lock_test%rowtype;   cursor c_lock_test   is   select c1   from lock_test   order by c1   for update skip locked; begin   open c_lock_test;   fetch c_lock_test into v_row;   dbms_output.put_line(v_row.c1);   close c_lock_test; end; /     

If you run that block in session 1, it will print out '1' as it got a locked the first row. Then run it again in session 2, and it will print '2' as it skipped row 1 and got the next free one.

This example is in PLSQL, but using the setFetchSize in Java you should be able get the exact same behaviour.