Malcolm Dew-Jones <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote:


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

Malcolm Dew-Jones <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote:


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

Malcolm Dew-Jones <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote:


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

Malcolm Dew-Jones (yf110 (AT) vtn1 (DOT) 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.

Malcolm Dew-Jones (yf110 (AT) vtn1 (DOT) 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.

Malcolm Dew-Jones (yf110 (AT) vtn1 (DOT) 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.

Malcolm Dew-Jones (yf110 (AT) vtn1 (DOT) 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.