dbms_lock.relase无法释放自定义的锁解决(二)
18 /
>>10737420671073742067151 --->本行的符号“>>”是有SecureCRT在空闲每300s自动生成的字符
0 --->也就是session 被阻塞
PL/SQL procedure successfully completed.
--在第三个session 1033中尝试请求锁并插入数据
1033@GOBO1> SET SERVEROUTPUT ON
1033@GOBO1> DECLARE
2 s VARCHAR2 (200);
3 BEGIN
4 lock_demo.request_lock (DBMS_LOCK.ss_mode, s);
5
6 DBMS_OUTPUT.put_line (s);
7
8 INSERT INTO lock_test (action, when)
9 VALUES ('started', SYSTIMESTAMP);
10
11 DBMS_LOCK.sleep (5);
12
13 INSERT INTO lock_test (action, when)
14 VALUES ('ended', SYSTIMESTAMP);
15
16 COMMIT;
17 END;
18 /
>>10737420671073742067151 --->本行的符号说明同session 1032
0
PL/SQL procedure successfully completed.
--在另外一个session观察被阻塞的情形
--下面的查询在session 1073的锁未释放前执行，可以看到1073的Exclusive锁阻塞了1032与1033的Row Share
1037@GOBO1> @waiting_sess_by_lock
SID USERNAME OSUSER TERMINAL OBJECT_NAME TY Lock Mode Req Mode
---------- -------------- --------------- ------------ -------------------- -- ----------- -----------
1032 GOEX_ADMIN robin pts/4 UL --Waiting-- Row Share
1033 GOEX_ADMIN robin pts/6 UL --Waiting-- Row Share
1073 GOEX_ADMIN robin pts/2 UL Exclusive
--下面释放session 1073的锁
1073@GOBO1> DECLARE
2 s VARCHAR2(200);
3 BEGIN
4 lock_demo.release_lock(s);
5 dbms_output.put_line(s);
6 END;
7 /
0
PL/SQL procedure successfully completed.
1073@GOBO1> select * from lock_test;
ACTION WHEN
---------- --------------------------------------------------
started 20-JUN-13 05.00.36.845854000 PM
ended 20-JUN-13 05.00.41.841460000 PM
started 20-JUN-13 05.00.36.845385000 PM
ended 20-JUN-13 05.00.41.841064000 PM
started 20-JUN-13 04.39.46.303529000 PM
ended 20-JUN-13 04.39.51.345226000 PM
3、演示中用的代码
[sql]
CREATE TABLE lock_test (
action VARCHAR2(10),
when TIMESTAMP(9));
CREATE OR REPLACE PACKAGE GOEX_ADMIN.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 GOEX_ADMIN.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;
DBMS_OUTPUT.put_line (TO_CHAR (v_lockhandle));
END request_lock;