Geek asks and answers

Read and update in database table concurrently

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

Here is a table with a name IMPORTJOBSwith a column STATUS.

I also have several processes running at the same time. Each of them reads the first line in IMPORTJOBS, which has a status 'REGISTERED', puts it in a state 'EXECUTING'and, if this is done, puts it in a state 'EXECUTED'.

Now, since these processes work in parallel, I believe the following:

  • Process A reads a line 10 which has a status REGISTERED,
  • process B also reads line 10, which has the status REGISTERED,
  • Process Updates line 10 to status EXECUTING.

Process B should not read line 10 as a process. And already read it and is going to update the status.

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

Thank!

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

+5
source share
3 answers

You must use the built-in database locking mechanisms. Do not reinvent the wheel, especially since RDBMS are designed to work with concurrency and consistency.

Oracle 11g SKIP LOCKED. , ​​ (, id - ):

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;

10 ( ), . , .

10g , Oracle , FOR UPDATE , , . , SELECT:

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

, SELECT? :

  • A 10 , .
  • B 10 , A.
  • .
  • Oracle () B , , FOR UPDATE ( Oracle ).
    , B 10 .

, . , , , concurrency .

+2

SELECT ... FOR UPDATE, . A , B , A , ( ) . Oracle , B, B. , , , .

  • , , , ( , , , ).
  • Oracle 11.1 , SKIP LOCKED FOR UPDATE, , ( , , ).
  • ImportJobs . Oracle ( Oracle ).
+2

concurrency.

IMPORTJOBS , ConcurrencyMode= Fixed . , EF , timestamp : WHERE timestamp = xxxxx.

B , concurrency, .

I am from the background of an SQL server, and I do not know the equivalent of Oracle timestamp (or rowversion), but the idea is that it is a field that is automatically updated when the update is done for writing.

+1
source

More articles:


All Articles