Re: Lock detection and resolution

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 25 May 2001 11:18:19 +1000
Message-ID: <3b0db2f1$1@news.iprimus.com.au>

"Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> wrote in message news:3b0d3b9e.23188499_at_news-server...
> On 24 May 2001 08:54:19 -0700, onlyforposting_at_yahoo.com (Vikas
> Agnihotri) wrote:
>
> >
> >Basically, my developers are asking if there is a way that, given a
> >empno, they can query Oracle internals to find out if that row is
> >free'n'clear or locked by a uncommited tranaction?
> >
>
> SELECT ..... FOR UPDATE OF .... NOWAIT
> FROM ....
> .
>

Yup, basically, Sue's transaction will fail to acquire the relevant locks, and will immediately fail with a 'resource busy and nowait specified' (or words to that effect).

That's the developers' solution. Personally, I'd hit John around the head a bit and tell him not to be so daft as to go for a cup of coffee in the middle of a transaction on what is evidently a busy OLTP system. And that's straight out of the "physical force" school of DBA techniques. Much more satisfying too.

(And in all seriousness, there is an element of User training involved in this sort of thing).

Regards
HJR .
> .
> .
>
> Check return code.
>
>
> It's in the "manuels"...
>
> HTH
>
> Cheers
> Nuno Souto
> nsouto_at_bigpond.net.au.nospam
> http://www.users.bigpond.net.au/the_Den/index.html
Received on Thu May 24 2001 - 20:18:19 CDT