G-8410: Always use application locks to ensure a program unit is 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)

 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
/* 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
   k_lock_name constant varchar2(30 char) := 'APPLICATION_LOCK';
begin
   lock_up.request_lock(in_lock_name => k_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;
/

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
/* good 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.k_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 errors.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 errors.e_lock_request_failed;
      end if;
   end release_lock;
end lock_up;
/

/* Call good example */
declare
   l_handle varchar2(128 char);
   k_lock_name constant varchar2(30 char) := 'APPLICATION_LOCK';
begin
   l_handle := lock_up.request_lock(in_lock_name => k_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;
/