摘要：
DBMS_了解LOCK包。了解DBMS_LOCK包的用法。该材料的描述和示例非常详细。找到它不容易，哈哈。
DBMS_LOCK包的学习
学习一下DBMS_LOCK包的用法。这份材料的说明和举例都很详细了，好不容易才找到的，哈哈。
| General Information | |||||||||||||||||||||||||||||
| Source | {ORACLE_HOME}/rdbms/admin/dbmslock.sql | ||||||||||||||||||||||||||||
| First Available | 7.3.4 | ||||||||||||||||||||||||||||
Constants |
| ||||||||||||||||||||||||||||
Dependencies | SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_LOCK' UNION SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_LOCK'; | ||||||||||||||||||||||||||||
| Exceptions |
| ||||||||||||||||||||||||||||
| 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;
| ||||||||||||||||||||||||||||
| 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;
| ||||||||||||||||||||||||||||
| 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;
| ||||||||||||||||||||||||||||
| 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); p_retval := dbms_lock.request(v_lockhandle, p_ltype); END IF; 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 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; | |||||||||||||||||||||||||||||