RELEASE function - Releases an acquired lock

The RELEASE function releases an acquired lock.

Syntax

Read syntax diagramSkip visual syntax diagram DBMS_LOCK.RELEASE ( lock_id )
Read syntax diagramSkip visual syntax diagram DBMS_LOCK.RELEASE ( lockhandle )

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 - Already owned lock specified by 'lock_id' or 'lockhandle'
  • 5 - Illegal lock handle value

Authorization

EXECUTE privilege on the DBMS_LOCK module.

Example

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.CONVERT( LOCKHANDLE => HANDLE, LOCKMODE => DBMS_LOCK.NL_MODE );
  CALL DBMS_OUTPUT.PUT_LINE('STATUS: '||STATUS);
END
DB20000I  The SQL command completed successfully.
STATUS: 0 
STATUS: 0

SELECT LOCK_ID, LOCK_MODE, FLAG FROM SYSTOOLS.DBMS_LOCK_DET

LOCK_ID      LOCK_MODE FLAG
------------ --------- ----

  0 record(s) selected.