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

Minor

Efficiency, Reliability

Unsupported in PL/SQL Cop Validators

We cannot identify where an application lock would make sense. Algorithms to detect wrong, missing and right usages of this pattern are virtually impossible to implement without understanding the context.

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.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 -- 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; / 
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 /* 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; /