Базы данных Oracle - статьи
Некоторые методы разрешения проблем мутации
Полтавский П. В., разработчик ПО, компания
В процессе эксплуатации автоматизированной системы учета операций с ценными бумагами возникла необходимость создания таблицы для хранения детальной информации (истории сделок). Для её заполнения был разработан механизм, реализуемый набором триггеров БД и серверных пакетных процедур, вызываемых из триггеров. Но при тестировании данного механизма возникла следующая ситуация: при срабатывании триггера вызываемая им серверная процедура запрашивает данные из таблицы, на которой создан триггер. В результате возникает ошибка мутации:
ORA-04091: table <имя_таблицы> is mutating, trigger/function may not see it
(описание этого явления выходит за рамки данной статьи). Для решения проблемы были использованы следующие методы:
При вставке или изменении записи из таблицы документов выполняется процедура инициализации глобальной переменной пакета значением столбца внутреннего идентификатора записи сделки. Это происходит при срабатывании следующего триггера (FOR EACH ROW trigger):
CREATE OR REPLACE TRIGGER DB_DOC_T1 after insert or update on DB_DOC FOR EACH ROW
begin if (inserting or updating) then DP_STNDS.Set_Id_Deal(:new.id_deal); else DP_STNDS.Set_Id_Deal(:old.id_deal); end if; end;
Пакетная процедура DP_STNDS.Set_Id_Deal и функция DP_STNDS.Get_Id_Deal чтения инициализированного значения пакетной переменной определяются следующим образом: … PROCEDURE Set_Id_deal(p_id_deal in db_doc.id_deal%type) is begin dp_id_deal:= p_id_deal; end;
FUNCTION Get_Id_Deal_No RETURN dp_id_deal db_doc.id_deal%type is begin return dp_id_deal ; end; …
Переменная dp_id_deal является глобальной переменной пакета DP_STNDS и объявляется в теле пакета:
CREATE OR REPLACE PACKAGE BODY DP_STNDS AS -- Переменные пакета dp_id_deal db_doc.id_deal%type; … END PACKAGE BODY DP_STNDS;
После инициализации этой переменной происходит выполнение процедуры генерации истории сделки. Это обеспечивается срабатыванием следующего триггера (STATEMENT trigger):
CREATE OR REPLACE TRIGGER DB_DOC_T2 after insert or update on DB_DOC declare v_id_deal DB_DOC.id_deal%type; begin v_id_deal:=DP_STNDS.Get_Id_Deal; -- Генерация истории сделки DC_DEAL_HIST.Gen_Deal_Hist(v_id_deal); end;
Процедура DC_DEAL_HIST.Gen_Deal_Hist обращается к таблице документов DB_DOC, но эффекта мутации не возникает, т. к. триггер DB_DOC_T2 является триггером типа STATEMENT (операторный).
ОГРАНИЧЕНИЯ.
Операторы DML, изменяющие записи таблицы (UPDATE), должны применяться к одной, а не к нескольким записям. Например, вместо оператора
UPDATE DB_DOC SET amount = 1000 WHERE id_doc in (1,2);
необходимо использовать операторы
UPDATE DB_DOC SET amount = 1000 WHERE id_doc = 1; UPDATE DB_DOC SET amount = 1000 WHERE id_doc = 2;
В первом случае триггер DB_DOC_T2 срабатывает только один раз, во втором – для каждой обновляемой записи (соответственно, для второго случая расчет истории сделки происходит также для каждой обновляемой записи, в отличие от первого случая).
Рассмотрим альтернативный вариант механизма для генерации истории сделок. Триггер DB_DOC_T1 будет выглядеть следующим образом:
CREATE OR REPLACE TRIGGER DB_DOC_T1 after insert or update on DB_DOC FOR EACH ROW begin DC_DEAL_HIST_SUPP.Create_DP_Job_Deals_Rec(:new.id_deal); end;
Процедура DC_DEAL_HIST_SUPP.Create_DP_Job_Deals_Rec формирует в регистрационной таблице DP_JOB_DEALS запись, содержащую значение внутреннего идентификатора записи сделки - :new.id_deal. Таким образом, данная таблице постоянно пополняется актуальной информацией, необходимой для генерации истории сделок. Для выполнения процедуры генерации истории сделки DC_DEAL_HIST.Gen_Deal используется задание серверного процесса – job. Ниже приводится PL/SQL блок определения задания:
VARIABLE jobno number; begin DBMS_JOB.SUBMIT(:jobno, ‘dc_deal_hist_supp.gen_deals_by_job;', SYSDATE,'SYSDATE + 1/86400'); commit; end; /
Задание, выполняясь периодически с интервалом в 1 сек, запускает пакетную процедуру DC_DEAL_HIST_SUPP.GEN_DEALS_BY_JOB, которая последовательно обрабатывает все записи регистрационной таблицы DP_JOB_DEALS и выполняет генерацию истории для каждой сделки (процедура DC_DEAL_HIST.Gen_Deal). По завершении работы процедура DC_DEAL_HIST_SUPP.GEN_DEALS_BY_JOB очищает регистрационную таблицу.
Данная технология была неоднократно реализована в нескольких промышленных системах. Использование приемов, описанных в данной статъе, позволяет:
