最近开发人员说使用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

      

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