4

Some PL/SQL procedure take a lot of time for evaluation (as recalculate and update a lot of data).

I want to limit numbers of execution context which evaluate this procedure to single.

Just do nothing if some another context execute it...

But I don't know which atomic operations to use in PL/SQL code.

Note: I afraid that using table row for synchronization can lead to inconsistent state in DB if server halted on middle of procedure evaluation...

gavenkoa
  • 45,285
  • 19
  • 251
  • 303
  • Also http://stackoverflow.com/questions/1053484/block-procedure-pl-sql-with-oracle – gavenkoa Jun 26 '13 at 13:54
  • 1
    Take a look at DBMS_LOCK. You can create your owned name lock, and then obtain the lock in your PL/SQL package. If another instance of the package execution runs, the lock call will fail. This is much more efficient and less kludgy than locking tables or rows in a table. – OldProgrammer Jun 26 '13 at 14:05

1 Answers1

9

You can use DBMS_LOCK.request to generate a unique lock handle. Only one session can hold this lock at the same time. If the database restarts of the session ends unexpectedly, the lock will be released automatically.

You decide when requesting the lock if the lock will be held across commits or not.

Here's an example:

SQL> CREATE OR REPLACE PROCEDURE serial IS
  2     l_lock_handle  VARCHAR2(128 BYTE);
  3     l_lock_request INTEGER;
  4  BEGIN
  5     dbms_lock.allocate_unique(lockname => 'MY_SERIAL_PROC',
  6                               lockhandle => l_lock_handle);
  7     l_lock_request := dbms_lock.request(lockhandle => l_lock_handle,
  8                                         timeout => 5,
  9                                         release_on_commit => FALSE);
 10     CASE l_lock_request
 11        WHEN 0 THEN
 12           NULL; -- success
 13        WHEN 1 THEN
 14           raise_application_error(-20002, 'lock already reserved');
 15        ELSE
 16           raise_application_error(-20001, 'Lock error: ' || l_lock_request);
 17     END CASE;
 18     BEGIN
 19        ---------- serialized block of code           ----------
 20        ---------- (lock will be kept accross commit) ----------
 21        dbms_lock.sleep(30);
 22        ---------- End of serialized code             ----------
 23     EXCEPTION
 24        WHEN OTHERS THEN -- release lock in case of uncatched error
 25           l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 26           RAISE;
 27     END;
 28     l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 29  END;
 30  /

Procedure created

I'll run two sessions at the same time:

Session A> exec serial;                

                                       Session B> -- Before session A ends
                                       Session B> exec serial;

                                       ERROR at line 1:
                                       ORA-20002: lock already reserved
                                       ORA-06512: at "APPS.SERIAL", line 13
                                       ORA-06512: at line 1


PL/SQL procedure successfully completed

                                       Session B> -- After session A ends
                                       Session B> exec serial;

                                       PL/SQL procedure successfully completed.
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • +1 nice ! did not know that one. i'm guessing that if the block fails, the transaction automatically rolls back and the lock gets released ? – haki Jun 26 '13 at 14:34
  • 3
    @haki: Nope, the transaction will not be rolled back, nor the lock released! I should probably add a release in a "finally" block because that would be the expected behaviour you're right. – Vincent Malgrat Jun 26 '13 at 14:51
  • 1
    Thanks - learned something. maybe edit the post with `exception when others` clause to release the lock. – haki Jun 26 '13 at 14:53