For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
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';
-- 98 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 AUDSYS, 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);