RELEASE function - Releases an acquired lock

The RELEASE function releases an acquired lock.

Syntax

Parameters

lock_id: An input argument of type DECIMAL(10) that specifies the user-generated ID of the lock that is requested.; Lock IDs are in the range of 0 - 1073741823.
lockhandle: An input argument of type VARCHAR(10) that specifies the handle of the lock that is requested.; Lock handles are in the range of 1073741824 - 1999999999.

Information returned

One of the following status codes as INTEGER:

0 - Success
3 - Parameter error
4 - Lock specified by lock_id or lockhandle is not owned by the current connection
5 - Illegal lock handle value

Authorization

EXECUTE privilege on the DBMS_LOCK module.

Usage notes

In Db2® 12.1.3 and later, DBMS_LOCK is implemented using Db2 lock instead of rows in the SYSTOOLS.DBMS_LOCK_DET table. Shared locking (lockmode = S_MODE) and transaction scope locking release_on_commit=TRUE) is also added. To use these new functionalities on a pre-12.1.3 database, you must first run db2updv121 against it with the -a option, to recreate the underlying module objects.

Example

The following example shows the command syntax for releasing an acquired lock.

Note: The monitoring query in the following example applies to Db2 12.1.3 and later.

BEGIN
  DECLARE STATUS DECIMAL(1);
  DECLARE HANDLE VARCHAR(128);
  CALL DBMS_LOCK.ALLOCATE_UNIQUE( 'LOCK5', HANDLE );
  SET STATUS = DBMS_LOCK.REQUEST( LOCKHANDLE => HANDLE, LOCKMODE =>DBMS_LOCK.X_MODE );
  SET STATUS = DBMS_LOCK.RELEASE( LOCKHANDLE => HANDLE);
  CALL DBMS_OUTPUT.PUT_LINE('STATUS: '||STATUS);
END
DB20000I  The SQL command completed successfully.
STATUS: 0 
STATUS: 0

    select 
      locks.lock_mode, 
      locks.lock_status,
      locks.lock_count,
      locks.application_handle,
      char(fmt.value,20) applockid
    from 
      table(mon_get_locks(null,-2)) locks,
      table(mon_format_lock_name(locks.lock_name)) fmt
    where
      locks.lock_object_type='APPLICATION' and
      fmt.name='APPLICATION_LOCK_ID' 
      
LOCK_MODE LOCK_STATUS APPLICATION_HANDLE   LOCK_COUNT           APPLOCKID           
--------- ----------- -------------------- -------------------- --------------------
     
0 record(s) selected.