Navigate

DBMS_LOCK PACKAGE In Oracle

DBMS_LOCK PACKAGE In Oracle

INTRODUCTION:

This package is used for managing lock operation in PLSQL:

  • LOCK
  • CONVERSION
  • RELEASE

SUBPROGRAMS OF DBMS_LOCK PACKAGE:

  • ALLOCATE_UNIQUE PROCEDURE
  • REQUEST FUNCTION
  • CONVERT FUNCTION
  • RELEASE FUNCTION
  • SLEEP PROCEDURE
ALLOCATE_UNIQUE PROCEDURE:
  • The ALLOCATE_UNIQUE procedure returns a unique handle to a lock specified by the lockname parameter.
  • This handle can be used to identify the locks in call to other DBMS_LOCK programs.
SYNTAX:
--DBMS_LOCK PACKAGE In Oracle: DBMS_LOCK.ALLOCATE_UNIQUE

PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE(LOCKNAME IN VARCHAR2,
                                    LOCKHANDLE OUT VARCHAR2,
                                    EXPIRATION_SECS IN INTEGER DEFAULT                                    
                                    86400
                                   );
  • LOCKNAME –> Name of the lock for which you want to generate an unique id.
  • LOCKHANDLE –> Unique handle to lock by name
  • EXPIRATION_SECS –> Length of time to leave the lock allocated.
RESTRICTIONS:
  • Locknames can be 128 characters long and are case-sensitive.
  • Locknames must not start with "ORA$" as these are reserved for oracle
  • ALLOCATE_UNIQUE procedure always perform a commit so it cannot be called from TRIGGER.
--DBMS_LOCK PACKAGE In Oracle: Example

CREATE OR REPLACE PACKAGE EMPLOYEE_ACCESS
IS
  FUNCTION GET_EMP_LOCK_HANDLE RETURN VARCHAR2;
END EMPLOYEE_ACCESS;
/
--DBMS_LOCK PACKAGE In Oracle: Example

CREATE OR REPLACE PACKAGE BODY EMPLOYEE_ACCESS
IS
  EMP_LOCKNAME VARCHAR2(128) := 'EMP_LOCK';
  EMP_LOCKHANDLE VARCHAR2(128);
  --
  FUNCTION GET_EMP_LOCK_HANDLE
  RETURN VARCHAR2
  IS
  BEGIN
    IF EMP_LOCKHANDLE IS NULL THEN
      DBMS_LOCK.ALLOCATE_UNIQUE (LOCKNAME => EMP_LOCKNAME,
                                 LOCKHANDLE => EMP_LOCKHANDLE
                                );
    END IF;
    RETURN EMP_LOCKHANDLE;
  END GET_EMP_LOCK_HANDLE;
END EMPLOYEE_ACCESS;
/
REQUEST FUNCTION:
  • The REQUEST function is used to acquire a lock in the mode specified by lockmode parameter.
  • If the lock cannot be acquired in the requested mode within the specified time the function returns a non-zero return value.
SYNTAX:

The first parameter of REQUEST function is overloaded. The first parameter is :

  • either identify the lock by an id (integer)
  • or identify the lock by lockhandle (varchar2)
--DBMS_LOCK PACKAGE In Oracle: DBMS_LOCK.REQUEST

DBMS_LOCK.REQUEST(ID INTEGER,
                  LOCKMODE INTEGER DEFAULT X_MODE,
                  TIMEOUT INTEGER DEFAULT MAXWAIT,
                  RELEASE_ON_COMMIT BOOLEAN DEFAULT FALSE)
                  RETURN INTEGER;

DBMS_LOCK.REQUEST(LOCKHANDLE VARCHAR2,
                  LOCKMODE INTEGER DEFAULT X_MODE,
                  TIMEOUT INTEGER DEFAULT MAXWAIT,
                  RELEASE_ON_COMMIT BOOLEAN DEFAULT FALSE)
                  RETURN INTEGER;
PARAMETER DESCRIPTIONS:
  • ID :-> Numeric id of the lock
  • LOCKHANDLE :-> Handle for lock returned by DBMS_LOCK.ALLOCATE_UNIQUE
  • LOCKMODE :-> Locking mode requested for lock
  • TIMEOUT :-> Time in second to wait for successful conversion.
  • RELEASE_ON_COMMIT :-> If TRUE, release lock automatically on COMMIT or ROLLBACK.
DESCRIPTION OF RETURN VALUES OF FUNCTION:
  • 0 –> Success
  • 1 –> Timed out
  • 2 –> Dead Lock
  • 3 –> Parameter error
  • 4 –> Do not own lock; cannot convert
  • 5 –> Illegal lock handle
--DBMS_LOCK PACKAGE In Oracle: Example

CREATE OR REPLACE PACKAGE EMPLOYEE_ACCESS
IS
  FUNCTION GET_EMP_LOCK_HANDLE RETURN VARCHAR2;
  PROCEDURE LOCK_EMP(P_RETURN_CODE OUT INTEGER);
END EMPLOYEE_ACCESS;
/
--DBMS_LOCK PACKAGE In Oracle: Example

CREATE OR REPLACE PACKAGE BODY EMPLOYEE_ACCESS
IS
  EMP_LOCKNAME VARCHAR2(128) := 'EMP_LOCK';
  EMP_LOCKHANDLE VARCHAR2(128);
  --
  FUNCTION GET_EMP_LOCK_HANDLE
  RETURN VARCHAR2
  IS
  BEGIN
    IF EMP_LOCKHANDLE IS NULL THEN
      DBMS_LOCK.ALLOCATE_UNIQUE (LOCKNAME => EMP_LOCKNAME,
                                 LOCKHANDLE => EMP_LOCKHANDLE
                                );
    END IF;
    RETURN EMP_LOCKHANDLE;
  END GET_EMP_LOCK_HANDLE;
  
  PROCEDURE LOCK_EMP(P_RETURN_CODE OUT INTEGER)
  IS
    TEMP_LOCKHANDLE EMP_LOCKHANDLE%TYPE := GET_EMP_LOCK_HANDLE;
    CALL_STATUS INTEGER;
  BEGIN
    CALL_STATUS := DBMS_LOCK.REQUEST(LOCKHANDLE => TEMP_LOCKHANDLE,
                                     LOCKMODE => DBMS_LOCK.X_MODE,
                                     TIMEOUT => 5,
                                     RELEASE_ON_COMMIT => TRUE);
    P_RETURN_CODE := CALL_STATUS;
  END LOCK_EMP;
END EMPLOYEE_ACCESS;
/
CONVERT FUNCTION:
  • The CONVERT function is used to convert a previously acquired lock to the mode specified by the lockmode parameter.
  • If the mode conversion cannot be granted within specified time then the function returns a non-zero value.
  • Just like REQUEST function, the first parameter of CONVERT function is overloaded. It is used to identify the lock either by integer or varchar2.
SYNTAX:
--DBMS_LOCK PACKAGE In Oracle: DBMS_LOCK.CONVERT

FUNCTION DBMS_LOCK.CONVERT(ID INTEGER,
                           LOCKMODE INTEGER,
                           TIMEOUT NUMBER DEFAULT MAXWAIT
                           ) RETURN INTEGER;

FUNCTION DBMS_LOCK.CONVERT(LOCKHANDLE VARCHAR2,
                           LOCKMODE INTEGER,
                           TIMEOUT NUMBER DEFAULT MAXWAIT
                           ) RETURN INTEGER;
PARAMETER DESCRIPTIONS:
  • ID :–> Numeric id of the lock
  • LOCKHANDLE :-> Handle for lock returned by DBMS_LOCK.ALLOCATE_UNIQUE
  • LOCKMODE :-> Locking mode to which to convert the lock
  • TIMEOUT :-> Time in second to wait for successful conversion.
DESCRIPTION OF RETURN VALUES OF FUNCTION:
  • 0 –> Success
  • 1 –> Timed out
  • 2 –> Dead Lock
  • 3 –> Parameter error
  • 4 –> Do not own lock; cannot convert
  • 5 –> Illegal lock handle
--DBMS_LOCK PACKAGE In Oracle: Example

DECLARE
  CALL_STATUS INTEGER;
BEGIN
  CALL_STATUS := DBMS_LOCK.CONVERT(9999, DBMS_LOCK.NL_MODE, 0);
  IF CALL_STATUS = 0 THEN
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
  ELSE
    IF CALL_STATUS = 1 THEN
      DBMS_OUTPUT.PUT_LINE('FAIL DUE TO TIME OUT');
    ELSIF CALL_STATUS = 2 THEN
      DBMS_OUTPUT.PUT_LINE('FAIL DUE TO DEAD LOCK');
    ELSIF CALL_STATUS = 3 THEN
      DBMS_OUTPUT.PUT_LINE('FAIL DUE TO PARAMETER ERROR');
    ELSIF CALL_STATUS = 4 THEN
      DBMS_OUTPUT.PUT_LINE('FAIL DUE TO DO NOT OWN LOCK OR UNABLE TO CONVERT');
    ELSIF CALL_STATUS = 5 THEN
      DBMS_OUTPUT.PUT_LINE('FAIL DUE TO ILLEGAL LOCK HANDLE');
    END IF;
  END IF;
END;
/
RELEASE FUNCTION:
  • RELEASE function releases a previously acquired lock.
  • Only parameter of RELEASE function is overloaded. Its used to identify the lock either by integer or varchar2.
SYNTAX:
FUNCTION DBMS_LOCK.RELEASE(ID INTEGER) RETURN INTEGER;
FUNCTION DBMS_LOCK.RELEASE(LOCKHANDLE VARCHAR2) RETURN INTEGER;
PARAMETER DESCRIPTION:
  • ID :–> Numeric id of the lock
  • LOCKHANDLE :–> Handle for lock returned by ALLOCATE_UNIQUE
DESCRIPTION OF RETURN VALUES OF FUNCTION:
  • 0 –> Success
  • 3 –> Parameter error
  • 4 –> Do not own lock; cannot release
  • 5 –> Illegal lock handle
--DBMS_LOCK PACKAGE In Oracle: Example

CREATE OR REPLACE PACKAGE EMPLOYEE_ACCESS
IS
  FUNCTION GET_EMP_LOCK_HANDLE RETURN VARCHAR2;
  PROCEDURE LOCK_EMP(P_RETURN_CODE OUT INTEGER);
  PROCEDURE RELEASE_EMP(P_RETURN_CODE OUT INTEGER);
END EMPLOYEE_ACCESS;
/
--DBMS_LOCK PACKAGE In Oracle: Example

CREATE OR REPLACE PACKAGE BODY EMPLOYEE_ACCESS
IS
  EMP_LOCKNAME VARCHAR2(128) := 'EMP_LOCK';
  EMP_LOCKHANDLE VARCHAR2(128);
  TEMP_LOCKHANDLE EMP_LOCKHANDLE%TYPE := GET_EMP_LOCK_HANDLE;
  CALL_STATUS INTEGER;
  --
  FUNCTION GET_EMP_LOCK_HANDLE
  RETURN VARCHAR2
  IS
  BEGIN
    IF EMP_LOCKHANDLE IS NULL THEN
      DBMS_LOCK.ALLOCATE_UNIQUE (LOCKNAME => EMP_LOCKNAME,
                                 LOCKHANDLE => EMP_LOCKHANDLE
                                );
    END IF;
    RETURN EMP_LOCKHANDLE;
  END GET_EMP_LOCK_HANDLE;
  
  PROCEDURE LOCK_EMP(P_RETURN_CODE OUT INTEGER)
  IS
  BEGIN
    CALL_STATUS := DBMS_LOCK.REQUEST(LOCKHANDLE => TEMP_LOCKHANDLE,
                                     LOCKMODE => DBMS_LOCK.X_MODE,
                                     TIMEOUT => 5,
                                     RELEASE_ON_COMMIT => TRUE);
    P_RETURN_CODE := CALL_STATUS;
  END LOCK_EMP;
  
  PROCEDURE RELEASE_EMP(P_RETURN_CODE OUT INTEGER)
  IS
  BEGIN
    CALL_STATUS := DBMS_LOCK.RELEASE(LOCKHANDLE => TEMP_LOCKHANDLE);
    P_RETURN_CODE := CALL_STATUS;
  END RELEASE_EMP;
END EMPLOYEE_ACCESS;
/
SLEEP PROCEDURE:
  • SLEEP procedure suspends the session for the no of seconds specified in seconds parameter.
  • This procedure can be specified in hundredth part of a second e.g. 1.25, 1.49 etc.
SYNTAX:
PROCEDURE DBMS_LOCK.SLEEP(SECONDS NUMBER);
  • Do not specify a null value for seconds parameter. It will raise ORA-00600 error.
BEGIN
  DBMS_LOCK.SLEEP(10);
END;
/

Author

Related Post
  1. The Ultimate Guide to PLSQL Compiler
  2. Working with Stored Procedure Parameters
  3. How to compile invalid trigger in Oracle?