Re: Locking problem?
From: Pete Kolton <peter.kolton_at_nspmbtinternet.com>
Date: Wed, 24 Nov 1999 09:01:20 -0000
Message-ID: <81g9cq$cb8$1_at_supernews.com>
Date: Wed, 24 Nov 1999 09:01:20 -0000
Message-ID: <81g9cq$cb8$1_at_supernews.com>
Interestingly, you don't even need to do the fetch. Just opening and closing the cursor is enough to lock the records.
-- ======================================================== Pete Kolton ORACLE Systems Consultant All reasonable offers considered :-) Pete_at_kolton.com http://www.kolton.com ======================================================== Neville Sweet wrote in message <81flsf$plu11_at_atbhp.corpmel.bhp.com.au>...Received on Wed Nov 24 1999 - 10:01:20 CET
>Hi Robert,
>
>It hangs because the default behaviour is to wait until another user
unlocks
>the row.
>You can use SELECT FOR UPDATE NOWAIT to explicitly lock the row prior to
>issuing the UPDATE statement, and then trap that the row is locked by using
>an exception.
>eg.
>FUNCTION your_function IS
>---
> CURSOR xxx IS
> SELECT 1 /* (or column_name, column_name) */
> FROM table_name
> WHERE yyy
> FOR UPDATE OF table_name
> NOWAIT;
>
> row_locked EXCEPTION; /* ORA-00054 Resource busy and acquire with
>NOWAIT. */
>BEGIN
> PRAGMA EXCEPTION_INIT(row_locked, -54);
>---
> OPEN xxx;
> FETCH xxx
> INTO your_var;
> CLOSE xxx;
> /* the row remains locked until commit or rollback. */
>
> UPDATE table_name
> etc.
>---
> RETURN 0;
>
>EXCEPTION
> WHEN row_locked
> THEN
> RETURN -1;
>END;
>
>Then in your calling form, if -1 returned, display an error. If there are
>potentially multiple errors then consider loading the error message into an
>OUT variable.
>
>An alternative approach is to lock the row in your Form, and then call the
>stored database procedure only if successful.
>The FOR UPDATE logic and syntax is the same.
>
>Robert Garcia wrote in message <383ACFB6.D73EDAF8_at_gte.net>...
>>I have a Developer 2000, Windows NT, Oracle Server version 7,
>>application that I execute update procedures out of a Validation
>>Trigger. The problem is that the update procedure hangs - instead of
>>executing an On Error Trigger - when one of the records to be updated
>>is being edited in another instance of my application. A further
>>description is as follows:
>>When running two instances of my application, the first instance was
>>locked onto (editing) a record that I knew that the second instance was
>>going to update. When following the breakpoints of the second instance,
>>it got to the update procedure (called out of the validation trigger)
>>and the screen froze. I have 'on error' triggers set on the item that
>>triggered the update procedure, on the block of this item. The 'on
>>error' trigger will display an alert which will require a response from
>>the user. None of these alerts was executed.
>>
>>What is happening is that my update (implicit) query is hanging - On
>>Error trigger is not executed - because the record I am trying to
>>update is locked by another user. Only when the first instance of my
>>application disconnects will the update query run to completion.
>> My gut feeling is that Oracle never expected Update Queries to be
>>executed out of validation triggers. Has anyone been able to do this?
>
>
>