Oracle: no wait / no fail on update statement
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.
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 somethingend;