Script categories

Tuesday, 10 December 2013

User defined locking with DBMS_LOCK

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.

 

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.