• Asktom: Lock


    You Asked

    In my PL/SQL code, when I select for update, if anybody else is working & has been locked 
    what I want to update, I want my program to wait for n seconds for the row(s) to be 
    released, if released then continue updating, if still not released after n seconds, 
    detects the user who is locking the resource and informs me that person. I am writing 
    pseudo code like this: 
    
        select xxx for update yyy 
           wait n seconds 
               message (the user USER is locking the record RECORD)
    
    Thanks.
    

    Tom:

    Your wrong.  select * from update nowait will raise an ORA-54, resource busy.

    That is what NOWAIT does.  Try it and see.

    Why use execute immediate?  It is static sql.  sigh....


    In one session I execute:

    ops$tkyte@ORA817DEV.US.ORACLE.COM> lock table emp in exclusive mode;

    Table(s) Locked.

    In another, I run:


    ops$tkyte@ORA817DEV.US.ORACLE.COM> set echo on
    ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on
    ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
    ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
      2      resource_busy   exception;
      3      pragma exception_init( resource_busy, -54 );
      4      success boolean := False;
      5      l_rec   emp%rowtype;
      6  begin
      7 
      8    for i in 1 .. 3
      9    loop
    10       exit when (success);
    11       begin
    12             select * into l_rec from emp where rownum=1 for update NOWAIT;
    13             success := true;
    14       exception
    15             when resource_busy then
    16                 dbms_lock.sleep(1);
    17       end;
    18     end loop;
    19 
    20     if ( not success ) then
    21           raise_application_error( -20001, 'row is locked by another session' );
    22     end if;
    23  end;
    24  /
    declare
    *
    ERROR at line 1:
    ORA-20001: row is locked by another session
    ORA-06512: at line 21



    works as advertised.

    Again, in one session issue:

    ops$tkyte@ORA817DEV.US.ORACLE.COM> update emp set ename=ename where empno=7369;    
    1 row updated.

    Now, goto another session and:

    ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
      2      resource_busy   exception;
      3      pragma exception_init( resource_busy, -54 );
      4      success boolean := False;
      5      l_rec   emp%rowtype;
      6  begin
      7 
      8    for i in 1 .. 3
      9    loop
    10       exit when (success);
    11       begin
    12             select * into l_rec from emp where empno=7369 for update NOWAIT;
    13             success := true;
    14       exception
    15             when resource_busy then
    16                 dbms_lock.sleep(1);
    17       end;
    18     end loop;
    19 
    20     if ( not success ) then
    21           raise_application_error( -20001, 'row is locked by another session' );
    22     end if;
    23  end;
    24  /
    declare
    *
    ERROR at line 1:
    ORA-20001: row is locked by another session
    ORA-06512: at line 21


    again works as advertised. 
    If you can prove otherwise, lay out the steps bit by bit and show us -- just like I do.

  • 相关阅读:
    uniGUI之uniEdit(23)
    ​Shiro授权
    Shiro密码重试次数限制
    Ehcache基础入门
    Shiro简单加密服务
    Shiro身份验证
    第二章、Web中使用shiro(实现登陆)
    第一章、认识Shiro
    使用IntelliJ/Eclipse生成类图
    Jedis操作Redis实例
  • 原文地址:https://www.cnblogs.com/tracy/p/2193991.html
Copyright © 2020-2023  润新知