dbms_lock.relase 无法释放自定义的锁?

      最近开发人员说使用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语句执行计划