How can I get the next n unlocked rows from Oracle?
Suppose an Oracle table books
stores information about n books with columns id
and title
. Some tuples are blocked in SELECT ... FOR UPDATE
.
Suppose these rows whose ids are in (1, 2, 4, 5, 6, 9) are locked.
Now I want to write SQL to achieve this by executing it return the following 2 records that are unlocked. And SQL can be called by multiple processes at the same time.
In other words, the first call returns id = 3 and id = 7 records; the second call will return id = 8 and id = 10 records.
I think it SELECT ... FOR UPDATE SKIP LOCKED
helps, it automatically skips lines that are locked and solved the problem with multiple processes at the same time. But how do you get the next 2 records? I don't think rownum works because I don't know which rows are locked.
Can anyone share their ideas? Many thanks!
source to share
In 12c, you can use row_limiting_clause documented here: http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
General syntax:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
Alternatively, use the Oracle Streams Advanced Queuing API described here: http://docs.oracle.com/database/121/ADQUE/aq_opers.htm#ADQUE2835
source to share
I'm not an oracle expert (or a user even;)), but could this work? (as suggested in this answer ):
SELECT * FROM MYTABLE FOR UPDATE SKIP LOCKED
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
source to share