CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_LOCK
Version 11.1
 
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;
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [273 users online]    © 2010 psoug.org