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