Re: Lock timeout

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 01 Dec 1999 20:28:57 +0800
Message-ID: <38451489.63CF@yahoo.com>

Terry Stewart wrote:
>
> Hi,
> Does anybody know how I specify a 'lock wait timeout' value for Oracle
> 8i (Solaris). All I want to do is say that whenever I need a lock and
> there is a conflicting lock, that I only want to wait for 15 seconds,
> for example. There used to be an init parameter
> 'distributed_lock_timeout', but this seems to have been obsoleted in 8i.
>
> The scenarion I am trying to deal with is simply the case where a
> transaction is started by some GUI client and he goes off to get a
> coffee. In the meantime someone else trys to lock a row which the first
> (coffee drinking) user has locked. I would like the second user to get a
> 'lock timeout' exception.
> This seems like a fairly fundamental DB thing that I am having
> considerable difficulty finding in the Oracle documantation. I am at the
> point of admitting to stupidity (maybe its true).
>
> Thanks in advance for any help,
> Terry Stewart

See Thomas's post...

To simulate a lock timeout you can use the dbms_lock.sleep routine, namely

declare
  failed_to_lock exception;
  pragma exception_init(failed_to_lock,-54);   v_counter number := 1;
begin
  begin
    lock blah in exclusive mode nowait;   exception when failed_to_lock then

     if v_counter > 10 then 
       raise_application_error(-20000,'10 seconds AGH!');
     else
       dbms_lock.sleep(1);
       v_counter := v_counter + 1;
     end if;

  end;
end;
/     

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Wed Dec 01 1999 - 06:28:57 CST