Re: Q: forcing failures when rows are locked.

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 24 May 2008 09:50:51 -0700
Message-ID: <1211647865.226507@bubbleator.drizzle.com>


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.

Oracle has created a specific syntax for performing a specific function and what you seem to be saying is that you want to not use the proper documented method but so whatever you want and have a multi-billion dollar software company, with hundreds of thousands of clients, alter the way its software works just for you. You know the answer. <g>

FOR UPDATE is there for a reason: Use it!

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat May 24 2008 - 11:50:51 CDT

Original text of this message