FOR UPDATE , NO WAIT | Oracle

When a SELECT statement picks the data from any table, the rows are not locked. And while the user performs some operation on the data, there is a chance that some other user may delete or modify the data. 

To resolve this, we can use the FOR UPDATE clause, which locks the rows picked up in the select statement and releases the locks only after the code block is exited. Ex- 

CURSOR cursor_name
IS
   select_statement

   FOR UPDATE [OF column_list] [NOWAIT] ;

This cursors select statement has put a lock on the selected rows and will be released only after the cursor is closed.

The NOWAIT clause is optional and it means that the control will not wait for the resources to be available. the clause provides the count of seconds until when the control waits for the resources to be available.

Mutating Trigger | Oracle

Found this small reference to the mutating trigger error.

The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example:

1. you insert a row in table A.
2. A trigger on table A (for each row) executes a query on table A, for example to compute a summary column.
3. Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it

This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees:

• (i) that each statement is atomic (i.e will either fail or succeed completely)
• (ii) that each statement sees a consistent view of the data

Most likely when you write this kind of trigger you would expect the query (2) to see the row inserted on (1). This would be in contradiction with both points above since the update is not finished yet (there could be more rows to be inserted).

Oracle could return the result consistent with a point in time just before the beginning of the statement but from most of the examples I have seen that try to implement this logic, people see a multi-row statement as a series of successive steps and expect the statement [2] to see the changes made by the previous steps. Oracle can not return the expected result and therefore throws the error.

If as I suspect the cause of the mutating table error is a trigger, one way to avoid the error is to move the logic out of the trigger into procedures.