How to avoid mutation on database tables

What is mutation ?

Clear definition here – http://www.databasejournal.com/features/oracle/article.php/3329121/The-Mutation-error-in-Oracle-Database-Triggers.htm

When will you come across mutation?

In practice, you will come across mutation problems when you are trying to modify a table in the database while that same table is getting updated by another process.

A typical scenario

Think of sort in a table. You let the client modify the sort order for each entry on a table. To accomplish this, you will create a trigger on update, that takes the new sort value of the row that was modified and changes the entries before and after accordingly. So if you have 10 employees, each with incremental sort orders from 1 to 10, changing number 6 to number 7 will perform the following logic.

– Find the employee with a sort order of 7, change it to 6.

Notice that during this operation, you will have to update the same table inside the update trigger. This is a classic Oracle mutation issue. Usually you will get a

ORA-04091: table TRANSACTIONS is mutating, trigger/function may not see it

 

Solution

When you research online, you will find a variety of solution. Asktom’s solution will suggest that you create a custom view based on that table, and then create a trigger on that view which updates the original table. This didn’t really work for us.

Some other solutions also mentioned creating a new package etc.

What works perfectly is inside the trigger, create an autonomous context in the code. This tells oracle to  consider the chunk of code that is inside the autonomous context annotation to work in a new session from the rest of the procedure.

Consider the following for example,

CREATE OR REPLACE TRIGGER TRG_UPDATE
AFTER UPDATE ON TABLEX
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; — This is where the magic happens
BEGIN

UPDATE TABLEX …
COMMIT;

END TRIGGER TRG_UPDATE;

Related Links and Solutions

 

http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems002.htm