| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmslock.sql |
| First Available |
7.3.4 |
Constants |
| Name |
Description |
Data Type |
Value |
| nl_mode |
NuLl |
INTEGER |
1 |
| ss_mode |
Sub Shared: used on an aggregate object to indicate that share locks are being acquired on subparts of the object |
INTEGER |
2 |
| sx_mode |
Sub eXclusive: used on an aggregate object to indicate that exclusive locks are being acquired on sub-parts of the object
|
INTEGER |
3 |
| s_mode |
Shared: indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks |
INTEGER |
4 |
| ssx_mod |
Shared SubeXclusive |
INTEGER |
5 |
| x_mode |
eXclusive |
INTEGER |
6 |
|
Dependencies |
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_LOCK'
UNION
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_LOCK'; |
| Exceptions |
| Error Number |
Description |
|
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. |
|
| Object Privileges |
GRANT execute ON dbms_lock TO <schema_name> |
| GRANT execute ON dbms_lock TO uwclass; |
| |
| 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 |
| |
| 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 |
Overload 2 |
dbms_lock.convert(
lockhandle IN VARCHAR2,
lockmode IN INTEGER,
timeout IN NUMBER DEFAULT maxwait)
RETURN INTEGER; |
| See dbms_lock demo |
| |
| 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 |
| Overload 2 |
dbms_lock.release(lockhandle IN VARCHAR2) RETURN INTEGER; |
| See dbms_lock demo |
| |
| 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 |
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 |
| |
| SLEEP |
| Suspends the session for a given period of time |
dbms_lock.sleep(seconds IN NUMBER); |
| exec
dbms_lock.sleep(1.00); |
| |
| Demo |
-- 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 IS
v_lockname VARCHAR2(12) := 'control_lock';
v_lockhandle VARCHAR2(200);
v_result PLS_INTEGER;
-- obtain a lock
PROCEDURE request_lock(p_ltype INTEGER, p_retval OUT INTEGER);
-- release an existing lock
PROCEDURE release_lock(p_retval OUT INTEGER);
-- view the stored handle
FUNCTION see_handle RETURN VARCHAR2;
-- decode lock request
FUNCTION decode_req(p_result PLS_INTEGER) RETURN VARCHAR2;
-- decode lock release
FUNCTION decode_rel(p_result PLS_INTEGER) RETURN VARCHAR2;
END lock_demo;
/
CREATE OR REPLACE PACKAGE BODY lock_demo IS
PROCEDURE request_lock(p_ltype IN INTEGER, p_retval OUT INTEGER) IS
BEGIN
IF v_lockhandle IS NULL THEN
dbms_lock.allocate_unique(v_lockname, v_lockhandle);
END IF;
p_retval := dbms_lock.request(v_lockhandle, p_ltype);
END request_lock;
------------------------------------------------------------
PROCEDURE release_lock(p_retval OUT INTEGER) IS
BEGIN
IF v_lockhandle IS NOT NULL THEN
p_retval := dbms_lock.release(v_lockhandle);
END IF;
END release_lock;
------------------------------------------------------------
FUNCTION see_handle RETURN VARCHAR2 IS
BEGIN
IF v_lockhandle IS NOT NULL THEN
RETURN v_lockhandle;
ELSE
RETURN 'Not Allocated';
END IF;
END see_handle;
------------------------------------------------------------
FUNCTION decode_req(p_result PLS_INTEGER) RETURN VARCHAR2 IS
retval VARCHAR2(20);
BEGIN
SELECT DECODE(p_result,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(p_result 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 |
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;
/ |
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;
|
