CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
monther zlatan
monther zlatan

Reputation: 156

How to manage concurrent access in an sql procedure

I have create an sql procedure in my oracle database that I will call later on on my c# code,

the main goal of this procedure is to loop over entries and make some treatment in a condition that every entry should be treated only once.

The body of the procedure looks like that.

FOR item IN
       ( SELECT * FROM
             (SELECT tab1.item1,tab1.item2,tab1.item3... 
               FROM tab1
               INNER JOIN tab2 ON ...condition...
               INNER JOIN TAB3 ON ...condition...

               WHERE ....main_condition=true;

               ORDER BY  tab1.item1
              )
          WHERE ROWNUM < in_param
        )
    LOOP

        .
        .
        .
        dbms_lock.sleep(4);
        .
        .
        .
        "set main_condition=false;" 
        commit;

    END LOOP;

What can I do in a way that when two user call this procedure at the same time, they get a different set of rows.

thank you.

Upvotes: 1

Views: 230

Answers (2)

APC
APC

Reputation: 146329

"a condition that every entry should be treated only once."

That sounds like a queue. The usual way of managing that would be to implement the queue with a SELECT … FOR UPDATE SKIP LOCKED cursor. There is a very important caveat here: that the treatment you refer to in your question include something which affects the initial criteria, say by updating a status value or deleting an identifying record (or whatever - unfortunately, questions which are vague about their specifics can only attract equally vague answers).

So it might lock something like this (obviously indicative code):

  cursor c_whatever is
     SELECT tab1.item1,tab1.item2,tab1.item3... 
           FROM tab1
           INNER JOIN tab2 ON ...condition...
           INNER JOIN TAB3 ON ...condition...
           WHERE tab1.main_condition = true     
           FOR UPDATE OF tab1.main_condition SKIP LOCKED ;

   begin
       open c_whatever;

       ….
       update tab1
       set tab1.main_condition = false
       where tab1.item1 = ….

       commit;

Pessimistic locking will prevent two sessions grabbing the same row. Updating the columns which feed WHERE clause will prevent the same record being treated more than once.

Upvotes: 2

XING
XING

Reputation: 9886

What can I do in a way that when two user call this procedure at the same time, they get a different set of rows.

You can modify your query and use ORDER BY DBMS_RANDOM.VALUE to get random rows. Hence you can modify your query as 

SELECT * FROM
             (SELECT tab1.item1,tab1.item2,tab1.item3... 
               FROM tab1
               INNER JOIN tab2 ON ...condition...
               INNER JOIN TAB3 ON ...condition...

               WHERE ....main_condition=true;

               ORDER BY  DBMS_RANDOM.VALUE

Upvotes: 1

Related Questions