最近开发人员说使用dbms_lock.allocate_unique自定义的锁在使用dbms_lock.relase无法释放，下面来个演示的例子来看看到底怎么一回事？
1、演示锁不能释放的情形
--演示环境
goex_admin@GOBO1> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
--调用包lock_demo来分配一个锁，关于lock_demo包的代码见文章尾部
goex_admin@GOBO1> DECLARE
2 s VARCHAR2 (200);
3 BEGIN
4 lock_demo.request_lock (6, s);
5 DBMS_OUTPUT.put_line (s);
6 END;
7 /
10737420671073742067151 ----->得到lock handle
0
PL/SQL procedure successfully completed
--在session 2查看用户自定义锁
goex_admin@GOBO1> @query_defined_lock
NAME PROGRAM SPID OSUSER SID PID TERMINAL STATUS LOCKID EXPIRATION
-------------- ------------------------ -------- -------- ---- ------- ---------- -------- ---------- -----------------
control_lock sqlplus@SZDB (TNS V1-V3) 30841 robin 1049 14567 pts/0 INACTIVE 1073742067 20130420 18:00:00
--在session 2尝试释放在session分配的锁，直接调用包DBMS_LOCK
goex_admin@GOBO1> DECLARE
2 RetVal NUMBER;
3 LOCKHANDLE VARCHAR2(32767);
4
5 BEGIN
6 LOCKHANDLE := '10737420671073742067151';
7
8 RetVal := SYS.DBMS_LOCK.RELEASE ( LOCKHANDLE );
9
10 DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));
11
12 DBMS_OUTPUT.Put_Line('');
13
14 COMMIT;
15 END;
16 /
RetVal = 4 ----->此处获得了为4的返回码即Do not own lock specified by id or lockhandle
PL/SQL procedure successfully completed.
--在原来的session 1释放锁，直接调用包DBMS_LOCK，此时锁被成功释放
goex_admin@GOBO1> DECLARE
2 RetVal NUMBER;
3 LOCKHANDLE VARCHAR2(32767);
4
5 BEGIN
6 LOCKHANDLE := '10737420671073742067151';
7
8 RetVal := SYS.DBMS_LOCK.RELEASE ( LOCKHANDLE );
9
10 DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));
11
12 DBMS_OUTPUT.Put_Line('');
13
14 COMMIT;
15 END;
16 /
RetVal = 0 --------> The lock was released successful.
PL/SQL procedure successfully completed.
--在session 2查询不到之前分配的锁
goex_admin@GOBO1> @query_defined_lock
no rows selected
2、自定义锁阻塞的情形
--首先分配一个锁
--注意下面的SQL提示符之前的SID代表不同的session，如1073@GOBO1>，即表示session ID 为1073。以下类同。
1073@GOBO1> SET SERVEROUTPUT ON
1073@GOBO1> DECLARE
2 s VARCHAR2 (200);
3 BEGIN
4 lock_demo.request_lock (6, s);
5 DBMS_OUTPUT.put_line (s);
6 END;
7 /
10737420671073742067151
0
PL/SQL procedure successfully completed.
--在第二个session 1032中尝试请求锁并插入数据
1032@GOBO1> SET SERVEROUTPUT ON
1032@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 --->本行的符号“>>”是有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.
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612
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、演示中用的代码
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;
------------------------------------------------------------
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;
/
4、小结
a、从上面的测试可知，对于使用dbms_lock手动分配的锁，只能在同一session释放。
b、使用dbms_lock显示锁管理与隐式锁管理能够实现相同的功能。
c、文章参考: Oracle DBMS_LOCK
更多参考
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划