Довольно часто возникает потребность выполнения определенных плановых работ в БД (сбор статистики, очистка логов). У пользователей БД тоже могут быть задачи, требующие периодического выполнения, например закрытие периода, подготовка отчетов, передача данных в другие системы и т.д. В СУБД Oracle для этого есть планировщик заданий. Но несмотря на то, что планировщик заданий в Oracle – довольно мощное средство, остается актуальным и использование различных планировщиков задач ОС (cron, at и др.).

Сразу хочу отметить, что вариант перехода от cron к планировщику Oracle здесь не рассматривается, т.к. в моем случае это было невозможно в силу многих причин (и не только технических).

Время работы приложения зависит от многих факторов: количество данных в таблицах, загрузка ресурсов БД и многих других. И может сложиться, и складывается ситуация, когда время выполнения задачи становится больше, чем период ее запуска. И если планировщик Oracle такое развитие событий отслеживает и предотвращает повторный старт до окончания предыдущей сессии, то cron запускает новый процесс вне зависимости от того, завершились предыдущие запуски или нет.

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

Как один из вариантов предотвращения параллельного запуска предлагается блокировка на уровне приложения. Такой способ устраняет и еще одну проблему – когда несколько пользователей пытаются одновременно запустить одно и то же приложение вручную (не через cron).

Для решения поставленной задачи будем использовать представление gv$session, сохраняя дополнительную информацию о выполняющейся в текущий момент сессии приложения в поле module. При старте приложения будем записывать в него имя приложения (для каждого приложения оно уникально), предварительно проверив, нет ли уже выполняющихся сессий с таким же значением поля module.

Ниже приведен пример реализации данного подхода:

Create function set_lock(pi_event_name in varchar2
                        ,po_error_message out varchar2) return boolean is
    l_count number;
    l_session_id varchar2(256);
  begin
    -- get current session id
    l_session_id := sys_context('USERENV', 'SESSIONID');
    select count(*) into l_count
    from gv$session
    where module = pi_event_name
    and audsid != l_session_id;
    if l_count > 0 then
          po_error_message := 'Other session found';
          return FALSE;
    end if;
    DBMS_APPLICATION_INFO.set_module(pi_event_name, NULL);
    po_error_message := NULL;
    return TRUE;
  end set_lock;

В код PL/SQL приложения добавляется вызов этой функции:

if set_lock(l_event_name, l_error_message) = FALSE then
    return;
end if;

Таким образом, используя только представление gv$session, не добавляя новых таблиц и не меняя структуру существующих, мы получили достаточно эффективное средство предотвращения параллельного запуска. Это решение работает не только с планировщиками задач, но и делает невозможным запуск одной и той же задачи разными пользователями одновременно. Предлагаемое решение достаточно простое, скорее всего я не «открыл Америку», но может быть кому-то этот вариант пригодится в работе.

Старт приложений в БД Oracle через CRON: предотвращение параллельного выполнения