Oracle DBMS_LOCK
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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';

86 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 CTXSYS, DATAPATCH_ROLE, EXECUTE_CATALOG_ROLE, GGSYS, GSMADMIN_INTERNAL, MDSYS, ORDSYS, RECOVERY_CATALOG_OWNER, RECOVERY_CATALOG_OWNER_VPD, RECOVERY_CATALOG_USER, and 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
Built-in Functions
Built-in Packages
USER_LOCK
What's New In 12cR2
What's New In 18cR1