snippetModerate

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

canensureprocedureonerunningthathoworacleonlycopy

Problem

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?

Solution

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;.

Code Snippets

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;
/

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

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>

Context

StackExchange Database Administrators Q#24269, answer score: 13

Revisions (0)

No revisions yet.