Re: Q: forcing failures when rows are locked.

Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca> 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?

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					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."