Oracle: no wait / no fail on update statement

Question

I have many competing update statements in a multi-application environment. With the current design, deadlocks are frequent.

All but one of the updates can be skipped if necessary and updated at the next interval.

Can I apply NOWAIT to the update statement? Can I have my statement silently fail when it can't obtain a lock? Can I specify the length time it attempts to get a lock?

Solution 1

No you cannot specify NOWAIT on the update statement - the NOWAIT must be specified on the SELECT FOR UPDATE statement.

Yes, you can silently fail a select for update - If you specify NOWAIT and handle the exception that is generated:

BEGIN
  SELECT x FROM table FOR UPDATE NOWAIT;
EXCEPTION
  WHEN OTHERS THEN
    <handle exception>
END;

Yes, a length of time can be specified to wait. In place of the NOWAIT in the above example, specify WAIT n, where n is the number of seconds to wait for the lock. If you can't get the lock in that time, it will fail again with the ORA-00054, which you can handle as before.

Solution 2

Never silently catch "others". In this case you should catch the "resource_busy exception ORA-00054".

declare
    resource_busy         exception;
    pragma exception_init(resource_busy,-54);
begin
    select x into z from table for update nowait;
exception
    when resource_busy
    then
        --Do something
end;