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