Re: Q: forcing failures when rows are locked.

From: Dan Blum <>
Date: Sat, 24 May 2008 18:55:56 +0000 (UTC)
Message-ID: <g19obs$19r$>

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?

alter session set isolation_level = serializable

will do something much like what you want (it works at the transaction level, not the statement level). You should test a bunch of scenarios to make sure you understand the behavior.

Dan Blum					
"I wouldn't have believed it myself if I hadn't just made it up."
Received on Sat May 24 2008 - 13:55:56 CDT

Original text of this message