G-8410

🆓

Error

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

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

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

Non-Compliant Example

/* 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
      co_lock_name constant app_locks.lock_name%type := in_lock_name;
   begin
      -- raises dup_val_on_index
      insert into app_locks (lock_name) values (co_lock_name);
   end request_lock;

   procedure release_lock(in_lock_name in varchar2) is
      co_lock_name constant app_locks.lock_name%type := in_lock_name;
   begin
      delete from app_locks where lock_name = co_lock_name;
   end release_lock;
end lock_up;
/

/* Call bad example */
declare
   co_lock_name constant app_locks.lock_name%type := '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 dup_val_on_index then
      -- expected exception
      lock_up.release_lock(in_lock_name => co_lock_name);
      raise;
   when others then
      -- unexpected exception, logging is recommended
      lock_up.release_lock(in_lock_name => co_lock_name);
      raise;
end;
/

Issues

Line	Column	Message
12	19	Acquiring application lock via insert statement.
18	19	Releasing application lock via delete statement.

Compliant Solution - ★★★★★

/* Example */
create or replace package body lock_up is
   function request_lock( -- dbLinter ignore(G-7460) non-deterministic function
      in_lock_name         in varchar2
     ,in_release_on_commit in boolean default false
   )
      return varchar2 is
      co_lock_name         constant type_up.lock_name := in_lock_name;
      co_release_on_commit constant boolean           := in_release_on_commit;
      l_lock_handle        type_up.lock_handle;
   begin
      sys.dbms_lock.allocate_unique(
         lockname        => co_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 => co_release_on_commit
         ) > 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
      co_lock_type constant type_up.lock_handle := in_lock_handle;
   begin
      if sys.dbms_lock.release(lockhandle => co_lock_type) > 0 then
         raise err.e_lock_request_failed;
      end if;
   end release_lock;
end lock_up;
/

/* Call good example */
declare
   l_handle     type_up.lock_handle;
   co_lock_name constant type_up.lock_name := '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 err.e_lock_request_failed then
      -- expected exception
      lock_up.release_lock(in_lock_name => co_lock_name);
      raise;
   when others then
      -- unexpected exception, logging is recommended
      lock_up.release_lock(in_lock_name => co_lock_name);
      raise;
end;
/

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
LockTableNamePattern	Regular expression pattern for application lock tables.	(?i)^.+(_lock).*$

References

same as Trivadis G-8410