User defined locks with DBMS_LOCKS often brings up unexpected results, especially when it occurs within a mix of transactions in an application layer. There are essentially three procedures for creating user defined locks,
namely: DBMS_LOCK.REQUEST, DBMS_LOCK.ALLOCATE_UNIQUE and
DBMS_LOCK.RELEASE. ALLOCATE_UNIQUE issues an implicit commit. The four anonymous blocks below highlight what is expected for the LOCK STATUS, whenever a REQUEST and RELEASE are performed, versus the actual value. All scripts were tested in an 11.2.0.3 release.
The main source of confusion arises from the use of the RELEASE_ON_COMMIT parameter coupled with the fact that an implicit COMMIT that occurs when calling DBMS_LOCK.ALLOCATE_UNIQUE. Some developers attempt to use PRAGMA AUTONOMOUS_TRANSACTION to skirt around the issue, which often masks the source of the underlying issue. Four scenarios in the form of scripts highlight the unexpected results that can occur.
Possible values for LOCK STATUS include:
0 - Success
3 - Parameter error
4 - Lock not owned
5 - Illegal lock handle
Scenario 1
Use an explicit lock ID as a handle, i.e. not generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE.
SQL> set serveroutput on
SQL> declare
2 lock_id number := 34789;
3 l_Lock_Status number;
4 b_release_on_commit boolean := true;
5 begin
6 l_Lock_Status:= dbms_lock.request
7 (id =>lock_id
8 ,lockmode =>dbms_lock.x_mode
9 ,release_on_commit=>b_release_on_commit);
10
11 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
12
13 l_Lock_Status:= dbms_lock.request
14 (id =>lock_id
15 ,lockmode =>dbms_lock.x_mode
16 ,release_on_commit=>b_release_on_commit );
17
18 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
19
20 l_Lock_Status:= dbms_lock.release(id => lock_id);
21 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
22
23 l_Lock_Status:= dbms_lock.release(id => lock_id );
24 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
25
26 end;
27 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Exactly as expected, all values match, so far so good :-)
Scenario 2
Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE. In addition, ensure an explicit RELEASE occurs.SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 begin
7 dbms_lock.allocate_unique('Lock 1',l_Lock_Handle);
8
9 l_Lock_Status:= dbms_lock.request
10 (lockhandle =>l_Lock_Handle
11 ,lockmode =>dbms_lock.x_mode
12 ,release_on_commit=>b_release_on_commit);
13
14 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
15
16 l_Lock_Status:= dbms_lock.request
17 (lockhandle => l_Lock_Handle
18 ,lockmode => dbms_lock.x_mode
19 ,release_on_commit=> b_release_on_commit);
20
21 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
22 dbms_lock.allocate_unique('Lock 1', l_Lock_Handle);
23
24 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
25 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
26
27 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
28 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
29
30 end;
31 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 4
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
As can be seen from values on the third line, expected is 0, but actual is 4!!! The root cause of the problem is that an implicit COMMIT occurs when calling DBMS_LOCK.ALLOCATE_UNIQUE. The implicit COMMIT coupled with RELEASE_ON_COMMIT set to TRUE causes the ownership of the lock to change.
Use the following useful query to identify locks requested, but not yet expired:
select * from dba_lock dl
join dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
where dla.name like 'Lock 1';
Use as follows for scenario 3:
SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 ty_Lock_Rec dba_lock%rowtype;
7
8 l_Lock_Name varchar2(10) := 'Lock 3';
9
10 begin
11 dbms_lock.allocate_unique(l_Lock_Name,l_Lock_Handle);
12
13 l_Lock_Status:= dbms_lock.request
14 (lockhandle =>l_Lock_Handle
15 ,lockmode =>dbms_lock.x_mode
16 ,release_on_commit=>b_Release_On_Commit);
17
18 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
19
20 l_Lock_Status:= dbms_lock.request
21 (lockhandle => l_Lock_Handle
22 ,lockmode => dbms_lock.x_mode
23 ,release_on_commit=> b_Release_On_Commit);
24
25 begin
26 select dl.session_id
27 ,dl.lock_type
28 ,dl.mode_held
29 ,dl.mode_requested
30 ,dl.lock_id1
31 ,dl.lock_id2
32 ,dl.last_convert
33 ,dl.blocking_others
34 into
35 ty_Lock_Rec
36 from
37 dba_lock dl
38 join
39 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
40 where dla.name like l_Lock_Name;
41
42 dbms_output.put_line('Session ID :' || ty_Lock_Rec.Session_ID);
43 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
44 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
45 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
46 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
47 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
48 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
49 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
50
51 exception
52 when no_data_found then
53 dbms_output.put_line('No data exists in DBA_LOCK');
54 end;
55
56 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
57 dbms_lock.allocate_unique(l_Lock_Name, l_Lock_Handle);
58
59 begin
60 select dl.session_id
61 ,dl.lock_type
62 ,dl.mode_held
63 ,dl.mode_requested
64 ,dl.lock_id1
65 ,dl.lock_id2
66 ,dl.last_convert
67 ,dl.blocking_others
68 into
69 ty_Lock_Rec
70 from
71 dba_lock dl
72 join
73 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
74 where dla.name like l_Lock_Name;
75
76 dbms_output.put_line('Session ID : ' || ty_Lock_Rec.Session_ID);
77 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
78 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
79 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
80 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
81 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
82 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
83 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
84
85 exception
86 when no_data_found then
87 dbms_output.put_line('No data exists in DBA_LOCK');
88 end;
89
90 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
91
92 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
93
94
95 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
96 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
97
98 end;
99 /
Expect 0. Actual: 0
Session ID :159
Lock type :PL/SQL User Lock
Mode held :Exclusive
Mode requested :None
Lock ID 1 :1073742997
Lock ID 2 :0
Last Convert :0
Blocking Others:Not Blocking
Expect 4. Actual: 4
No data exists in DBA_LOCK
Expect 0. Actual: 4
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Use the following useful query to identify locks requested, but not yet expired:
select * from dba_lock dl
join dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
where dla.name like 'Lock 1';
Use as follows for scenario 3:
SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 ty_Lock_Rec dba_lock%rowtype;
7
8 l_Lock_Name varchar2(10) := 'Lock 3';
9
10 begin
11 dbms_lock.allocate_unique(l_Lock_Name,l_Lock_Handle);
12
13 l_Lock_Status:= dbms_lock.request
14 (lockhandle =>l_Lock_Handle
15 ,lockmode =>dbms_lock.x_mode
16 ,release_on_commit=>b_Release_On_Commit);
17
18 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
19
20 l_Lock_Status:= dbms_lock.request
21 (lockhandle => l_Lock_Handle
22 ,lockmode => dbms_lock.x_mode
23 ,release_on_commit=> b_Release_On_Commit);
24
25 begin
26 select dl.session_id
27 ,dl.lock_type
28 ,dl.mode_held
29 ,dl.mode_requested
30 ,dl.lock_id1
31 ,dl.lock_id2
32 ,dl.last_convert
33 ,dl.blocking_others
34 into
35 ty_Lock_Rec
36 from
37 dba_lock dl
38 join
39 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
40 where dla.name like l_Lock_Name;
41
42 dbms_output.put_line('Session ID :' || ty_Lock_Rec.Session_ID);
43 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
44 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
45 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
46 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
47 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
48 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
49 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
50
51 exception
52 when no_data_found then
53 dbms_output.put_line('No data exists in DBA_LOCK');
54 end;
55
56 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
57 dbms_lock.allocate_unique(l_Lock_Name, l_Lock_Handle);
58
59 begin
60 select dl.session_id
61 ,dl.lock_type
62 ,dl.mode_held
63 ,dl.mode_requested
64 ,dl.lock_id1
65 ,dl.lock_id2
66 ,dl.last_convert
67 ,dl.blocking_others
68 into
69 ty_Lock_Rec
70 from
71 dba_lock dl
72 join
73 dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
74 where dla.name like l_Lock_Name;
75
76 dbms_output.put_line('Session ID : ' || ty_Lock_Rec.Session_ID);
77 dbms_output.put_line('Lock type :' || ty_Lock_Rec.Lock_Type);
78 dbms_output.put_line('Mode held :' || ty_Lock_Rec.Mode_Held);
79 dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
80 dbms_output.put_line('Lock ID 1 :' || ty_Lock_Rec.lock_id1);
81 dbms_output.put_line('Lock ID 2 :' || ty_Lock_Rec.lock_id2);
82 dbms_output.put_line('Last Convert :' || ty_Lock_Rec.last_convert);
83 dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
84
85 exception
86 when no_data_found then
87 dbms_output.put_line('No data exists in DBA_LOCK');
88 end;
89
90 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
91
92 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
93
94
95 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
96 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
97
98 end;
99 /
Expect 0. Actual: 0
Session ID :159
Lock type :PL/SQL User Lock
Mode held :Exclusive
Mode requested :None
Lock ID 1 :1073742997
Lock ID 2 :0
Last Convert :0
Blocking Others:Not Blocking
Expect 4. Actual: 4
No data exists in DBA_LOCK
Expect 0. Actual: 4
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Scenario 3
Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to FALSE. Perform an explicit RELEASE.SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := false;
4 l_Lock_Handle varchar2(128 byte);
5
6 begin
7 dbms_lock.allocate_unique('Lock 1',l_Lock_Handle);
8
9 l_Lock_Status:= dbms_lock.request
10 (lockhandle => l_Lock_Handle
11 ,lockmode => dbms_lock.x_mode
12 ,release_on_commit=> b_release_on_commit);
13
14 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
15
16 l_Lock_Status:= dbms_lock.request
17 (lockhandle => l_Lock_Handle
18 ,lockmode => dbms_lock.x_mode
19 ,release_on_commit=> b_release_on_commit);
20
21 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
22 dbms_lock.allocate_unique('Lock 1', l_Lock_Handle);
23
24 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
25 dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
26
27 l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
28 dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
29
30 end;
31 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
Setting REALEASE_ON_COMMIT to FALSE resolves the problem. The implicit COMMIT does not effect the explicit release so results will match. In some cases developers include a PRAGMA AUTONOMOUS_TRANSACTION when calling ALLOCATE_UNIQUE to hide the implicit COMMIT. Although this does seem to work, this can have even more unexpected results and should be avoided.
Scenario 4
Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE. Perform an explicit RELEASE. Wrap the ALLOCATE_UNIQUE in a PRAGMA AUTONOMOUS TRANSACTION.
SQL> set serveroutput on
SQL> declare
2 l_Lock_Status number;
3 b_Release_On_Commit boolean := true;
4 l_Lock_Handle varchar2(128 byte);
5
6 function Get_Lock(p_Lock_In in varchar2) return varchar2
7 as
8 pragma autonomous_transaction;
9 v_lock_handle varchar2(128 byte);
10 begin
11 dbms_lock.allocate_unique(lockname =>p_Lock_In
12 ,lockhandle=>v_lock_handle);
13 return v_lock_handle;
14 end Get_Lock;
15
16 begin
17
18 l_Lock_Handle := Get_lock('Lock 1');
19
20 l_Lock_Status:= dbms_lock.request
21 (lockhandle => l_Lock_Handle
22 ,lockmode => dbms_lock.x_mode
23 ,release_on_commit => b_release_on_commit);
24
25 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
26
27 l_Lock_Status:= dbms_lock.request
28 (lockhandle => l_Lock_Handle
29 ,lockmode => dbms_lock.x_mode
30 ,release_on_commit => b_release_on_commit);
31
32 dbms_output.put_line('Expect 4. Actual: '|| l_Lock_Status);
33 l_Lock_Handle := Get_Lock('Lock 1');
34
35 l_Lock_Status:=dbms_lock.release(lockhandle=>l_Lock_Handle);
36 dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
37
38 l_Lock_Status:=dbms_lock.release(lockhandle=>l_Lock_Handle);
39 dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
40
41 end;
42 /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4
PL/SQL procedure successfully completed.
