Select from table in Oracle with multiple sessions

Question

I have multiple threads that are processing rows from the same table that is - in fact - a queue.

I want that each row will be handled by one thread only. So, I've added a column - "IsInProccess", and in the threads SELECT statement I've added "WHERE IsInProccess = 0". In addition I use "SELECT FOR UPDATE" so after a thread get a row from the table no other thread will get it before it puts 1 in "IsInProccess".

The problem is that I have many threads and in many times the following scenario happens: Thread A selecting by "SELECT FOR UPDATE" form the table and getting row no. 1. Before it changes IsInProccess to 1, thread B selects in the same way from the table and get row no 1 too. Oracle save row no. 1 to thread A session and thread B can't change this row and return an error - "Fail to be fetched".

I want that when some thread select from the table Oracle will return rows that are no saved to other open session.

Can I do that?

Answer 1

One solution:

1. Put the select and update queries into a transaction. This sort of problem is exactly why transactions were invented.

2. You also need to worry about "orphan" rows - e.g. a thread picks up the row and then dies without finishing the work. To solve that, one solution is to have 2 columns: "IsInProcess" and "StartprocessingTime".

   isInProcess will have 3 values: 0 (not processed), 1 (picked up), 2 (done).

   The original transaction will set the row's "isInProcess" to 1 and "StartprocessingTime" to something else, but the select will also add this to where clause (assuming you can specify a valid timeout period)

   "WHERE isInProcess = 0 OR (isInProcess = 1 AND StartprocessingTime < getdate()-timeout)".

   Please note that the syntax above is not ORACLE, just pseudo-code.

Answer 2

This is a sketch of a solution that I've seen used very successfully before:

1. Use the SELECT FOR UPDATE NOWAIT syntax so that if the session cannot get a lock on a row immediately it raises an exception instead of waiting for the lock. The exception handler could wait a few seconds (e.g. with dbms_lock.sleep); the whole block could be wrapped in a loop which tries again a certain number of times before it gives up.

2. Add WHERE ROWNUM<=n to the query so that it only tries to get a certain number of rows (e.g. 1) at a time; if it succeeds, update the rows as "in process".

3. A good way to mark rows as "in process" (that I've seen used successfully) is to have two columns - SID and SERIAL# - and update these columns with the SID and SERIAL# for the current session.

4. In case a session fails while it has the rows marked as "in process", another process could "clean up" the rows that were marked as "in process" by searching for any rows that have SID/SERIAL# that are not found as active sessions in v$session.

Answer 3

Oracle have already solved this for you: use the Advanced Queueing API

Answer 4

If you have 11g, look at SKIP LOCKED It is there, but undocumented (and therefore unsupported and maybe buggy) in 10g. That way, when Session A locks the row, Session B can skip it and process the next.