Ошибка ora 04091

Время на прочтение
3 мин

Количество просмотров 37K

Рис.1 – художники мутанты ниндзя черепашки

Мутирование таблиц (ошибка ORA-04091) возникает, если в триггере уровня строки выполняется изменение или чтение данных из той же самой таблицы, для которой данный триггер должен был сработать.

Рассмотрим два способа обхода данной ситуации. Первый – через пакет – древний, как удар маваши гери с разворота, смотрится эффектно, но долго готовится и сложен в исполнении. Второй – более свежий и простой – с использованием составных триггеров.

create table turtles 
as
select 'Сплинтер' name, 'Крыса' essence from dual union all
select 'Леонардо', 'Художник' from dual union all
select 'Рафаэль', 'Художник' from dual union all
select 'Микеланджело', 'Художник'  from dual union all
select 'Донателло', 'Художник'  from dual;

Условимся, что когда Сплитер из крысы мутируют в сэнсэя, художники должны будут автоматически превратиться в ниндзя. Казалось бы, для этого должен подойти такой триггер

create or replace trigger tr_turtles_bue
before update of essence
on turtles
for each row
when (
  new.name = 'Сплинтер' and old.essence = 'Крыса' and new.essence = 'Сэнсэй'
)
begin
  update turtles
     set essence = 'Ниндзя'
   where essence = 'Художник';  
end; 

Но при попытке обновить запись

update turtles
   set essence = 'Сэнсэй'
 where name = 'Сплинтер'

возникает
ORA-04091: table SCOTT.TURTLES is mutating, trigger/function may not see it

Удалим этот триггер

drop trigger tr_turtles_bue;

Способ обхода 1. С помощью пакета и триггера уровня инструкции.

create or replace package pkg_around_mutation 
is
  bUpdPainters boolean;
  procedure update_painters;  
end pkg_around_mutation;
/

create or replace package body pkg_around_mutation
is
  procedure update_painters
  is
  begin   
    if bUpdPainters then
      bUpdPainters := false;
      update turtles
         set essence = 'Ниндзя'
       where essence = 'Художник';
    end if;
  end;  
end pkg_around_mutation;
/

create or replace trigger tr_turtles_bue
before update of essence
on turtles
for each row
when (
  new.name = 'Сплинтер' and old.essence = 'Крыса' and new.essence = 'Сэнсэй' 
)
begin
  pkg_around_mutation.bUpdPainters := true;  
end tr_turtles_bue; 
/

create or replace trigger tr_turtles_bu
after update
on turtles
begin
  pkg_around_mutation.update_painters;  
end tr_turtles_bu;
/ 

Способ обхода 2. С помощью составного триггера (compound DML triggers).
Доступно, начиная с Oracle 11g.

create or replace trigger tr_turtles_ue
  for update of essence
  on turtles
  compound trigger
    bUpdPainters  boolean;
 
  before each row is
  begin
    if :new.name = 'Сплинтер' and :old.essence = 'Крыса' and :new.essence = 'Сэнсэй' then
      bUpdPainters := true;
    end if;
  end before each row;
  
  after statement is
  begin
    if bUpdPainters then
      update Turtles
         set essence = 'Ниндзя'
       where essence = 'Художник';
    end if;
  end after statement;
end tr_turtles_ue; 

Пробуем

update turtles
   set essence = 'Сэнсэй'
 where name = 'Сплинтер'

Даже если вы столкнулись с более сложным случаем мутации, можно использовать приведенную идею обхода. Она, во-первых, связана с тем, что в триггере уровня инструкции, в отличие от триггера уровня строки, мутации не возникает. Во-вторых, можно использовать либо переменные (признаки, защелки, таблицы PL SQL) в специально созданном вспомогательном пакете, либо переменные, глобальные для всех секций составного триггера, что более предпочтительно, начиная с версии Oracle 11g.

Так что теперь и вы знаете кунг-фу. До новых встреч.

Дополнительные материалы: Compound DML Triggers, Мутирование таблиц

TL;DR: Не надо использовать триггер там, где в этом нет никакой необходимости.

Удалите триггер, перенесите логику из триггера в то место, где вызывается DML выражение, которое приводит к срабатыванию триггера. Создайте там процедуру или функцию, если эта логика используется не единожды в приложении.

Перед тем как создать триггер, надо убедится в его необходимости ознакомившись с рекомендациями по применению триггеров, например в офф. документации.

Само появление ошибки — ORA-04091: table is mutating, говорит, что допущена логическая ошибка в дизайне кода и БД вынуждена прибегнуть к защите от возможной потери целостности данных в мульти-пользовательской среде.

Выше изложеное не раз обсуждалось на различных ресурсах, например, цитирую Тома Кайта на спроси Тома:

My personal opinion — when I hit a mutating table error, I’ve got a serious fatal flaw in my logic.

Have you considered the multi-user implications in your logic? Two people inserting at the same time (about the same time). What happens then??? Neither will see eachothers work, neither will block — both will think «ah hah, I am first»…

anyway, you can do too much work in triggers, this may well be that time — there is nothing wrong with doing things in a more straightforward fashion (eg: using a stored procedure to implement your transaction)

PS Кроме того, не возникнет ситуация как в вопросе, где с наибольшей долей вероятности, поиск ошибки идёт не в том триггере, который эту ошибку действительно вызвал.

Summary:

In this post, I will explain what’s this ORA-04091 error is and why you are frequently getting it. After that I demonstrate how to fix Oracle Mutating Trigger Table Error (Ora-04091).

The table is the most important part of the Oracle database. This database works as RDBMS (Relational Database Management System) and to relate the stored information with each other table is used. In a table, all entered information is stored and relate to each other. And if the table of any database gets corrupted then all the stored data gets inaccessible or unreachable.

Oracle database has a different trigger concept as MS SQL Server; it has a set-based approach. All the rows that are exaggerated by the data alteration like the update, insert, delete are stored in the deleted and stored tables. A regular trigger DML is used in MS SQL, it regularly executes after the statement. Before the image is stored in the deleted table and after the image stored in the inserted table and both are accessed within the trigger. You can also join the deleted and inserted table and used them to update the table.

Recursive trigger error and mutating table error are related in the logic that they are invoked when the table on which the trigger has been called up is customized or accessed by a similar trigger but should not be perplexed with each other. When users doing modification on a similar table in a per statement trigger so that trigger fire recursively again and recursion error occurs.

To understand Oracle Mutating Trigger Table Error more clearly suppose a statement executed the custom PL/SQL function or trigger. That statement’s trigger or function is trying to change the table that is already being modified by the statement which fired the function/trigger.

Error Detail:

Error code: ORA-04091

Error description: ORA-04091: table name is mutating, trigger/function may not see it

Screenshot:

What Causes Oracle Mutating Trigger Table Error?

Oracle mutating trigger table error (ORA-04091) mainly comes when the row-level trigger attempting to modify or examine those tables which are already undertaking changes through the DELETE, INSERT, or UPDATE statement.

Particularly, this error arises when the row-level trigger tries to write or read those tables from which the trigger was executed.

How To Fix Oracle Mutating Trigger Table Error (Ora-04091)?

Method 1# Rewrite The Trigger As Statement-Level Trigger

Oracle Mutating Trigger Table Error only put an impact on the row-level triggers. So to fix this issue the very option that you must try is rewriting the trigger as the statement-level trigger.

For using the statement level trigger, the very first thing you need to do is to preserve some data from each row. Later on, it will be used by the statement level trigger.

You can store this data either in a temporary table or in the PL/SQL collection. For achieving the required output, a row-level trigger that causes the mutating trigger table error may result in a complicated statement-level trigger.

Follow these triggers criteria before using it:

• Triggers don’t commit transactions. So, if any transaction is rolled back, then entire data modified by the trigger also been rolled back.
• In the trigger body, you can’t use the save points, Commits, and rollbacks. The entire transaction is get affected by this commit/rollback.
• Unhandled exceptions within the trigger result in the rollbacking of the entire transaction. So don’t consider it only as a trigger.
• On the delete triggers have no: NEW values.
• When several triggers are defined in the event, their order of being fired is not defined. So if you want that the trigger must be fired in order then you have to create one separate trigger which will execute all the action in order.
• On the insert triggers have no: OLD values.
• One trigger can easily affect other events for executing their triggers.

Method 2# Avoid Using Triggers

Another way to avoid oracle mutating trigger table error is not to use the triggers.

Currently, the object-oriented Oracle offers “methods” which are mainly associated with database tables. So, even the several PL/SQL developers try to avoid usage of triggers until and unless it is very much necessary.

Method 3# Use The “after” Or “instead of” Trigger 

To sort out the mutating trigger table error another possible solution that you can try is using the “after” or “instead of” trigger. As, this will help you to avoid the currency issue regarding the mutating table.

Suppose, using the trigger “:after update on xxx”, the table will not get mutated and the original update will get over.

Method 4# Use Autonomous Transactions And Trigger Syntax

To avoid this Mutating Trigger Table Error you can use the combination of statement-level triggers and row-level.

Apart from this, you can also mark your trigger as an autonomous transaction which will make it independent from the database table which calls up the procedure.

Method 5# Go With The Recommended Option

To fix any big corruption or to repair any large database you can use any third-party repair tool like Oracle File Repair tool. This tool very effectively repairs corrupted or damaged Oracle databases without any data loss. If your database or table of the database gets corrupted and displays any error message then you can apply this tool to repair and recover the corrupted data.

This tool provides users with a very simple user interface so that it doesn’t require typing any commands on the command line. So download the Oracle database repair tool and apply it to recover your corrupted database table.

It is a professional tool and it is capable of fixing any errors related to the Oracle. It is a powerful and comprehensive software and it can be used to recover Oracle database file that is corrupted or damaged.

Steps To Repair Corrupt Or Damaged Oracle Databases

Step 1: Search the Initial screen of Oracle File Repair tool with a pop-up window showing options to select or search corrupt Oracle databases on your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Wrap Up:

I have shared all the possible fixes to resolve Oracle error 04091. But if you are getting any kind issue meanwhile performing these fixes then let me know by asking your queries on our FB and Twitter page.

So share your problems which you are frequently getting meanwhile working with the Oracle database.  I will try my best to provide the best possible solution to fix it…!