Monday, November 07, 2016

DBMS_LOCK: How to ensure only one instance of a PL/SQL procedure can execute concurrently.

I recently had a need to ensure that only one instance of a PL/SQL procedure was running concurrently. My solution was to create a named lock with Oracle-supplied DBMS_LOCK package. There are lots of other blogs and posts on the internet on this subject, but this is how I used it.
Just as a row level lock will block another session from obtaining a lock on the same row, a session holding a named lock created with DBMS_LOCK can prevent another session obtaining a lock with the same name.
(updated 21.11.2016) An essential difference is that by default these locks will survive a commit or rollback.  Although you can choose to release them on commit.
In my package MY_PACKAGE, I have a procedure MY_PROCEDURE. I want to ensure that only a single instance of MY_PROCEDURE can run at any one time in my database.
CREATE OR REPLACE PACKAGE BODY my_package AS 
…
k_module     CONSTANT VARCHAR2(48 CHAR) := $$PLSQL_UNIT; --name of package for instrumentation
l_lockhandle VARCHAR2(128); -- lock handle for session lock object
…
PROCEDURE my_procedure
          (/*other parameters*/
          ,p_lock_timeout   INTEGER  DEFAULT 0 /*override lock timeout*/
) IS
  l_retcode      NUMBER;
…
BEGIN
  unlock;/*unlock any session lock from previous execution in this session*/
  DBMS_LOCK.ALLOCATE_UNIQUE(k_module,l_lockhandle); /*take out an exclusive lock*/
  l_retcode:=DBMS_LOCK.REQUEST(lockhandle=>l_lockhandle
                              ,lockmode=>DBMS_LOCK.x_mode
                              ,timeout=>p_lock_timeout
                              /*,release_on_commit=>FALSE by default*/
                              ); /*check we have the lock*/

  IF l_retcode<>0 THEN
    raise_application_error(-20000,k_module||'.MY_PROCEDURE is already running');
  END IF;
…
/*do rest of package*/unlock;
END my_procedure;
  • A package global constant is set to $$PLSQL_UNIT$$. This evaluates to the name of the package, in this case, MY_PACKAGE. I often use this during instrumentation to pass the name of the package to DBMS_APPLICATION_INFO. In this case, it will be used as the name of the lock.
  • I also have a package global variable L_LOCKHANDLE which will be used as the handle of the lock. The contents of this variable will be available to subsequent instances of the package in the same session.
  • A unique lock identifier is allocated with DBMS_LOCK.ALLOCATE_UNIQUE. The lock is available for the life of the session, up to a default timeout of 10 days. The name of the lock is the name of the package taken ultimately from $$PLSQL_UNIT$$.
  • The lock is acquired with the DBMS_LOCK.REQUEST function. It is possible to pass a timeout in section to this function for which it will wait if it cannot acquire the lock.
  • The lock is requested in exclusive mode.  Other lock modes are available, but I only want one session to be able to acquire this lock.  
  • If the request function returns a non-zero code then the lock has not been acquired and the package raises an exception with a custom message. 
ERROR at line 1:
ORA-20000: MY_PACKAGE.MY_PROCEDURE is already running
If the package failed with an error it would continue to hold the lock in the session. Restarting the procedure in the same session would generate an error because the session could not acquire the lock again. I put the code to release the lock in a separate procedure.  The lock handle is stored in a package global variable so the handle will be available from previous executions of the package in the same session.  The first and last thing MY_PROCEDURE does is to call the unlock procedure.
I also found it useful to be able to call the unlock procedure during debugging, so I exposed it in the package header.
PROCEDURE unlock IS
  l_retcode NUMBER;
…
BEGIN
…
IF l_lockhandle IS NULL THEN 
    debug_msg('no lock handle',9);
  ELSE
    l_retcode:=DBMS_LOCK.RELEASE(l_lockhandle); /*release lock*/
    debug_msg('dbms_lock.release('||l_lockhandle||') return code '||l_retcode,8);
    IF l_retcode = 0 THEN 
      debug_msg('Lock Released',2);
    ELSIF l_retcode = 3 THEN
      debug_msg('Parameter Error',8);
    ELSIF l_retcode = 4 THEN
      debug_msg('Do not own lock specified by lockhandle '||l_lockhandle,8);
    ELSIF l_retcode = 5 THEN
      debug_msg('Illegal lock handle '||l_lockhandle,8);
    END IF;
  END IF;
…
END unlock;
DBMS_LOCK is a simple and robust mechanism that can easily be incorporated into a package.  Even if you can control execution concurrency via whatever you use to schedule the package, it is a useful safety mechanism.

No comments :