G-8410: Always use application locks to ensure a program unit only running once at a given time.

Minor

Efficiency, Reliability

Reason

This technique allows us to have locks across transactions as well as a proven way to clean up at the end of the session.

The alternative using a table where a “Lock-Row” is stored has the disadvantage that in case of an error a proper cleanup has to be done to “unlock” the program unit.

Example (bad)

CREATE OR REPLACE PACKAGE BODY lock_up IS
   FUNCTION request_lock (in_lock_name         IN VARCHAR2
                         ,in_release_on_commit IN BOOLEAN := FALSE) RETURN VARCHAR2 IS
      l_lock_handle VARCHAR2(128 CHAR);
   BEGIN
      SYS.DBMS_LOCK.ALLOCATE_UNIQUE (lockname        => in_lock_name
                                    ,lockhandle      => l_lock_handle
                                    ,expiration_secs => constants_up.co_one_week); 

      IF sys.DBMS_LOCK.REQUEST(lockhandle        => l_lock_handle
                              ,lockmode          => sys.DBMS_LOCK.X_MODE
                              ,TIMEOUT           => sys.DBMS_LOCK.MAXWAIT
                              ,release_on_commit => COALESCE(in_release_on_commit
                                                            ,FALSE)) > 0 THEN
         RAISE err.e_lock_request_failed;
      END IF;

      RETURN l_lock_handle;
   END request_lock;

   PROCEDURE release_lock (in_lock_handle IN VARCHAR2) IS
   BEGIN
     IF sys.DBMS_LOCK.RELEASE(lockhandle => in_lock_handle) > 0 THEN
         RAISE err.e_lock_request_failed;
     END IF;
   END release_lock;
END lock_up;
/

Example (good)

DECLARE
   l_handle VARCHAR2(128 CHAR);
   co_lock_name CONSTANT VARCHAR2(30 CHAR) := 'APPLICATION_LOCK';
BEGIN
   l_handle := lock_up.request_lock(in_lock_name => co_lock_name);
   -- processing
   lock_up.release_lock(in_lock_handle => l_handle);
EXCEPTION
   WHEN OTHERS THEN
      -- log error
      lock_up.release_lock(in_lock_handle => l_handle);
      RAISE;
END;
/