Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

update nowait only with for update?

795241Sep 1 2010 — edited Sep 1 2010
Hello,

Aparently there is no "nowait" option for simple sql update statements. It exists only in "select .... for update nowait".
As oracle does a implicit row lock anyway upon execution of a normal update statement, i thought it would be handy
if I could write the following statement in order to not have to wait if another session is locking the current row:

update my_table set attr_y = 'val_z' where my_key = 123 nowait;

As this doesn't exist, I tried the following:

declare
v_key number;
begin
select my_key into v_key from my_table where my_key = 123 for update nowait;
update my_table set attr_y = 'val_z' where my_key = 123;
commit;
end;

When I block this record with another session (update without commit), this perfectly works,
I get the desired exeption.

Now this is probably not "the way to do this". Are there any other solutions?

Thanks for any help,
Martin
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2010
Added on Sep 1 2010
1 comment
1,545 views