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?

Lepton

I'll give the bounty to someone who can give me a simpler answer than Gary Myers with his cursor, as I want to omit the cursor as well, just like the OP – Creosote

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;

Parotid

FIRST_ROWS_N and FIRST_ROWS(N) do not limit results returned, but optimize the query engine to return those rows more quickly. This causes all rows to be locked, still resulting in similar behavior when executed concurrently (first thread will lock all rows, second thread will skip locks and see nothing) – Lepton

On second look, since you are using a cursor, the row is not locked until fetched from the cursor per what I found here rwijk.blogspot.com/2009/02/for-update-skip-locked.html. I'm trying to solve this without PL/SQL (I'd like to execute a simple SQL batch from JDBC). – Lepton

This answer is interesting. The possibility to skip locked rows with a cursor might be an option, especially if the cursor is returned from a stored procedure as a REF CURSOR (if that's even possible). But like the OP, I'm more looking for a SQL-Server-like solution in a single SELECT statement – Creosote

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.

Quicksilver

Are you sure this will work? The FIRST_ROWS hint will cause Oracle to try to return the first rows as soon as possible, but I believe it will still end up locking all of the rows regardless of the number in the hint (the hint doesn't actually limit the number of records retrieved), or the fetch size set in JDBC. – Lepton

I am not sure it will work, but I think it will. Somewhere around this thread, someone said that Oracle only locks the rows as it returns then. If your fetch size is 10, then one call to fetch should give you 10 rows and lock that 10 and no more. I'd say you are going to have to code something up and give it a try. I would do it, but my Java skills are very rusty. – Quicksilver

I haven't tried setFetchSize(10) but limiting with ROWNUM <= 10. That has the same effect, except that the priority ordering is pretty useless when using ROWNUM. Committing after setting the locked flag does the trick indeed, but it's not very nice to commit in the middle of an EJB SessionBean handling business logic. And it still doesn't scale well, i.e. the probability that a second process will run the FOR UPDATE SKIP LOCKED query before the first one committed is still too high... But I guess that's the best solution short of using AQ... – Creosote

I don't think using ROWNUM to limit the results is going to give you what you want. I have updated my answer above to include more information - basically, I think you need to use setFetchSize. – Quicksilver

After your updates, I think this boils down to the original solution, but with more detail. Thanks for the extra research/info. – Lepton

Just tested with Oracle SQL developer Version 17.4.0.355. select for update on whole table about 1 million rows. Only the first couple hundred rows fetched* is been locked. (* fetched: those showed up in my sql developer Query Result tab) – Bobbee

Answer 3

In your first session, when you 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

Your inner select attempt to grab only id=4 and lock it. This is successful because this single row is not yet locked.

In second session, your inner select STILL tries to grab ONLY id=4 and lock it. This is not successful because that single row is still locked by first session.

Now, if you updated the "locked" field in first session, the next session to run that select will grab id=3.

Basically, in your example you are depending on a flag that isn't being set. To use your locked flag, you probably mean to do something like:

1. select IDs you want based on some criteria.
2. Immediate update the locked flag = 1 for these IDs (if resource busy, another session beat you to this step for 1 or more IDs, goto 1 again)
3. Do whatever on these IDs
4. update the locked flag back to null

You can then use your select for update skip locked statement since your locked flag is being maintained.

Personally, I don't like all the updates to flags (your solution may require them for whatever reason), so I'd probably just try to select the IDs I want to update (by whatever criteria) in each session:

select * from queuetest where ... for update skip locked;

For example (in reality, my criteria wouldn't be based on a list of ids, but queuetest table is overly simplistic):

• sess 1: select * from queuetest where id in (4,3) for update skip locked;

• sess 2: select * from queuetest where id in (4,3,2) for update skip locked;

Here sess1 would lock 4,3 and sess2 would lock only 2.

You cannot to my knowledge do a top-n or use group_by/order_by etc in the select for update statement, you'll get a ORA-02014.

Neckar

The point of a concurrent queue is to allow multiple clients to process records that match some criteria. The processing is more complex than just updating data in the row. Typical usage would be multiple clients trying to access rows with the exact same criteria specified. In your example, the first session would potentially lock all of the records, starving session 2. – Lepton

if 2 clients want to access and lock the exact same rows specified by the exact same criteria, then one would "win" (lock these rows) and the other would "lose" (get a resource busy). In my example, session 1 tries to lock 2 rows and succeeds in locking 2 rows, session 2 tries to lock 3 rows and succeeds in locking only 1. You can't have multiple session locking the same rows. – Neckar

@Travis: i think what you're missing in your OP example (for Oracle) is that you never set the "locked" field, but you try to use it in the subselect. For BOTH sessions, its the same as ... where id IN (4) ... because "SELECT ID FROM (SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority) WHERE ROWNUM = 1" will always return 4 if you aren't updating the locked flag in the table. Apparently SqlServer lets you do a top-n in a locking select, but Oracle won't allow a group function with select for update as far as I know, you'll get ORA-02014, so I tried to present alt – Neckar

The point is that I want each client to lock only a single row, not all of them. Yes, one will win at locking the first row, in which case I want the second to lock the next row (see SQL server example). The locked field is not relevant in the post, as I want to set it once I have attained the row lock in a transaction, this will allow it to be excluded from additional queries which come in later. The problem I am examining now is what happens before that (two clients attempt to attain access at the same time, before the Locked flag is set on any row). – Lepton

the locked field is relevant because its used in your subselect. If you want to use it, you need to set it (see steps 1-4 in my OP). If you don't want to use it, the next question is what is the criteria to select the group of IDs you wish to lock. The second part of my response (bullets) I choose a simple list of IDs for each session, but you may use a date field or whatever in your real table. You cannot to my knowledge use a top-n or similar group function for this subselect along with "for update". So, what's your criteria for choosing which IDs to lock? – Neckar

Yes, it is used in the subselect, but is still not relevant to the problem. Let me try to rephrase what I am trying to do: 1. In a transaction, select the next available record from a queue, using a rowlock to prevent concurrent selection of the same row. 2. In the same transaction, set the locked field on the selected row. 3. Commit the transaction. 4. Do whatever processing is necessary for the row. – Lepton

@Neckar let us continue this discussion in chat – Lepton

@Lepton what did you end up using? I'm in similar situation and asktom.oracle.com/pls/asktom/… shows that BULK COLLECT with LIMIT 1 can solve the problem but when I run the query in a multhreaded fashion (i may be doing it wrong) i still get dupes... When setting isolation level to SERIALIZABLE it appears to work but other threads updating the database causes some other threads to receive 08177 error (even though the threads have unique IDs being worked on... but elsewhere found it may be due to index updates causing the ORA error) – Houseboat

Answer 4

My solution - is to write stored procedure like this:

CREATE OR REPLACE FUNCTION selectQueue 
RETURN SYS_REFCURSOR
AS
  st_cursor SYS_REFCURSOR;
  rt_cursor SYS_REFCURSOR;
  i number(19, 0);

BEGIN

  open st_cursor for
  select id
  from my_queue_table
  for update skip locked;

  fetch st_cursor into i;
  close st_cursor;

  open rt_cursor for select i as id from dual;
  return  rt_cursor;

 END;

This is simple example - returning TOP FIRST non blocked row. To retrieve TOP N rows - replace single fetch into local variable ("i") with loop fetch into temp table.

PS: returning cursor - is for hibernate friendship.

Foreboding

Answer 5

I met this problem, we spend a lot of time to solve it. Some use for update for update skip locked, in oracle 12c, a new method is to use fetch first n rows only. But we use oracle 11g.

Finally, we try this method, and found works well.

CURSOR c_1 IS  
   SELECT *
     FROM QueueTest qt
     WHERE Locked IS NULL
     ORDER BY PRIORITY;
   myRow c_1%rowtype;
   i number（5）:=0;
   returnNum := 10;
BEGIN
  OPEN c_1;
  loop 
    FETCH c_1 into myRow 
    exit when c_1%notFOUND 
    exit when i>=returnNum;
    update QueueTest set Locked='myLock' where id=myrow.id and locked is null;
    i := i + sql%rowcount;
  END
  CLOSE c_1;
  commit;
END;

I wirte it in notepad, so something maybe wrong, you can modify it as a procedure or else.

Accordion

Answer 6

Firstly thanks for top 2 answers ..Learnt a lot from them.I have tested the following code and after running Practicedontdel.java main method ,I found that the two classes prints different rows every time. Please let me know if in any case this code might fail.(P.S : thanks to stack overflow)

Practicedontdel.java:

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs =null;
    String val="";
    int count =0;

        conn = getOracleConnection();
        conn.setAutoCommit(false);
        ps = prepareStatement(conn,"SELECT /*+FIRST_ROWS_3*/ t.* from 
        REPROCESS_QUEUE t FOR UPDATE SKIP LOCKED");
        ps.setFetchSize(3);
        boolean rss = ps.execute();
        rs = ps.getResultSet();
        new Practisethread().start();
        while(count<3 && rs.next())
        {
            val = rs.getString(1);
            System.out.println(val);
            count++;
            Thread.sleep(10000);
        }
       conn.commit();
            System.out.println("end of main program");

Practisethread.java: in run():

            conn = getOracleConnection();
            conn.setAutoCommit(false);
            ps = prepareStatement(conn,"SELECT /*+FIRST_ROWS_3*/ t.* from REPROCESS_QUEUE t FOR UPDATE SKIP LOCKED");
            ps.setFetchSize(3);
            boolean rss = ps.execute();
            rs = ps.getResultSet();
            while(count<3 && rs.next())
            {
                val = rs.getString(1);
                System.out.println("******thread******");
                System.out.println(val);
                count++;
                Thread.sleep(5000);
            }
            conn.commit();
            System.out.println("end of thread program");

Gower

Answer 7

Worked on similar task, here are my findings

SELECT BANNER_FULL FROM v$version
-- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
-- Version 19.8.0.0.0

Approach 1: Function with cursor

SQL

CREATE OR REPLACE FUNCTION lockRows RETURN SYS_REFCURSOR IS
    cur SYS_REFCURSOR;
BEGIN
    OPEN cur FOR select /*+ FIRST_ROWS(10) */ TASK_ID from QUEUE where <...> FOR UPDATE SKIP LOCKED;
    return cur;
END;

Java

CallableStatement cs = connection.prepareCall("{ ? = call lockRows }")
cs.setFetchSize(topN);
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet resultSet = (ResultSet) cs.getObject(1);
while(topN-- > 0 && resultSet.next()) {
  String taskId = resultSet.getString(TASK_ID);
  // update the row (where clause will not match it anymore)
}

Did not notice much change when removing FIRST_ROWS optimizer hint and setFetchSize.

Approach 2: Prepared Statement

Java

PreparedStatement ps = connection.prepareStatement(
"select /*+ FIRST_ROWS(10) */ TASK_ID from QUEUE where <...> FOR UPDATE SKIP LOCKED")
ps.setFetchSize(topN);
ps.execute();

ResultSet resultSet = ps.getResultSet();
while(topN-- > 0 && resultSet.next()) {
  String taskId = resultSet.getString(TASK_ID);
  // update the row (where clause will not match it anymore)
}

The test

1. Create 20,000 rows in the QUEUE table
2. Start multiple worker threads (> 10) that continuously process 1-40 records at a time until all 20,000 have been processed.

Record (log) all occurrences where count of rows returned is less than topN. These events are only noticed at the end - some workers getting less rows than they asked for.
If the prepared statement would indeed lock all rows in the table there would be a lot more events.

ROWNUM

If using ROWNUM with the PreparedStatement - where <...> and ROWNUM < topN - there are a lot of events where the workers get less rows than they ask for.

Conclusion

I coded the PreparedStatement implementation just to validate that it does not work - 1 worker would lock all rows (as hinted in other answers) leading to poor concurrency.
Given the findings I conclude the cursor is not needed, PreparedStatement works just as fine.

Uintathere