Q: forcing failures when rows are locked.

"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. Received on Fri May 23 2008 - 20:11:57 CDT