Q: forcing failures when rows are locked.

#1

"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.

#2

On May 23, 9:11*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

"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.

Look in the SQL manual. If you do not see the nowait option as being
listed then it is not available nor can you behind the scenes apply a
non-existent option to all execution of a DML statement.

I think you are out of luck though you could code user functions and
packaged cursors that your application calls instead of issueing SQL
directly and via the stored code provide the option, at least for
select for update.

HTH -- Mark D Powell --

#3

On May 23, 9:11*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

"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.

Look in the SQL manual. If you do not see the nowait option as being
listed then it is not available nor can you behind the scenes apply a
non-existent option to all execution of a DML statement.

I think you are out of luck though you could code user functions and
packaged cursors that your application calls instead of issueing SQL
directly and via the stored code provide the option, at least for
select for update.

HTH -- Mark D Powell --

#4

On May 23, 9:11*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

"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.

Look in the SQL manual. If you do not see the nowait option as being
listed then it is not available nor can you behind the scenes apply a
non-existent option to all execution of a DML statement.

I think you are out of luck though you could code user functions and
packaged cursors that your application calls instead of issueing SQL
directly and via the stored code provide the option, at least for
select for update.

HTH -- Mark D Powell --

#5

On May 23, 9:11*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:

Quote:

"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.

Look in the SQL manual. If you do not see the nowait option as being
listed then it is not available nor can you behind the scenes apply a
non-existent option to all execution of a DML statement.

I think you are out of luck though you could code user functions and
packaged cursors that your application calls instead of issueing SQL
directly and via the stored code provide the option, at least for
select for update.

HTH -- Mark D Powell --

#6

Malcolm Dew-Jones wrote:

Quote:

"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.

Oracle has created a specific syntax for performing a specific function
and what you seem to be saying is that you want to not use the proper
documented method but so whatever you want and have a multi-billion
dollar software company, with hundreds of thousands of clients, alter
the way its software works just for you. You know the answer. <g>

FOR UPDATE is there for a reason: Use it!
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#7

Malcolm Dew-Jones wrote:

Quote:

"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.

Oracle has created a specific syntax for performing a specific function
and what you seem to be saying is that you want to not use the proper
documented method but so whatever you want and have a multi-billion
dollar software company, with hundreds of thousands of clients, alter
the way its software works just for you. You know the answer. <g>

FOR UPDATE is there for a reason: Use it!
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#8

Malcolm Dew-Jones wrote:

Quote:

"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.

Oracle has created a specific syntax for performing a specific function
and what you seem to be saying is that you want to not use the proper
documented method but so whatever you want and have a multi-billion
dollar software company, with hundreds of thousands of clients, alter
the way its software works just for you. You know the answer. <g>

FOR UPDATE is there for a reason: Use it!
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#9

Malcolm Dew-Jones wrote:

Quote:

"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.

Oracle has created a specific syntax for performing a specific function
and what you seem to be saying is that you want to not use the proper
documented method but so whatever you want and have a multi-billion
dollar software company, with hundreds of thousands of clients, alter
the way its software works just for you. You know the answer. <g>

FOR UPDATE is there for a reason: Use it!
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#10

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

Quote:

"Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production

Quote:

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

Quote:

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