REQUEST function - Requests a lock based on the lock ID or lock handle

The REQUEST function requests a lock based on the specified lock ID or specified lock handle.

Description

The lock handle is a value that is generated by the ALLOCATE_UNIQUE procedure. The lock ID is a user-generated value that identifies the lock that is requested.

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.
lockmode: An input argument of type DECIMAL(1) that specifies the requested lock mode.; Only NL_MODE (null) and X_MODE (exclusive) lock modes are supported.
timeout: An input argument of type DECIMAL(5) that specifies how long the session waits to acquire the lock before the operation times out.; The default value is 32,767 seconds.
release_on_commit: An input argument of type BOOLEAN that specifies whether the lock is to be released on commit or rollback.; Only 'FALSE' is supported. If you specify 'TRUE', the operation fails, and the status code 3 (parameter error) is returned.; The default value is 'FALSE'.

Information returned

One of the following status codes as INTEGER:

0 - Success
1 - Timeout
2 - Deadlock
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.

SELECT LOCK_ID, LOCK_MODE, FLAG FROM SYSTOOLS.DBMS_LOCK_DET

LOCK_ID      LOCK_MODE FLAG
------------ --------- ----
 1073741825.        6. H   

  1 record(s) selected.