8 Replies Latest reply: Mar 8, 2013 9:07 AM by John Spencer RSS

    ORA-04091: table  is mutating, trigger/function

    MDK999
      I am using oracle 11g R2 - 11.2.0.1
      I have following function to fetch the ID from name supplied
      create or replace
      FUNCTION get_group_id
      (p_groupname IN group_list.groupname%TYPE)
      RETURN group_list.group_id%TYPE
      AS
      v_group_id group_list.group_id%TYPE;
      BEGIN
      SELECT group_list.group_id
      INTO v_group_id
      FROM group_list
      WHERE group_list.groupname = p_groupname;
      RETURN v_group_id;
      END get_group_id;
      /

      and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

      update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

      Error which I am getting is -
      Error report:
      SQL Error: ORA-04091: table is mutating, trigger/function may not see it
      ORA-06512: at "GET_GROUP_ID", line 7
      04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
      *Cause:    A trigger (or a user defined plsql function that is referenced in
      this statement) attempted to look at (or modify) a table that was
      in the middle of being modified by the statement which fired it.
      *Action:   Rewrite the trigger (or function) so it does not read that table.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        • 1. Re: ORA-04091: table  is mutating, trigger/function
          sb92075
          MDK999 wrote:
          I am using oracle 11g R2 - 11.2.0.1
          I have following function to fetch the ID from name supplied
          create or replace
          FUNCTION get_group_id
          (p_groupname IN group_list.groupname%TYPE)
          RETURN group_list.group_id%TYPE
          AS
          v_group_id group_list.group_id%TYPE;
          BEGIN
          SELECT group_list.group_id
          INTO v_group_id
          FROM group_list
          WHERE group_list.groupname = p_groupname;
          RETURN v_group_id;
          END get_group_id;
          /

          and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

          update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

          Error which I am getting is -
          Error report:
          SQL Error: ORA-04091: table is mutating, trigger/function may not see it
          ORA-04091 occurs when you do SQL against the table upon which the trigger is based.
          • 2. Re: ORA-04091: table  is mutating, trigger/function
            rp0428
            >
            and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

            update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

            Error which I am getting is -
            Error report:
            SQL Error: ORA-04091: table is mutating, trigger/function may not see it
            >
            And do you have a trigger on the GROUP_LIST table that calls you function that queries the GROUP_LIST table?

            The solution is simple: DON'T DO THAT!

            See Trigger Restrictions on Mutating Tables in the SQL Language doc
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#g1699708
            Trigger Restrictions on Mutating Tables
            A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
            
            The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
            
            This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
            
            When a trigger encounters a mutating table, a run-time error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. (You can use compound triggers to avoid the mutating-table error. For more information, see Using Compound Triggers to Avoid Mutating-Table Error.)
            
            Consider the following trigger:
            
            CREATE OR REPLACE TRIGGER Emp_count
            . . .
            • 3. Re: ORA-04091: table  is mutating, trigger/function
              dilipkumar10285
              Hi,

              MDK999 wrote:
              I am using oracle 11g R2 - 11.2.0.1
              I have following function to fetch the ID from name supplied
              create or replace
              FUNCTION get_group_id
              (p_groupname IN group_list.groupname%TYPE)
              RETURN group_list.group_id%TYPE
              AS
              v_group_id group_list.group_id%TYPE;
              BEGIN
              SELECT group_list.group_id
              INTO v_group_id
              FROM group_list
              WHERE group_list.groupname = p_groupname;
              RETURN v_group_id;
              END get_group_id;
              /
              CREATE OR REPLACE FUNCTION GET_GROUP_ID (P_GROUPNAME IN GROUP_LIST.GROUPNAME%TYPE) RETURN GROUP_LIST.GROUP_ID%TYPE
              AS
                   V_GROUP_ID GROUP_LIST.GROUP_ID%TYPE;
                   PRAGMA AUTONOMOUS_TRANSACTION;
              BEGIN
                   SELECT GROUP_LIST.GROUP_ID INTO V_GROUP_ID FROM GROUP_LIST WHERE  GROUP_LIST.GROUPNAME = P_GROUPNAME;
                   RETURN V_GROUP_ID;
              END GET_GROUP_ID;
              You can use Autonomous Transaction. Which will create separate session for your function so it will not cause table mutating.
              Or create view which will return the group_id for group_name.
              Also there should be exception handler in the function.

              Hope this helps.

              Cheers
              Dilipkumar
              • 4. Re: ORA-04091: table  is mutating, trigger/function
                rp0428
                >
                You can use Autonomous Transaction. Which will create separate session for your function so it will not cause table mutating.
                >
                And that 'separate session' will have access to even less data than the trigger since it will NOT be able to see any uncommitted data from the triggering session.

                Transaction-level processes should NOT be performed in triggers.
                • 5. Re: ORA-04091: table  is mutating, trigger/function
                  Paul  Horth
                  MDK999 wrote:
                  I am using oracle 11g R2 - 11.2.0.1
                  I have following function to fetch the ID from name supplied
                  create or replace
                  FUNCTION get_group_id
                  (p_groupname IN group_list.groupname%TYPE)
                  RETURN group_list.group_id%TYPE
                  AS
                  v_group_id group_list.group_id%TYPE;
                  BEGIN
                  SELECT group_list.group_id
                  INTO v_group_id
                  FROM group_list
                  WHERE group_list.groupname = p_groupname;
                  RETURN v_group_id;
                  END get_group_id;
                  /

                  and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

                  update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

                  Error which I am getting is -
                  Error report:
                  SQL Error: ORA-04091: table is mutating, trigger/function may not see it
                  ORA-06512: at "GET_GROUP_ID", line 7
                  04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
                  *Cause:    A trigger (or a user defined plsql function that is referenced in
                  this statement) attempted to look at (or modify) a table that was
                  in the middle of being modified by the statement which fired it.
                  *Action:   Rewrite the trigger (or function) so it does not read that table.
                  You can't do that. To maintain data consistency, Oracle stops you from looking at data from within a function or trigger that
                  is called or triggered by a statement that is modifying the table.

                  You need to do this in plain SQL without function calls (in other words take what the function is doing and put it directly in
                  the update).
                  • 6. Re: ORA-04091: table  is mutating, trigger/function
                    riedelme
                    dilipkumar vishwakarma wrote:
                    You can use Autonomous Transaction. Which will create separate session for your function so it will not cause table mutating.
                    There is a chance of corrupting data if ATs are used in this way.

                    ATs can't see uncommitted data for the parent transaction. That means that you can read a value that has already been changed in the parent transaction and make wrong decisions based on that value. For this reason using ATs in triggers to avoid the dreaded mutating table error is not reccommended.\

                    ATs are more useful for logging. We use them to record error messages, committing the message before returning to the calling transaction to roll back changes and propagate the error so we can see the error message later.
                    • 7. Re: ORA-04091: table  is mutating, trigger/function
                      Carlovski
                      Might be confusing the OP here.
                      There is no trigger in this situation. The mutating table error is being caused by the function. Interestingly I had never seen this behaviour pre 11g, although the documentation implies it isn't new.
                      In this situation Dilipkumar's suggestion of the Autonomous transaction makes a bit more sense, it will give you the expected behaviour of the function. Although even better would be to avoid the function altogether and just go with a SQL solution.
                      • 8. Re: ORA-04091: table  is mutating, trigger/function
                        John Spencer
                        You will need to incorporate the function's logic into your update statement. Something like:
                        update GROUP_LIST set MGR_GROUP_ID = (select group_id from group_list
                                                              where groupname = 'manager1')
                        where group_id = (select group_id from group_list
                                          where groupname = 'employee51');
                        John