Re: Q: forcing failures when rows are locked.

On May 23, 9:11 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
> Production
>
> In one session I lock a row
>         begin
>         select  the_ID, COMMENT_TXT into :id,:txt
>         from the_table
>         where the_id = 1234
>         for update nowait ;
>         end;
>         /
>
> In a second session I try to update the same row.
>
>         update the_table set comment_txt = 'new comment'
>         where the_id = 1234;
>
> The second session hangs, until the first session commits or rolls back;
> Same thing happens with delete, (and presumably any other operations that
> would alter that row).
>
> On the other hand, if the second session tries to lock the row then it
> gets the error
>
>         ERROR at line 1:
>         ORA-00054: resource busy and acquire with NOWAIT specified
>         ORA-06512: at line 2
>
> What I want is for the second session to "automatically" get that error
> for all operations, (i.e. delete and update with no special options in the
> command) not just in "select for update"
>
> Ideally I could set that at the start of a section of code, and then
> restore what ever the original default behaviour was afterwards.
>
> But how to do any of that?
>
> Thanks for feedback.

Look in the SQL manual. If you do not see the nowait option as being listed then it is not available nor can you behind the scenes apply a non-existent option to all execution of a DML statement.

I think you are out of luck though you could code user functions and packaged cursors that your application calls instead of issueing SQL directly and via the stored code provide the option, at least for select for update.

HTH -- Mark D Powell -- Received on Sat May 24 2008 - 08:09:34 CDT