Re: Q: forcing failures when rows are locked.

From: DA Morgan <>
Date: Sat, 24 May 2008 09:50:51 -0700
Message-ID: <>

Malcolm Dew-Jones wrote:
> "Oracle Database 10g Enterprise Edition Release - 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 (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sat May 24 2008 - 11:50:51 CDT

Original text of this message