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