How can I retrieve next n unlocked rows from Oracle?

Question

Suppose I have Oracle table books store n books info with columns id and title. Some of the tuples are locked by SELECT ... FOR UPDATE clause.

Suppose these rows whose id in (1, 2, 4, 5, 6, 9) are get locked.

Now I want to write a SQL to achieve that when execute it, return the next 2 records which are unlocked. And the SQL may be called by multiple process at same time.

That is to say, the first call will return id = 3 and id = 7 records; the second call will return id = 8 and id = 10 records.

I think SELECT ... FOR UPDATE SKIP LOCKED would help, it auto skips rows which are locked and solved the multiple process call at same time problem. But how to achieve get next 2 records? I don't think rownum works, because I do not know which rows are locked.

Could anybody share your ideas? Thanks a lot!

Have you proved that using a rownum predicate would not work? — David Aldridge, Jun 23 '15 at 11:30
@DavidAldridge Yes, indeed. Because you do not know which rows are locked, so cannot use `rownum` to restrict. — coderz, Jun 25 '15 at 15:39
that sounds like a deduction rather than an actual test, though. I didn't see any documentation that said that rownum and skip locked wouldn't work. — David Aldridge, Jun 25 '15 at 20:22
"skip locked" is applied after the rownum filter, so if you supplied a rownum which was locked, the query would return 0 rows unless a wait clause was specified. — mancini0, Apr 10 '17 at 23:34

score 1 · Answer 1 · answered Jun 23 '15 at 11:34

In 12c you can use the row_limiting_clause, documented here: http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702

The general syntax is:

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Alternatively, use the Oracle Streams Advanced Queuing API documented here: http://docs.oracle.com/database/121/ADQUE/aq_opers.htm#ADQUE2835

score -2 · Answer 2 · edited May 23 '17 at 12:02

-2

I'm no oracle expert (or user even ;)) but might this work? (as suggested in this answer):

SELECT * FROM  MYTABLE FOR UPDATE SKIP LOCKED        
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

edited May 23 '17 at 12:02

Community

1
1

answered Jun 23 '15 at 11:11

Sam Holder

32,535
13
101
181

How can I retrieve next n unlocked rows from Oracle?

2 Answers2

Linked