selecting from same table with multiple sessions [duplicate]

This question already has answers here: Closed 11 years ago.

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

+--开发者_C百科-+---+
+ 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.

---

Instead of the SKIP LOCKED in your example, what you're actually asking for is READ UNCOMMITTED, or a 'dirty read' which Oracle's not keen on.

I'd have a read of this question/answer: Does oracle allow the uncommitted read option?