G-8410: Always use application locks to ensure a program unit is 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)

-- Bad
/* Example */
CREATE OR REPLACE PACKAGE BODY lock_up IS
   -- manage locks in a dedicated table created as follows:
   --   CREATE TABLE app_locks (
   --      lock_name VARCHAR2(128 CHAR) NOT NULL primary key
   --   );

   PROCEDURE request_lock (in_lock_name IN VARCHAR2) IS
   BEGIN
      -- raises dup_val_on_index
      INSERT INTO app_locks (lock_name) VALUES (in_lock_name);
   END request_lock;

   PROCEDURE release_lock(in_lock_name IN VARCHAR2) IS
   BEGIN
      DELETE FROM app_locks WHERE lock_name = in_lock_name;
   END release_lock;
END lock_up;
/

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

Example (good)

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

/* Call good example */
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;
/