Re: Lock question in Oracle 7.3

In article <Pine.LNX.3.96.980602155103.19603C-100000_at_hercules.corvex.hu>,   Szirmai Tibor <szt_at_mail.corvex.hu> wrote:
>
>

SNIP
> We use automatic row-locking in our programs, and we need to set
> a timeout value, which controls the time that clients wait when
> they are about to access a locked table. In our system this is
> usually 3 sec.

SNIP (Informix folklore deleted)
> In Oracle it doesn't work. Our customer service in Hungary had only
> one idea: the NOWAIT option with LOCK TABLE command - but it locks
> a whole table, not only a row - and if it is possible we would like
> to modify our source code as little as possible.
>
> Thanks in advance for any ideas,
> please send me email.
>
> Tibor Szirmai, dba
> Corvex Corp.
> mail: szt_at_mail.corvex.hu

Use the following technique:

SELECT
<your columns>, rowid
<rest of your query>
FOR UPDATE NOWAIT; If the row is locked, Oracle responds with ORA-00054: resource busy and acquire with NOWAIT specified. Catch this message. Then wait for your 3 seconds. Then, try the same thing again.

If you can select your row, you may later use the rowid as world-record fastest access method for the update. Since you locked the row, the rowid is *guaranteed* not to change.

Hope this helped, rgw

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Jun 04 1998 - 08:34:13 CDT