Сергей Герасимов
НПО "Балтрос"

ORA-04091 или как я боролся с мутирующими таблицами.

Источник: http://pbl.narod.ru/mut.txt

Наверное, распростаненная ситуация, когда необходимо решить следующую задачу. Есть таблица, в которой реализован стандартный подход к посторению иерархического хранилища:

create table test_table 
 (id number(10) constraint pk_id primary key,
 id_parent number(10) constraint fk_id references test_table(id),
 prop1 varchar2(10),
 prop2 varchar2(10) 
 );

В строках таблицы хряняться сущности, имеющие свои свойства, и организованные в иерархию.

Часть свойств у каждой сущности индивидуален, а часть - это свойство сущности верхнего уровня.

Например: 
Здание
 Часть здания
 Помещение
 Часть помещения

У всех дочерних записей свойства одинаковое свойство родителя - адрес. Встала задача - при изменении общего для родителя свойства - изменять его у всех дочерних триггером. Естественно, в прямую мои попытки это сделать потерпели полное фиаско:

 ORA-04091 или table name is mutating, trigger/function may not see it.

Вот мой путь решения данной проблемы:

использование "универсальной" дополнительной таблицы и двух триггеров;

один из них before update for each rows,

другой after update for statement.

Универсальная в кавычках, потому что в данном случае это приемлимо только для таблиц с одиночным primary key. Для составного надо накручивать логику и дополнять поля под количество полей в primary key. Работают они так:

При изменении триггер before для каждой измененной строки проверяет, а изменились ли в данной записи общие свойства.

Если да, то записывает в доп. таблицу значение первичного ключа в одно поле и id транзакции в другое.

Id транзакции необходим, чтобы разделить изменения разных пользователей при соответствующем уровне изоляции.

При необходимости можно добавить еще одно поле, куда записывать имя таблицы, в которой производятся изменения, для того, чтобы в одной транзакции изменять несколько "ушастых таблиц"

Дальше триггер after анализирует дополнительную таблицу и производит изменения в исходной таблице, зная, какие первичные ключи подверглись изменениям.

Данный механизм работает каскадно. Важно оценить возможную степень вложенности и объемы изменяемой информации для того, чтобы корректно установить значение OPEN_CURSORS в файле init.ora. По умолчанию = 50 открытых курсоров на сессию.

Примеры триггеров.

 
CREATE OR REPLACE TRIGGER lau_obj
BEFORE UPDATE
ON obj
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
temp_local_tran_id varchar2(100); 
Begin
-- проверка изменения общего свойства
if <условие> then 
 temp_local_tran_id:=dbms_transaction.local_transaction_id;
 -- запись первичного ключа в доп. таблицу
 insert into temp_ehcap_obj 
 values(:new.obj_id, temp_local_tran_id);
end if;
End;
/
 
-- разбор доп. таблицы в о втором триггере
CREATE OR REPLACE TRIGGER lsu_obj
AFTER UPDATE
ON obj
REFERENCING NEW AS NEW OLD AS OLD
declare
 -- объявляем параметризованный курсор, 
 -- в котором выберем все строки, 
 -- которые сделаны текущей транзакцией
 CURSOR my_tran (par_local_tran_id IN varchar2) IS
 select obj_id, local_tran_id
 from temp_ehcap_obj
 where local_tran_id = par_local_tran_id ;
 temp_local_tran_id varchar2(100);
 temp_1 varchar2(30);
 temp_2 date;
Begin
 -- получим текущий id транзакции
 temp_local_tran_id:= dbms_transaction.local_transaction_id;
 
 -- цикл по курсору
 FOR my_tran_rec IN my_tran(temp_local_tran_id) LOOP
 -- выберем из основной таблицы те свойства, 
 -- которые хотим поменять у дочерних записей
 select prop1, prop2
 into temp_1, temp_2
 from obj
 where obj_id = my_tran_rec.obj_id;
 -- ВАЖНО!!!!!!
 -- так как подразумевается рекурсия, 
 -- то операцию удаления необходимо делать 
 -- здесь, до UPDATE, который опять вызовет 
 -- срабатывание триггера for each rows
 -- если не удалим - окажемся в бесконечю цикле, 
 -- но ORACLE нас спасет и выкинет при достижении
 -- количества открытых курсоров, 
 --определенных параметров OPEN_CURSOR
 -- удалим из пром. таблицы строку с текущим primary
  delete from temp_ehcap_obj 
 where local_tran_id = temp_local_tran_id 
 and obj_id = my_tran_rec.obj_id ;
 -- меняем основную таблицу, 
 -- изменяем общие свойства для дочек.
 -- в моем случае obj_whole_id - ссылка на родителя
 update obj
 set prop1 = temp_1,
 prop2 = temp_2
 where obj_whole_id = my_tran_rec.obj_id;
 -- конец. ВСЕМ СПАСИБО.
 END LOOP;
End;
/

Я надеюсь, что мое сбивчивое повествование поможет Вам в борьбе с мутантами. Вопросы, предложения и пожелания буду рад услышать по адресу mailto:gsa@baltros.ru

С уважением,
Сергей Герасимов.