Oracle DBMS_LOCK

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;