Learn to avoid the mutating table problem in Oracle

Mutating Table Exceptions

Mutating table exceptions occur when we try to reference the triggering table in a query from within row-level trigger code. In this article I'll present examples of how a mutating table exception might occur and simple methods to get round it.

• Test Schema
• Mutating Table Demonstration
• Solution 1 (Collection in Package Variable)
• Solution 2 (Global Temporary Table)

Test Schema

The following schema objects are necessary to run the code in this article.

CREATE TABLE tab1 (
  id           NUMBER(10) NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

ALTER TABLE tab1 ADD (
  CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE tab1_seq;


CREATE TABLE tab1_audit (
  id            NUMBER(10) NOT NULL,
  action        VARCHAR2(10) NOT NULL,
  tab1_id       NUMBER(10),
  record_count  NUMBER(10),
  created_time  TIMESTAMP
);

ALTER TABLE tab1_audit ADD (
  CONSTRAINT tab1_audit_pk PRIMARY KEY (id)
);

ALTER TABLE tab1_audit ADD (
  CONSTRAINT tab1_audit_tab1_fk FOREIGN KEY (tab1_id)
  REFERENCES tab1(id)
);

CREATE SEQUENCE tab1_audit_seq;

Mutating Table Demonstration

Let's assume we need to audit the actions on the parent table and for some reason, this involves querying the triggering table. We can demonstrate this with the following package and trigger.

We place all our trigger code into a package as follows.

CREATE OR REPLACE PACKAGE trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2);

END trigger_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
  l_count  NUMBER(10) := 0;
BEGIN
  SELECT COUNT(*)
  INTO   l_count
  FROM   tab1;

  INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
  VALUES (tab1_audit_seq.NEXTVAL, p_action, p_id, l_count, SYSTIMESTAMP);
END tab1_row_change;

END trigger_api;
/
SHOW ERRORS

Next we create the row-level trigger itself to catch any changes to the table.

CREATE OR REPLACE TRIGGER tab1_ariu_trg
AFTER INSERT OR UPDATE ON tab1
FOR EACH ROW
BEGIN
  IF inserting THEN
    trigger_api.tab1_row_change(p_id => :new.id, p_action => 'INSERT');
  ELSE
    trigger_api.tab1_row_change(p_id => :new.id, p_action => 'UPDATE');
  END IF;
END;
/
SHOW ERRORS

If we try to insert into the TAB1 table we might expect the insert to complete and the audit record to be created but as you can see below this is not the case.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE');
INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE')
                                           *
ERROR at line 1:
ORA-04091: table TEST.TAB1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRIGGER_API", line 7
ORA-06512: at "TEST.TAB1_ARIU_TRG", line 3
ORA-04088: error during execution of trigger 'TEST.TAB1_ARIU_TRG'

SQL>

Solution 1 (Collection in Package Variable)

We can get round this issue by using a combination of row-level and statement-level triggers. First we alter the TRIGGER_API package to store any data passed by the row-level trigger in a PL/SQL table. We also add a new statement-level procedure to process each of the rows in the PL/SQL table.

CREATE OR REPLACE PACKAGE trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2);

PROCEDURE tab1_statement_change;

END trigger_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY trigger_api AS

TYPE t_change_rec IS RECORD (
  id      tab1.id%TYPE,
  action  tab1_audit.action%TYPE
);

TYPE t_change_tab IS TABLE OF t_change_rec;
g_change_tab  t_change_tab := t_change_tab();

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
BEGIN
  g_change_tab.extend;
  g_change_tab(g_change_tab.last).id     := p_id;
  g_change_tab(g_change_tab.last).action := p_action;
END tab1_row_change;

PROCEDURE tab1_statement_change IS
  l_count  NUMBER(10);
BEGIN
  FOR i IN g_change_tab.first .. g_change_tab.last LOOP
    SELECT COUNT(*)
    INTO   l_count
    FROM   tab1;

    INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
    VALUES (tab1_audit_seq.NEXTVAL, g_change_tab(i).action, g_change_tab(i).id, l_count, SYSTIMESTAMP);
  END LOOP;
  g_change_tab.delete;
END tab1_statement_change;

END trigger_api;
/
SHOW ERRORS

Our existing row-level trigger is fine, but we need to create a statement-level trigger to call our new procedure.

CREATE OR REPLACE TRIGGER tab1_asiu_trg
AFTER INSERT OR UPDATE ON tab1
BEGIN
  trigger_api.tab1_statement_change;
END;
/
SHOW ERRORS

The TAB1 inserts/updates will now work without mutation errors.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE');

1 row created.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'TWO');

1 row created.

SQL> UPDATE tab1 SET description = description;

2 rows updated.

SQL> SELECT * FROM tab1;

        ID DESCRIPTION
---------- -----------
         2 ONE
         3 TWO

2 rows selected.

SQL> COLUMN created_time FORMAT A30
SQL> SELECT * FROM tab1_audit;

        ID ACTION        TAB1_ID RECORD_COUNT CREATED_TIME
---------- ---------- ---------- ------------ ------------------------------
         1 INSERT              2            1 03-DEC-03 14.42.47.515589
         2 INSERT              3            2 03-DEC-03 14.42.47.600550
         3 UPDATE              2            2 03-DEC-03 14.42.49.178678
         4 UPDATE              3            2 03-DEC-03 14.42.49.179655

4 rows selected.

SQL>

There is a flaw in this solution. Sometimes Oracle chooses to rollback changes and rerun the DML. If this happens, the contents of the PL/SQL table will not be cleaned up because it is not transactional. For this reason it is better to use the temporary table approach.

 The introduction of Compound Triggers in Oracle 11g Release 1 makes solving mutating table errors much simpler as a single trigger definition can perform most of the actions.

Solution 2 (Global Temporary Table)

An alternative strategy is to use a Global Temporary Table (GTT) in place of the collection. This is a better solution as the contents of the global temporary table are transactions, and so work as expected for automatic reruns of DML. First we need to create the temporary table to hold the data.

CREATE GLOBAL TEMPORARY TABLE tab1_mods (
  id      NUMBER(10),
  action  VARCHAR2(10)
) ON COMMIT DELETE ROWS;

Next, we recreate the package body to use the global temporary table in place of the collection.

CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_id      IN  tab1.id%TYPE,
                           p_action  IN  VARCHAR2) IS
BEGIN
  INSERT INTO tab1_mods (id, action) VALUES (p_id, p_action);
END tab1_row_change;

PROCEDURE tab1_statement_change IS
  l_count  NUMBER(10);
BEGIN
  FOR i IN (SELECT * FROM tab1_mods) LOOP
    SELECT COUNT(*)
    INTO   l_count
    FROM   tab1;

    INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
    VALUES (tab1_audit_seq.NEXTVAL, i.action, i.id, l_count, SYSTIMESTAMP);
  END LOOP;
  DELETE FROM tab1_mods;
END tab1_statement_change;

END trigger_api;
/
SHOW ERRORS

Once again, the inserts and updates work as expected.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'THREE');

1 row created.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'FOUR');

1 row created.

SQL> UPDATE tab1 SET description = description;

2 rows updated.

SQL> SELECT * FROM tab1;

        ID DESCRIPTION
---------- -----------
         2 ONE
         3 TWO
         4 THREE
         5 FOUR

4 rows selected.

SQL> COLUMN created_time FORMAT A30
SQL> SELECT * FROM tab1_audit;

        ID ACTION        TAB1_ID RECORD_COUNT CREATED_TIME
---------- ---------- ---------- ------------ ------------------------------
         1 INSERT              2            1 23-NOV-2011 13:24:48.300250
         2 INSERT              3            2 23-NOV-2011 13:24:54.744229
         3 UPDATE              2            2 23-NOV-2011 13:25:01.170393
         4 UPDATE              3            2 23-NOV-2011 13:25:01.170837
         5 INSERT              4            3 23-NOV-2011 13:27:03.765868
         6 INSERT              5            4 23-NOV-2011 13:27:10.651991
         7 UPDATE              2            4 23-NOV-2011 13:27:18.097429
         8 UPDATE              3            4 23-NOV-2011 13:27:18.097957
         9 UPDATE              4            4 23-NOV-2011 13:27:18.098176
        10 UPDATE              5            4 23-NOV-2011 13:27:18.098801

10 rows selected.

SQL>

Learn to avoid the mutating table problem in Oracle

Tables are everything to IT database managers in ERP database environments. The organization of database tables, their relationships, and the manner in which they are managed are a fair indicator of the overall quality of performance you can expect from the system.

So when something can happen to mess up your tables, you want to watch out for it. In Oracle, there’s a nasty snare out there waiting for you. It's Oracle error ORA-04091, the mutating table error, and the best way to deal with it is to avoid it altogether. This article will explain why.

When tables mutate
There are several causes of mutating tables, but in general a table mutation error will result if you create a trigger on a table that attempts to examine the current table. Also, the error is given when an attempt is made to examine a parent table in a cascading update/delete.

Why are these considered errors? It’s obvious when you think about it: The trigger is trying to change or examine something that’s already being changed. This confuses Oracle, and understandably so. In a row-level trigger firing, when the trigger is about to fire for the second row in a multirow update, what is Oracle to do? Does the application intend that Oracle see the table in its pre-update state? Or does it intend to see it after it has just been updated? Oracle doesn’t know—so it is attempting, by generating the error, to fend off a disaster that your design may not allow for.

The good news is that the potential disaster—the screwing up of your tables—never physically occurs. The Oracle error indicates that Oracle refuses to proceed, on the chance that your tables will mutate. The downside, however, is that your application is dead in the water.

Use autonomous transactions
Autonomous transactions give you a way out. You can actually make your trigger an autonomous transaction. Remember that an autonomous transaction is an embedded block of code that executes from within a parent transaction, but it is treated by Oracle as a completely independent transaction.

This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist, for Oracle does not perceive that there is any choice between an unchanged table and an uncommitted but changed one.

To make your trigger an autonomous transaction, add the statement
PRAGMA AUTONOMOUS TRANSACTION

and do a COMMIT within the trigger.

But you're still not out of danger!
You’ve avoided one trap, but another may be ahead. You may be avoiding a design logic issue that could theoretically lead to table corruption and replacing it with a design logic decision that is incorrect for your application but won’t be caught in time.

Here’s why: If you are using an autonomous transaction as a trigger, you are sidestepping the mutating table error by setting Oracle up to see your initial table in a pre-update state, even if uncommitted changes exist. You can then read from the table as it was before any changes occurred. So there is no potential conflict from Oracle’s point of view.

But what if this means you’re pulling data into your application that will lead you astray? Suppose your application calls for you to read from a table in a process that also modifies it. Right away you’ll get error 04091 unless you set the trigger up as an autonomous transaction. However, suppose you are, in the course of other processing, calculating a running average of values from line items that are having quantities modified in a series. Remember that the trigger you are using causes the autonomous transaction to see the original values, not the updated ones, so what you’ll end up with is the updated quantities you intended in your line items and a completely wrong average value, calculated from the pre-update quantities. And Oracle won’t tell you that you’ve implemented a flawed design. You’ll bang your head against a wall for weeks, trying to find the flaw in the code—when no flaw exists in the code. Unfortunately, you can’t fix this potential error with code. This is purely a design issue. You simply need to walk through the process in your mind and be certain that you’re reading and writing with your table at the right times.

General guidelines to keep your tables safe
If you keep the following guidelines in mind, you’ll know you aren’t building any update conflicts into your table-handling design:

• Always remember that a row-level trigger can’t read or write the table it’s fired from. A statement-level trigger can, however.
• Are you reading or writing? If you need to query the firing table but don’t need to modify it, you can do so with a row-level trigger by making it an autonomous transaction, which circumvents the above restriction.

Like so many things, it comes down to good design. Don’t let the convenience of Oracle table handling lead you into sloppy design. The tips above will aid you in sidestepping the mutating table problem—but your best hedge is, as always, to think long and hard before you code!