| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Lock timeout
tedchyn_at_yahoo.com wrote:
>
> conner, I tried your code out
> using lock table xx in exclusive mode nowait;
> the exception codition was never met hence no error was raised.
> should there be another exception condition other than the one you
> listed.
> thanks ted
>
> In article <38451489.63CF_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
> > 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."
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Hmmm..I thought it was 54...
If no exception was raised, then you successfully have the lock. If an exception is raised, then note the error number and use that in the pragma.
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri Dec 03 1999 - 06:00:37 CST
 |
 |