update nowait only with for update?
795241Sep 1 2010 — edited Sep 1 2010Hello,
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