Search code examples
oraclelockingdeadlock

Oracle: no wait / no fail on update statement

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

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