"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
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);
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;
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);
