How can I ensure that only one copy of a procedure is running in Oracle?

Question

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.

Whats the best method of doing this?

Answer 1

You can do this with DBMS_LOCK and an exclusive lock.

See the following procedure:

CREATE OR REPLACE PROCEDURE myproc
IS
  lockhandle VARCHAR2(128);
  retcode NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);

  retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode);

  IF retcode<>0
  THEN
    raise_application_error(-20000,'myproc is already running');
  END IF;

  /* sleep so that we can test with a 2nd execution */
  DBMS_LOCK.sleep(1000);

  retcode:=DBMS_LOCK.RELEASE(lockhandle);

END myproc;
/

Test (session 1):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /

(Obviously returns when DBMS_LOCK.sleep() returns).

Test (session 2):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-20000: myproc is already running
ORA-06512: at "PHIL.MYPROC", line 12
ORA-06512: at line 2


SQL>

Obviously you need to GRANT EXECUTE ON DBMS_LOCK TO YOURUSER;.

Answer 2

Use a 'lock' table.

When procedure starts check the table for a known value, if present go no further and exit proc. If not there, write the value to the table, execute procedure, then delete value and exit as normal.

Answer 3

When my clients have a request that has unique business logic like this one I try turning the question around and asking why this is needed.

Best way to make sure only one copy is running is not to let users have execute on the procedure at all. If this procedure is so special then it's use should be restricted to dba/developers.

Another way is to only run this procedure as a job. Add a check in the procedure to see if any jobs calling this are running. If they are then stop further processing and log the occurrence.