Oracle DBMS_LOCK
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Note: I can not advise, too strongly, that DBAs and Developers, working with Oracle, read the documentation included in the DBMS_LOCK source file.
AUTHID DEFINER
Constants
Name Data Type Value
nl_mode INTEGER 1
ss_mode INTEGER 2
sx_mode INTEGER 3
s_mode INTEGER 4
ssx_mode INTEGER 5
x_mode INTEGER 6
maxwait INTEGER 32767
badseconds_num NUMBER -38148
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_LOCK'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_LOCK';

83 objects
Documented Yes
Exceptions
Error Code Reason
ORA-20000 Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.
ORU-10003 Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to APEX_040200, CTXSYS, EXECUTE_CATALOG_ROLE, EXFSYS, GSMADMIN_INTERNAL, MDSYS, OLAPSYS, ORDSYS, WMSYS
Source {ORACLE_HOME}/rdbms/admin/dbmslock.sql
Subprograms
 
ALLOCATE_UNIQUE
Allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name. Lock identifiers are used to enable applications to coordinate their use of locks. dbms_lock.allocate_unique(
lockname        IN  VARCHAR2,
lockhandle      OUT VARCHAR2,
expiration_secs IN  INTEGER DEFAULT 864000);
See dbms_lock Demo Below
 
ALLOCATE_UNIQUE_AUTONOMOUS (new 12.1)
Autonomous version of the allocate_unique procedure dbms_lock.allocate_unique_autonomous(
lockname        IN  VARCHAR2,
lockhandle      OUT VARCHAR2,
expiration_secs IN  INTEGER DEFAULT 864000);
See dbms_lock Demo Below
 
CONVERT
Converts a lock from one mode to another

Overload 1
dbms_lock.convert(
id       IN INTEGER,
lockmode IN INTEGER,
timeout  IN NUMBER DEFAULT maxwait)
RETURN INTEGER;

Return Values
0 Success
1 Timeout
2 Deadlock
3 Parameter error
4 Don't own lock specified by id or lockhandle
5 Illegal lock handle
See dbms_lock Demo Below
Overload 2 dbms_lock.convert(
lockhandle IN VARCHAR2,
lockmode   IN INTEGER,
timeout    IN NUMBER DEFAULT maxwait)
RETURN INTEGER;
See dbms_lock Demo Below
 
RELEASE
Explicitly releases a lock previously acquired using the REQUEST function

Overload 1
dbms_lock.release(id IN INTEGER) RETURN INTEGER;

Return Values
0 Success
3 Parameter error
4 Don't own lock specified by id or lockhandle
5 Illegal lock handle
See dbms_lock Demo Below
Overload 2 dbms_lock.release(lockhandle IN VARCHAR2) RETURN INTEGER;
See dbms_lock Demo Below
 
REQUEST
Requests a lock with a given mode

Overload 1
dbms_lock.request(
id                IN INTEGER,
lockmode          IN INTEGER DEFAULT x_mode,
timeout           IN INTEGER DEFAULT maxwait,
release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;

Return Values
0 Success
1 Timeout
2 Deadlock
3 Parameter error
4 Don't own lock specified by id or lockhandle
5 Illegal lock handle
See dbms_lock Demo Below
Overload 2 dbms_lock.request(
lockhandle        IN VARCHAR2,
lockmode          IN INTEGER DEFAULT x_mode,
timeout           IN INTEGER DEFAULT maxwait,
release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
See dbms_lock Demo Below
 
SLEEP
Suspends the session for a given period of time dbms_lock.sleep(seconds IN NUMBER);
exec dbms_lock.sleep(1.00);
 
DBMS_LOCK Demo
Session 1
-- create demo table

CREATE TABLE lock_test (
action VARCHAR2(10),
when   TIMESTAMP(9));

GRANT insert ON lock_test TO public;

CREATE OR REPLACE PACKAGE lock_demo ATHID DEFINER IS
 vLockName   VARCHAR2(12) := 'control_lock';
 vLockHandle VARCHAR2(200);
 vResult     PLS_INTEGER;

 -- obtain a lock
 PROCEDURE request_lock(pLMode INTEGER, pRetval OUT INTEGER);
 -- release an existing lock
 PROCEDURE release_lock(pRetval OUT INTEGER);
 -- view the stored handle
 FUNCTION see_handle RETURN VARCHAR2;
 -- decode lock request
 FUNCTION decode_req(pResult PLS_INTEGER) RETURN VARCHAR2;
 -- decode lock release
 FUNCTION decode_rel(pResult PLS_INTEGER) RETURN VARCHAR2;
END lock_demo;
/

CREATE OR REPLACE PACKAGE BODY lock_demo IS
 PROCEDURE request_lock(pLMode IN INTEGER, pRetval OUT INTEGER) IS
 BEGIN
   IF v_lockhandle IS NULL THEN
     dbms_lock.allocate_unique(vLockName, vLockHandle);
     p_retval := dbms_lock.request(vLockHandle, pLMode);
   END IF;
 END request_lock;
 ------------------------------------------------------------
 PROCEDURE release_lock(pRetval OUT INTEGER) IS
 BEGIN
   IF vLockHandle IS NOT NULL THEN
     pRetval := dbms_lock.release(vLockHandle);
   END IF;
 END release_lock;
 ------------------------------------------------------------
 FUNCTION see_handle RETURN VARCHAR2 IS
 BEGIN
   IF vLockHandle IS NOT NULL THEN
     RETURN vLockHandle;
   ELSE
     RETURN 'Not Allocated';
   END IF;
 END see_handle;
 ------------------------------------------------------------
 FUNCTION decode_req(pResult PLS_INTEGER) RETURN VARCHAR2 IS
  retval VARCHAR2(20);
 BEGIN
   SELECT DECODE(pResult, 0, 'Success', 1, 'Timeout', 2, 'Deadlock',
   3, 'Parameter Error', 4, 'Already owned', 5, 'Illegal Lock Handle')
   INTO retval
   FROM dual;

   RETURN retval;
 END decode_req;
 ------------------------------------------------------------
 FUNCTION decode_rel(pResult PLS_INTEGER) RETURN VARCHAR2 IS
  retval VARCHAR2(20);
 BEGIN
   SELECT DECODE(p_result, 0, 3, 'Parameter Error', 4, 'Already owned',
   5, 'Illegal Lock Handle')
   INTO retval
   FROM dual;

   RETURN retval;
 END decode_rel;
 ------------------------------------------------------------
END lock_demo;
/

GRANT execute ON lock_demo TO public;

set serveroutput on

-- get an exclusive lock in the current session (Session 1)
DECLARE
 s VARCHAR2(200);
BEGIN
  lock_demo.request_lock(6, s);
  dbms_output.put_line(s);
END;
/

/* Two session request a shared lock (ss_mode). The shared lock cannot be acquired because session 1 holds an exclusive lock. Execution will stop on the request until the the exclusive lock is released. */
Session 2 Session 3
conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
  s VARCHAR2(200);
BEGIN
  uwclass.lock_demo.request_lock(dbms_lock.ss_mode, s);

  dbms_output.put_line(s);

  INSERT INTO uwclass.lock_test
  (action, when)
  VALUES
  ('started', SYSTIMESTAMP);

  dbms_lock.sleep(5);

  INSERT INTO uwclass.lock_test
  (action, when)
  VALUES
  ('ended', SYSTIMESTAMP);
  COMMIT;
END;
/
conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
  s VARCHAR2(200);
BEGIN
  uwclass.lock_demo.request_lock(dbms_lock.ss_mode, s);

  dbms_output.put_line(s);

  INSERT INTO uwclass.lock_test
  (action, when)
  VALUES
  ('started', SYSTIMESTAMP);

  dbms_lock.sleep(5);

  INSERT INTO uwclass.lock_test
  (action, when)
  VALUES
  ('ended' , SYSTIMESTAMP);
  COMMIT;
END;
/

-- Session 1 releases its lock
DECLARE
  s VARCHAR2(200);
BEGIN
  lock_demo.release_lock(s);
  dbms_output.put_line(s);
END;
/

-- Execution resumes when the exclusive lock is released
SELECT TO_CHAR(when,'dd.mm.yyyy hh24:mi:ss'), action
FROM lock_test
ORDER BY when;

Related Topics
Packages
USER_LOCK