Skip to content

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

Minor

Efficiency, Reliability

Unsupported in db* CODECOP 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.

Example (bad)

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

Example (good)

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