Re: Lock timeout

I sent a personal note to Tom Kyte asking for a little more detail about how long a lock wait would last. He responded:

        "the transaction will wait until the resource becomes available....."

Terry Stewart

Doug Cowles wrote:

> I had a similar situation where a developer ungracefully exited from the client and
> left
> a select for update nowait on a row. He was unable to restart his development
> because
> the application had left a JDBC session holding the lock indefinitely. When he came
>
> back in the morning, the lock was gone. Is this because I put on idle_time limit
> of
> 180 minute on that user which eventually terminated the session? What if I had not
> placed a limit.. will locks be held indefinitely? Or do they get cleaned up under
> any
> circumstances (automatic rollbacks and such).
>
> - Dc.
>
> Thomas Kyte wrote:
>
> > A copy of this was sent to Terry Stewart <0A2215_at_us.ibm.com>
> > (if that email address didn't require changing)
> > On Wed, 01 Dec 1999 11:33:13 -0600, you 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).
> > >
> >
> > you can either wait for a lock or not wait -- you cannot set a timeout on a lock
> > in Oracle.
> >
> > In order to avoid waiting for the lock, you woul "select the data FOR UPDATE
> > NOWAIT" in the client. If this returns an error -- its already locked.
> >
> > >Thanks in advance for any help,
> > >Terry Stewart
> >
> > --
> > See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> > Current article is "Part I of V, Autonomous Transactions" updated June 21'st
> >
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries Reston, VA USA
> >
> > Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu Dec 02 1999 - 15:28:51 CST