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
0PL/SQL procedure successfully completed--在session 2查看用户自定义锁
goex_admin@GOBO1> @query_defined_lockNAME 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 lockhandlePL/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_lockno rows selected2、自定义锁阻塞的情形--首先分配一个锁
--注意下面的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
0PL/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
0PL/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 /0PL/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

Oracle Tablespace Transportation使用datapump 导出导入同义词（export and import synonym using datapump）相关资讯 dbms_lock.relase 本文评论查看全部评论（0）