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 HANDLE VARCHAR(128);
DECLARE STATUS DECIMAL(1);
CALL DBMS_LOCK.ALLOCATE_UNIQUE( 'LOCK5', HANDLE );
SET STATUS = DBMS_LOCK.REQUEST( LOCKHANDLE => HANDLE, LOCKMODE =>
DBMS_LOCK.X_MODE );
CALL DBMS_OUTPUT.PUT_LINE('STATUS: '||STATUS);
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 LOCK_ID, LOCK_MODE, FLAG FROM SYSTOOLS.DBMS_LOCK_DET"

LOCK_ID LOCK_MODE FLAG

0 record(s) selected.