Базы данных Oracle - статьи

         

Некоторые методы разрешения проблем мутации


Полтавский П. В., разработчик ПО, компания

  • Постановка задачи
  • В процессе эксплуатации автоматизированной системы учета операций с ценными бумагами возникла необходимость создания таблицы для хранения детальной информации (истории сделок). Для её заполнения был разработан механизм, реализуемый набором триггеров БД и серверных пакетных процедур, вызываемых из триггеров. Но при тестировании данного механизма возникла следующая ситуация: при срабатывании триггера вызываемая им серверная процедура запрашивает данные из таблицы, на которой создан триггер. В результате возникает ошибка мутации:

    ORA-04091: table <имя_таблицы> is mutating, trigger/function may not see it

    (описание этого явления выходит за рамки данной статьи). Для решения проблемы были использованы следующие методы:

  • использование триггеров различных типов (FOR EACH ROW и STATEMENT) и глобальных переменных пакета;
  • применение заданий серверных процессов (JOBs).
  • Использование триггеров различных типов и глобальных переменных пакета
  • При вставке или изменении записи из таблицы документов выполняется процедура инициализации глобальной переменной пакета значением столбца внутреннего идентификатора записи сделки. Это происходит при срабатывании следующего триггера (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 срабатывает только один раз, во втором – для каждой обновляемой записи (соответственно, для второго случая расчет истории сделки происходит также для каждой обновляемой записи, в отличие от первого случая).



  • Применение заданий серверных процессов (JOBs)


  • Рассмотрим альтернативный вариант механизма для генерации истории сделок. Триггер 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 очищает регистрационную таблицу.

  • Результаты


  • Данная технология была неоднократно реализована в нескольких промышленных системах. Использование приемов, описанных в данной статъе, позволяет:

  • активно использовать базовые триггера при проектировании серверной части приложений;
  • упростить программный код на уровне базовых триггеров;
  • централизовать логику приложения на уровне серверной части.



  • Содержание раздела