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

最近开发人员说使用dbms_lock.allocate_unique自定义的锁在使用dbms_lock.relase无法释放，下面来个演示的例子来看看到底怎么一回

最近开发人员说使用dbms_lock.allocate_unique自定义的锁在使用dbms_lock.relase无法释放，下面来个演示的例子来看看到底怎么一回事？

1、演示锁不能释放的情形 

–演示环境
goex_admin@GOBO1> select * from v$version where rownum

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