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.
-- Bad/* Example */createorreplacepackagebodylock_upis-- manage locks in a dedicated table created as follows:-- CREATE TABLE app_locks (-- lock_name VARCHAR2(128 CHAR) NOT NULL primary key-- );procedurerequest_lock(in_lock_nameinvarchar2)isbegin-- raises dup_val_on_indexinsertintoapp_locks(lock_name)values(in_lock_name);endrequest_lock;procedurerelease_lock(in_lock_nameinvarchar2)isbegindeletefromapp_lockswherelock_name=in_lock_name;endrelease_lock;endlock_up;//* Call bad example */declareco_lock_nameconstantvarchar2(30char):='APPLICATION_LOCK';beginlock_up.request_lock(in_lock_name=>co_lock_name);-- processinglock_up.release_lock(in_lock_name=>co_lock_name);exceptionwhenothersthen-- log errorlock_up.release_lock(in_lock_name=>co_lock_name);raise;end;/
/* Example */createorreplacepackagebodylock_upisfunctionrequest_lock(in_lock_nameinvarchar2,in_release_on_commitinboolean:=false)returnvarchar2isl_lock_handlevarchar2(128char);beginsys.dbms_lock.allocate_unique(lockname=>in_lock_name,lockhandle=>l_lock_handle,expiration_secs=>constants_up.co_one_week);ifsys.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))>0thenraiseerr.e_lock_request_failed;endif;returnl_lock_handle;endrequest_lock;procedurerelease_lock(in_lock_handleinvarchar2)isbeginifsys.dbms_lock.release(lockhandle=>in_lock_handle)>0thenraiseerr.e_lock_request_failed;endif;endrelease_lock;endlock_up;//* Call good example */declarel_handlevarchar2(128char);co_lock_nameconstantvarchar2(30char):='APPLICATION_LOCK';beginl_handle:=lock_up.request_lock(in_lock_name=>co_lock_name);-- processinglock_up.release_lock(in_lock_handle=>l_handle);exceptionwhenothersthen-- log errorlock_up.release_lock(in_lock_handle=>l_handle);raise;end;/