Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I understand mutating table errors are caused by a design flaw or problematic query.

An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how.

What exactly causes mutating table errors and how would our DBA have fixed the problem?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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

For further reading: "mutating table" on Ask Tom.

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.

share|improve this answer

A mutating table occurs when a statement causes a trigger to fire and that trigger references the table that caused the trigger. The best way to avoid such problems is to not use triggers, but I suspect the DBA didn’t take the time to do that. He could have done one of the following:

share|improve this answer

Have a look at the Oracle docs, and this article by Tom Kyte

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.