Possible Duplicate:
Force Oracle to return TOP N rows with SKIP LOCKED
I am experiencing some problems with FOR UPDATE clause on Oracle. What I want to do is select a number of values (say 1000, but this value might be changed by the user at runtime) from a table and process them. But, my application may run on multiple nodes and all of them will use the same table, so I have to lock the records to prevent same records to be fetched by multiple nodes.
To demonstrate it, lets create a sample table:
CREATE TABLE t (ID PRIMARY KEY) AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;
This is how I select the records. Here I tried to fetch 3 records.
SELECT rownum r, a.ID i
FROM (SELECT * FROM t ) a
WHERE rownum <= 3
FOR UPDATE skip locked
This query returns 3 records
+---+---+
+ R + I +
+---+---+
+ 1 + 1 +
+---+---+
+ 2 + 2 +
+---+---+
+ 3 + 3 +
+---+---+
When I ran the same query from another session (without commiting the session 1), I get an empty resultset. But what I really want is fetch the next 3 items (4, 5, 6 in this case). I understand that this is the expected behaviour, but cannot find an appropriate solution to it. How can I solve this without creation an Oracle procedure?
This came up before, and I gave a fairly detailed answer then:
Force Oracle to return TOP N rows with SKIP LOCKED
I cannot really think of another way (short of using a queue) than the information that is given in the thread above.