Concurrent reading and updating in a database table

Question

I have an Oracle database that I access using Devart and Entity Framework.

There's a table called IMPORTJOBS with a column STATUS.

I also have multiple processes running at the same time. They each read the first row in IMPORTJOBS that has status 'REGISTERED', put it to status 'EXECUTING', and if done put it to status 'EXECUTED'.

Now because these processes are running in parallel, I believe the following could happen:

• process A reads row 10 which has status REGISTERED,
• process B also reads row 10 which has still status REGISTERED,
• process A updates row 10 to status EXECUTING.

Process B should not be able to read row 10 as process A already read it and is going to update its status.

How should I solve this? Put read and update in a transaction? Or should I use some versioning approach or something else?

Thanks!

EDIT: thanks to the accepted answer I got it working and documented it here: http://ludwigstuyck.wordpress.com/2013/02/28/concurrent-reading-and-writing-in-an-oracle-database.

Answer 1

You should use the built-in locking mechanisms of the database. Don't reinvent the wheel, especially since RDBMS are designed to deal with concurrency and consistency.

In Oracle 11g, I suggest you use the SKIP LOCKED feature. For example each process could call a function like this (assuming id are number):

CREATE OR REPLACE TYPE tab_number IS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION reserve_jobs RETURN tab_number IS
   CURSOR c IS 
      SELECT id FROM IMPORTJOBS WHERE STATUS = 'REGISTERED'
      FOR UPDATE SKIP LOCKED;
   l_result tab_number := tab_number();
   l_id number;
BEGIN
   OPEN c;
   FOR i IN 1..10 LOOP
      FETCH c INTO l_id;
      EXIT WHEN c%NOTFOUND;
      l_result.extend;
      l_result(l_result.size) := l_id;
   END LOOP;
   CLOSE c;
   RETURN l_result;
END;

This will return 10 rows (if possible) that are not locked. These rows will be locked and the sessions will not block each other.

In 10g and before since Oracle returns consistent results, use FOR UPDATE wisely and you should not have the problem that you describe. For instance consider the following SELECT:

SELECT *
  FROM IMPORTJOBS 
 WHERE STATUS = 'REGISTERED'
   AND rownum <= 10
FOR UPDATE;

What would happen if all processes reserve their rows with this SELECT? How will that affect your scenario:

1. Session A gets 10 rows that are not processed.
2. Session B would get the same 10 rows, is blocked and waits for session A.
3. Session A updates the selected rows' statuses and commits its transaction.
4. Oracle will now (automatically) rerun Session B's select from the beginning since the data has been modified and we have specified FOR UPDATE (this clause forces Oracle to get the last version of the block).
   This means that session B will get 10 new rows.

So in this scenario, you have no consistency problem. Also, assuming that the transaction to request a row and change its status is fast, the concurrency impact will be light.