Atomic SELECT and UPDATE

Question

If my data looks like this:

ID STATUS     DATE_ADDED
== ========== ==========
 1 Processing 2011-04-01
 2 New        2011-04-02 
 3 New        2011-04-03
 4 Processing 2011-04-03
 5 Done       2011-04-06
 6 New        2011-04-06
 7 New        2011-04-14
 8 Done       2011-04-14
 ...

... what's the recommended way to pick the 10 oldest records with status "New" and set their status to "Processing" while ensuring that any other concurrent process cannot do the same with the same records?

It's a web application running on PHP/5.2.6 under Windows Server 2003 that connects to a remote Oracle 10g server through ODBC (Oracle's driver, not Microsoft's).

Answer 1

This is hard to do in Oracle 10g. In 11g, the SELECT FOR UPDATE ... SKIP LOCKED syntax makes it easy.

A simple UPDATE statement will serialize. As will a SELECT FOR UPDATE. Sure, two competing processes won't ever get the same rows; the problem is that they will at best serialize, and at worst, they can deadlock.

The recommended way would be to use Oracle Advanced Queueing (or the queuing implementation of your choice) to enqueue to the IDs to be processed, and allow the queueing implementation to manage the contention for values.

--

The SQL will work, but will fail with an ORA-00054 if a second user runs it for the same offsets while someone has that range locked. That can be mitigated by wrapping the select in a loop, catching the ORA-00054 error and using that to increment the offsets.

select * from my_table
 where rowid in 
       (select row_id 
          from (select rowid as row_id, rownum as rn 
                  from mytable where some_condition 
                 order by deterministic_sort_order)
         where rn between :low_rn and :hi_rn
       )
 for update nowait;

The sort expression needs to be deterministic (simply, include the primary key as the end of the sort expression) to prevent collisions.

Answer 2

Use a transaction to do that. Using the isolation level "serializable" for the transaction will prevent any other process to access/modify the rows while your transaction is working on them.

If a serializable transaction tries to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails.

You may want to use:

set transaction isolation level serializable;