Re: Q: forcing failures when rows are locked.

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 2 Jun 2008 13:48:36 -0800
Message-ID: <48445ca4$1@news.victoria.tc.ca>


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"

I did not correctly appreciate that the nowait could lock an arbitrary set of rows all at once.

	for rec in 
	( select the_ID, COMMENT_TXT 
	  from the_table
	  where -condition-matching-numerous-rows-
	  for update nowait
	) 
	loop
	  exit ;
	end loop;

	If we get here then _all_ the rows are now locked.  Otherwise we
	would have got the -54 error.

	The admin can now use any other SQL command (that targets those
	rows) with no unexpected delays, and all the FORMS users are
	locked out of exactly those rows the admin is targetting for the
	mass update.
Received on Mon Jun 02 2008 - 16:48:36 CDT

Original text of this message