ARTICLES

Controlling the "ORA-04091 Table is mutating, trigger/function may not see it .." error using autonomous transactions

By John Adolph Palinski

Oracle has given developers a nice tool to contol modifications to the database.  This tool is the database trigger.  Database triggers are PL/SQL code blocks that are executed as the result of a DML (Insert, Update, Delete) transaction against a table.  Database triggers are excellent tools because Oracle always executes the trigger script.  It is not application dependent.  This means the code is always executed and is not dependant upon a developer remembering to place the code in the application.  Database triggers can be used to make values upper-case, generating artificial primary key values, or performing special calculations.  However, developers occasionally encounter the "ORA-04091: Table is mutating" error when using database triggers.

The "ORA-04091" error occurs when the trigger modifies the same table record that the trigger's script modified.  This is essentially two different transactions against the same table and is not allowed. To illustrate such a situtation, assume that you are responsible for a Salesman table.  The Salesman table has an attribute called Manager.  There is a unary relationship on the Salesman table.  A manager is also a salesman.  Thus, the Manager column value is actually a foreign key to the Salesman_number column in the Salesman table.  Figure 1 illustrates the DDL for this table:

Figure 1 - Salesmen Create statement containing a Foreign Key constraint

create table salesmen
(salesman_number      number primary key,
 last_name                varchar2(20),
 manager                  number references salesmen);   

Further assume that you would like to create a database trigger that removes or makes null the Salesman_number value from the Manager column when a manager is removed from the database.  Removing this value is required before the record can be deleted since a Foreign Key constraint exists.  The Foregin Key constraint prevents the deletion of a record if it is referenced by a Foreign Key constraint.  A Before Delete database trigger is an ideal candidate for this operation.  The trigger can update the Manager column before the manager's record is deleted.  Figure 2 depicts this trigger.

Figure 2 - Database trigger that sets the Manager value to null when the manager's record is deleted 

Create or replace trigger salesmen_delete_trigger
Before delete
On salesmen
For each row
Begin
  Update salesmen
  Set manager = null
  Where manager = :new.salesman_number;
End;
/

After the database trigger is in place, issuing a Delete statement to remove a record results in the "ORA-04091: table Salesman is mutating..." error message depicted in Figure 3. 

Figure 3 -  Delete statement causing the "ORA-04091" error 

SQL> delete from salesmen
  2  where salesman_number = 1;
delete from salesmen
            *
ERROR at line 1:
ORA-04091: table SALESMEN is mutating, trigger/function may not see it
ORA-06512: at "SALESMEN_DELETE_TRIGGER", line 2
ORA-04088: error during execution of trigger 'SALESMEN_DELETE_TRIGGER'

SQL>

The error was caused by attempting to delete a record from the Salemen table that has a database lock as the result of a previous transaction.  The lock was the result of the Update statement issued by the Salesman_delete_trigger database trigger.  To remove the lock, a Commit must be executed before the actual delete operation occurs.  It is not possible to perform the commit within this trigger.  The same error occurs.  The best solution is to use an autonomous transaction. 

Autonomous transactions are transactions that are started by another transaction.  A transaction is a series of statements that perform a logical unit of work.  Examples of a transaction are DML statements, stored procedures, and database triggers.  The "ORA-04091" error occurs as a result of the same transaction attempting to modify the same object.  Thus, an autonomous transaction is the answer.  Autonomous transactions allow the developer to suspend the current transaction and perform another transaction before starting up.  To solve the above situation, the database trigger can be modified to call an autonomous transaction that performs the Update/Commit operations.  This will overcome the "ORA-04091" problem by having two different transactions modify the same object (i.e the Salemen table).

To create an autonomous, create a stored procedure and add the compiler directive "pragma autonomous_transaction".  The compiler will then consider the called stored procedure a transaction separate from the calling procedure.  Figure 4 depicts a stored procedure using the directive.  This stored procedure contains the Update and Commit statements that had been in the database trigger.  Separating these operations into separate transactions will eliminate the "ORA-04091" error.