G-8410: Always use application locks to ensure a program unit is only running once at a given time.
Blocker
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
36
37
38
39
40 | /* 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;
/
|
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
50
51
52
53
54
55
56
57 | /* Example */
create or replace package body lock_up is
function request_lock( -- NOSONAR: non-deterministic
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;
/
|