Pages

Thursday, 17 January 2013

What is Mutation & How to avoid Mutation



    Orianconsulting.com

The Mutating table error is a well-known problem encountered by most developers has come across this error.

  ORA-04091: table <tablename> is mutating,
            trigger/ function may not see it

Oracle has a very unique feature of read consistent view of data & when developers trying to break this rule Mutation errors come and stop the developer to break the rule So Mutation error is a friend of developers.

Lets Understand Mutation in detail.
  
The basic reason for this error is the way Oracle manages a read consistent view of data at a given point of time.You can only see consistent data. When we try to break this read consistency we face Mutation which simply says that you are breaking the rule and stop you do so and save your data & Object structure from corruption.

Objects with which you will face this error are

  1. Triggers
  1. Functions  
Most of the developers just think it will happen with triggers only but that’s not the case.

When & in which Situation Mutation error occurs with Triggers.

Case 1: When Trigger on table refers the same table:
  
Note: Mutating error with Triggers Occurs only for row level triggers.
not for statement level triggers


Operation
Type
Mutating
Insert
Before Row  Level
Only for Multi Row insert
Insert
After Row  Level
Yes for both
Update
Before Row  Level
Yes for both
Update
After Row  Level
Yes for both
Delete
Before Row  Level
Yes for both
Delete
After Row  Level
Yes for both



The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.

Mutation will not occur if a single record is inserted in the table (using VALUES clause). If bulk insertion is done or data is inserted from another table mutation will occur.
  
The mutating error is not only encountered during queries, but also for insert, updates and deletes present in the trigger.

Key points for before row level Insert Triggers

  1. Only for multiple rows, with sub query syntax even in sub query we select only one value.
  2. After row level triggers generate mutation errors for single and multiple rows


See examples

SQL> create table mtt_same_table (col1 number,col2 varchar2(10));                                                                            
Table created.

SQL> create or replace trigger mutation_on_same_table
    before insert or update or delete on mtt_same_table
    for each row
    declare
    vcount number;
    begin
    select count(*) into vcount from mtt_same_table;
    dbms_output.put_line('mtt');
    end;
   /

Trigger created.

SQL> select * from mtt_same_table;

no rows selected

-- we can insert single row data with before row level triggers

SQL> insert into mtt_same_table values(1,'arun');
                                                                
1 row created.

--when we try to insert multiple rows using sub query 
-- Mutation  error prevent you to do do even sub query  returns no row.

SQL> insert into mtt_same_table select salary , last_name from employees where employee_id=101
 
insert into mtt_same_table select salary , last_name from employees where employee_id=101
         *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- When we try to update mutation error prevents to do so 

SQL> update mtt_same_table set col1=null ;
update mtt_same_table set col1=null
       *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- When we select from sub query ,either its one value or many mutation error you face because oracle don’t know how many rows are there until it fetch the rows

QL> insert into mtt_same_table select 1,'arun' from dual
SQL> /
insert into mtt_same_table select 1,'arun' from dual
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- DELETE example

SQL> delete from mtt_same_table;
 
delete from mtt_same_table
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- Now the After row level triggers and Insert , You face Mutation with single row insertion as well unlike before row level triggers

SQL> create or replace trigger mutation_on_same_table
    After insert or update or delete on mtt_same_table
    for each row
    declare
    vcount number;
    begin
    select count(*) into vcount from mtt_same_table;
    dbms_output.put_line('mtt');
    end;
   /

Trigger created

  1* insert into mtt_same_table values (2,'mass')
SQL> /
insert into mtt_same_table values (2,'mass')
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'



Case 2 when Trigger on table refers the different table

This happens when a foreign key reference is present with an on-delete-cascade option. A row level trigger on the master table will mutate if the detail table is being referred to in the trigger, for a delete transaction. This will only happen if the foreign key on the detail table is created with the on delete cascade option.

No mutation occurs if the master table is being referred in a trigger on the detail table.


-- master table

SQL> create table mtt_master_table (id number primary key ,name varchar2(10));

Table created.

-- Child table with foreign key and on delete cascade constraint.

SQL> create table mtt_child_table (id1 number references mtt_master_table(id) on delete cascade , name varchar2(10));

Table created.

-- Trigger on Master table

SQL>
   create or replace trigger trig_diff_table
    before insert or delete or update on
    mtt_master_table
    for each row
    declare
    vcount number;
    begin
    select count(*) into vcount from mtt_child_table ;
   end;
SQL> /

Trigger created.

-- insert data in master table

SQL> insert into mtt_master_table values(1,'arun');

1 row created.

SQL> insert into mtt_master_table values(2,'varun');

1 row created.

-- insert into child table

SQL> insert into mtt_child_table values(1,'bharat');

1 row created.

SQL>  insert into mtt_child_table values(1,'mbharat');

1 row created.

-- update master table as there are on delete cascade constarint is present which tries to delete the Corresponding record from the child table, and in the trigger we are selecting data from the Child table and data is changing.So mutation prevent to do so.

SQL> select * from mtt_master_table;
 
        ID NAME                                                                
---------- ----------                                                          
         1 arun                                                                
         2 varun                                                                

SQL> select * from mtt_child_table;
 
       ID1 NAME                                                                
---------- ----------                                                           
         1 bharat                                                              
         1 mbharat                                                             

SQL> delete from mtt_master_table where id=2;
 
  delete from mtt_master_table where id=2
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_CHILD_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_DIFF_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRIG_DIFF_TABLE'


-- BUT WE CAN DELETE THE DATA FROM THE CHILD TABLE

SQL> delete from mtt_child_table;
 
2 rows deleted.

SQL> select count(*) from mtt_child_table;

Count(*)
-----------------
     0

 --Even there is no row in the child still we cannot delete from master table

SQL> delete from mtt_master_table
  2  /
delete from mtt_master_table
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_CHILD_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_DIFF_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRIG_DIFF_TABLE'

--we just simply disable the trigger and see the results

SQL> alter trigger trig_diff_table disable;

Trigger altered.

SQL> delete from mtt_master_table ;

2 rows deleted.

We can delete now.

 Case 3 .Mutation error with Functions:

Mutation can occurs with functions as well, many of us think Mutation happens with Triggers only but it’s not true.In a function we are counting the no of rows from table and returning the count.

We are trying to insert into same table using this function.. Which will give us mutation error?

-- CREATE TABLE

  SQL> create table mtt_function_table (id number);

Table created.

-- CREATE FUNCTION WHICH REFER THE MTT_FUNCTION_TABLE

SQL > create or replace function mtt_function return number
     is
     vcount number;

     begin
     select count(*) into vcount from mtt_function_table;
    return vcount;
    end;
   /

Function created

-- Now we are trying to use this functions output in a insert statement as a value and at the same time we are refering the same table so Mutating
function ORA-04091

  SQL>  insert into mtt_function_table select mtt_function() from mtt_function_table;

insert into mtt_function_table select mtt_function() from mtt_function_table
                                      *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_FUNCTION_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MTT_FUNCTION", line 5


SQL>  1* insert into mtt_function_table select mtt_function() from dual;

insert into mtt_function_table select mtt_function() from dual
                                      *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_FUNCTION_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MTT_FUNCTION", line 5



Now the Question is how to avoid this

 
  • Don’t break the rule , don’t use the same table for insertion and selection at the same time , use temporary table or views.
  • Don’t use triggers, modern developers don’t use trigger 
  • If you have requirement that you have to break the rule then there is one more friend of developers Autonomous Transaction
  • Use compound triggers to avoid mutation  Compound triggers



Let us use the autonomous transaction approach for all the above examples we describe.


Case 1: When Trigger on table refers the same table:

We just use the Autonomous transaction Pragma to break the rule here

-- create trigger with Pragma autonomous transaction & see the magic


create or replace trigger mutation_on_same_table
    before insert or update or delete on mtt_same_table
    for each row
    declare
    vcount number;
pragma Autonomous_transaction;              
    begin
    select count(*) into vcount from mtt_same_table;
    dbms_output.put_line('mtt');
    end;
/

--Now as we create the trigger with Pragma autonomous transaction we are ready to break the rule  we can insert multiple value using sub query for a before row level trigger , update and delete from the same table

--single row Insert example

SQL> insert into mtt_same_table values (1,'ar'); 


1 row created.

--multiple rows with sub query example

SQL> insert into mtt_same_table select employee_id ,substr(last_name,1,6) from employees;

109 rows created.

SQL> select count(*) from mtt_same_table;
 
  COUNT(*)                                                                     
----------                                                                      
       110                                                                     

 --update table example

SQL> update mtt_same_table set col1=null ;

110 rows updated.


--delete from table example

SQL> delete from mtt_same_table;

110 rows deleted.

SQL> select count(*) from mtt_same_table ;

  COUNT(*)                                                                     
----------                                                                     
         0


Case 2 when Trigger on table refers the different table

Now we user Pragma autonomous transaction to break this rule with on delete cascade constraint, we will delete the records with trigger referring to the child table

SQL> create or replace trigger trig_diff_table
      before insert or delete or update on
        mtt_master_table
        for each row
       declare
        vcount number;
    pragma autonomous_transaction;
        begin
        select count(*) into vcount from mtt_child_table ;
     end;
   /

Trigger created.

--see the status of the trigger is enabled

SQL> select TRIGGER_NAME,STATUS from user_triggers where trigger_name='TRIG_DIFF_TABLE';

TRIGGER_NAME                   STATUS                                          
------------------------------ --------                                         
TRIG_DIFF_TABLE                ENABLED                                         


--check no of records in master table

SQL> SELECT COUNT(*) FROM MTT_MASTER_TABLE;


  COUNT(*)                                                                      
----------                                                                     
         2                                                                     

--check no of records in child table
SQL> SELECT COUNT(*) FROM MTT_CHILD_TABLE;


  COUNT(*)                                                                     
----------                                                                     
         1  

--delete from master table and see the magic of pragma  Autonomous Transaction                                                                 

SQL> DELETE FROM MTT_MASTER_TABLE;


2 rows deleted.

SQL> SELECT COUNT(*) FROM MTT_MASTER_TABLE;


  COUNT(*)                                                                     
----------                                                                      
         0                                                                     

 --see child records also deleted as we have the on delete cascade and We are referring the same table in trigger

SQL> SELECT COUNT(*) FROM MTT_CHILD_TABLE;

  COUNT(*)                                                                     
----------                                                                     
         0                                                                      

SQL> select TRIGGER_NAME,STATUS from user_triggers where trigger_name='TRIG_DIFF_TABLE';


TRIGGER_NAME                   STATUS                                          
------------------------------ --------                                         
TRIG_DIFF_TABLE                ENABLED                                         


Case 3 .Mutation error with Functions:

Now we insert the function output in the same table , and function is referring the same table


--create function with PRAGMA AUTONOMOUS_TRANSACTION


SQL>   create or replace function mtt_function return number
         is
     vcount number;

       PRAGMA AUTONOMOUS_TRANSACTION;
     begin
   
              select count(*) into vcount from mtt_function_table;
    return vcount;
    end;
   /

Function created.

--now we can insert into the same table function output and while in a function we are refering the same table



SQL>  insert into mtt_function_table select mtt_function() from mtt_function_table;

1 row created.

SQL> insert into mtt_function_table select mtt_function() from dual;

1 row created.


As we all see the magic of Autonomous Transaction Pragma , but It does not mean every time you just use the Pragma to break the rules

See detail uses of Pragama Autonomous Transaction in my next blog.

Your comments suggestions are awaited :0

12 comments:

  1. Dear,

    I want to create trigger after or before insert for each row and in this trigger insert statement on the same table , not just select.

    ReplyDelete
  2. Can You please explain what is the requirement , in which you want to insert rows in the same table before or after for a row level trigger.

    ReplyDelete
  3. too good piece of information, I had come to know about your site from my friend sajid, bangalore,i have read atleast 11 posts of yours by now, and let me tell you, your web-page gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanks a lot once again, Regards, Single Row Function in sql


    ReplyDelete
  4. Thanks Nandkishor .. This is what i like to do, knowledge sharing .. when ever i get time ..

    ReplyDelete
  5. I like the way you explain the concept. Thank you very much!!!

    ReplyDelete
  6. Really the mutating error concept becomes very easy to understand after reading your post... Thanks a lot..Can u pls explain about the performace tuning concepts also...

    ReplyDelete
  7. if you have rows in a table that are dependent on other rows (sums/discounts/etc ) the row-level triggers can write the keys to a pl/sql table and then the after-statement triggers can read those keys, perform the calculations and re-update. http://www.oracle-base.com/articles/11g/trigger-enhancements-11gr1.php

    ReplyDelete
  8. without using oracle sequence, can we insert incremental sequence number into same table id column using Autonomous transaction?

    ReplyDelete
  9. Super boss. Good Explanation.

    ReplyDelete
  10. Could you please explain why we cant use TCL inside the trigger?
    i have searched from many site, but still not getting sufficient answer.

    ReplyDelete