Re: Q: forcing failures when rows are locked.
Date: 2 Jun 2008 13:48:36 -0800
Malcolm Dew-Jones (yf110_at_vtn1.victoria.tc.ca) wrote:
: "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
: In one session I lock a row
: select the_ID, COMMENT_TXT into :id,:txt
: from the_table
: where the_id = 1234
: for update nowait ;
: 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