Oracle的dbms_lock包的相关知识

DBMS_LOCK授权
revoke execute on dbms_lock from apps;
grant execute on dbms_lock to apps;

将DBMS_LOCK包授权给USER:APPS.

相关操作：
-- 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. */
session2
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 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;
/

-- 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;