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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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;.
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.