First, what is a mutating table? Second, let's say I execute a PL/SQL block having multiple
INSERT, UPDATE or DELETE statements, with the Set AutoCommit ON command. If the script aborts, will
all the changes be committed or none at all?
First, a mutating table is a table that is in the process of being modified by an UDPATE, DELETE or INSERT statement. A table may also be considered to be mutating if it needs to be updated due to a DELETE CASCADE referential integrity constraint. The reason you may get a mutating table error (i.e. ORA-04091) is that if you fire a trigger which attempts to look at or modify the same table, the trigger is effectively prevented from seeing a consistent view of the data.
For example, let's say that you attempt to give every employee who makes $10/hour a 10% salary increase as follows:
UPDATE employee SET salary = salary * 1.10 WHERE salary = 10 ;
This would mean that the changed salary for all these employees would be $11. Your UPDATE statement caused an AFTER ROW trigger to be fired that contained the following statement: SELECT salary FROM employee WHERE . . .
You would get the mutating table error because the trigger tried to query the original employee table but the original table was in the process of change. Which version of the table should Oracle retrieve when the SELECT is issued? That's the problem! Oracle simply will not allow this statement to occur, because it doesn't have any way of knowing how to provide the correct set of data (i.e. a consistent view of the data). Oracle would generate the ORA-04091 error and rollback both the trigger body and the triggering statement (i.e. the UPDATE).
Bottom-line: you cannot touch the table you are updating, deleting from or inserting into in any trigger or user-defined function code that it may execute.
In response to your second question: SET AUTOCOMMIT ON treats a PL/SQL block as a single transaction. In other words, no matter how many INSERT, UPDATE and DELETE statements that are executed in the PL/SQL block, the commit will happen only at the end of the complete block. Therefore, if the script aborts in the middle of the block, then nothing will be committed. But as long as the block completes successfully, all statements executed within the block will be committed.
This was first published in July 2001