Re: Lock timeout

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 Wed Dec 01 1999 - 15:06:36 CST