开发者社区> leshami> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:       最近开发人员说使用dbms_lock.allocate_unique自定义的锁在使用dbms_lock.relase无法释放,下面来个演示的例子来看看到底怎么一回事? 1、演示锁不能释放的情形    --演示环境 goex_admin...
+关注继续查看

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

 

Oracle&nbsp;牛鹏社

 

更多参考

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语句执行计划

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Kompose: Docker-compose 到 Kubernetes 的迁移工具
在 skippbox,我们开发了 Kompose 这一工具,他能够自动把 Docker Compose 应用转换为 Kubernetes 描述文件。利用一个简单的 kompose up 命令,就可以在 Kubernetes 集群上启动 Compose 应用。
1689 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
刚开通学生ECS,使用并安装docker和docker-compose
刚开通学生ECS,使用并安装docker和docker-compose
0 0
Kubernetes Docker Compose 迁移
Kubernetes Docker Compose 迁移
0 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
unable to import 'smart_open.gcs', disabling that module
unable to import 'smart_open.gcs', disabling that module
0 0
+关注
leshami
传播知识,分享快乐!十年以上数据库,系统运维与管理,性能优化经验。全部文章,欢迎扩散,转载请注明出处!
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载