Автономные транзакции на языке PL/SQL

Автономные транзакции языка PL/SQLОпределяя блок PL/SQL как автономную транзакцию, вы тем самым изолируете вы­полняемые в нем команды DML от контекста транзакции вызывающего кода. Этот блок определяется как независимая транзакция, начатая другой транзакцией, которая является главной.

В блоке автономной транзакции главная транзакция приостанавливается. Вы выполня­ете SQL-операции, затем производите их фиксацию или откат и возобновляете главную транзакцию (рис. 1).



 

Последовательность выполнения главной и вложенной (автономной) транзакций 

Рис. 1. Последовательность выполнения главной и вложенной (автономной) транзакций

 

Определение автономной транзакции

Определить блок PL/SQL как автономную транзакцию очень просто. Для этого до­статочно включить в раздел объявлений следующую директиву:

 

PRAGMA AUTONOMOUS_TRANSACTION;

Директива приказывает компилятору PL/SQL назначить блок PL/SQL как автономный (независимый). В контексте автономных транзакций автономным может быть объявлен любой из следующих блоков:

  •  Анонимный блок PL/SQL верхнего уровня (не вложенный!).
  •  Функция или процедура, определенная в пакете или как отдельная программа.
  •  Метод (функция или процедура) объектного типа.
  •  Триггер базы данных.

Директива AUTONOMOUS_TRANSACTlON может располагаться в любом места раздела объяв­лений блока PL/SQL. Однако лучше всего разместить ее перед определениями струк­тур данных, чтобы любому программисту сразу было ясно, что программа является анонимной транзакцией. Введение этой директивы — единственное изменение в коде PL/SQL для поддержки анонимных транзакций. Все остальное — команды COMMIT и ROLLBACK, а также команды DML — остается прежним. Автономная транзакция просто изменяет их область действия и видимость при выполнении в контексте автономной транзакции, и вы должны включить команду COMMIT или ROLLBACK в каждую программу автономной транзакции.

 

Правила и ограничения на использование автономных транзакций

Включить в программный код директиву AUTONOMOUS_TRANSACTION несложно, однако при ее использовании действует целый ряд правил и ограничений.

  •  Если автономная транзакция пытается обратиться к ресурсу, заблокированному главной транзакцией (приостановленной до завершения автономной программы), происходит взаимная блокировка (deadlock). Простой пример: программа обновляет таблицу, а затем, не зафиксировав изменения, вызывает автономную процедуру, которая должна внести изменения в ту же таблицу:

 

/* Файл в Сети: autondlock.sql */
PROCEDURE update_salary (dept_in IN NUMBER)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   CURSOR myemps IS
      SELECT empno FROM emp WHERE deptno = dept_in FOR UPDATE NOWAIT;
   BEGIN
      FOR rec IN myemps LOOP
         UPDATE emp SET sal = sal * 2 WHERE empno = rec.empno;
      END LOOP;
      COMMIT;
   END;
   BEGIN
      UPDATE emp SET sal = sal * 2; update_salary (10);
   END;

 

Результат не радует:

 

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

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

  •  Чтобы выход из программы с автономной транзакцией, выполнившей как минимум одну команду INSERT, UPDATE, MERGE или DELETE, произошел без ошибок, необходимо выполнить явную фиксацию или откат транзакции. Если в программе содержится незавершенная транзакция, то исполняющее ядро инициирует исключение и про­изводит откат незафиксированной транзакции.
  •  Команды COMMIT и ROLLBACK завершают активную автономную транзакцию, но не про­грамму, в которой она содержится. Поэтому блок с автономной транзакцией может содержать несколько команд commit и rollback.
  •  При выполнении отката до точки сохранения эта точка должна быть определена в те­кущей транзакции. В частности, нельзя выполнить откат из автономной транзакции до точки сохранения, определенной в главной транзакции.
  •  Параметр TRANSACTIONS в инициализационном файле Oracle определяет допустимое количество выполняемых одновременно автономных транзакций для одного сеанса. Если в приложении используется множество программ, выполняемых в режиме автономной транзакции, этот предел может быть превышен, что приведет к выдаче исключения. В этом случае значение параметра TRANSACTIONS следует увеличить. По умолчанию оно равно 75.

 

Область видимости транзакций

После выполнения в автономной транзакции команд COMMIT или ROLLBACK изменения по умолчанию немедленно становятся видимыми в главной транзакции. А если вы предпочитаете скрыть их от последней? Да, изменения должны быть сохранены или отменены, но информация об этом не должна становиться доступной в главной тран­закции. Для этой цели используется следующая модификация команды SET TRANSACTION:

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

По умолчанию для транзакции устанавливается уровень изоляции READ COMMITTED, при котором зафиксированные изменения тут же становятся видимыми главной транзакции. Как обычно, команда SET TRANSACTION должна выполняться до начала транзакции (то есть перед первой командой SQL). Кроме того, данная установка влияет на весь сеанс, а не только на текущую программу. Сценарий autonserial.sql на сайте книги демонстрирует уровень изоляции SERIALIZABLE в действии.

 

 

В каких случаях следует применять автономные транзакции

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

  •  Механизм протоколирования. Требуется записать информацию об ошибке в та­блицу базы данных, а также выполнить откат внутренней транзакции, приведшей к ошибке. При этом вы не хотите, чтобы из таблицы-журнала были удалены и дру­гие записи об ошибках. Сделайте внутреннюю транзакцию автономной! Вероятно, это самая распространенная причина для использования автономных транзакций в коде PL/SQL.
  •  Фиксация и откат в триггерах базы данных. Определив триггер как автономную транзакцию, все выполненные им изменения можно фиксировать или отменять, и это никак не отразится на транзакции, приведшей к срабатыванию триггера. Для чего это может быть нужно? Например, в триггере базы данных можно выполнить действие, которое не зависит от статуса транзакции, вызвавшей срабатывание триг­гера. Предположим, вы хотите отслеживать все действия с таблицей независимо от того, было это действие завершено или нет. А может, вы хотите отслеживать действия, завершившиеся неудачей. Примеры использования этого приема пред­ставлены в сценариях autontrigger*.sql на сайте книги.
  •  Многократно используемые компоненты приложения. Для программ этого типа возможность определять автономные транзакции жизненно необходима. В совре­менных системах, особенно в распределенном и многоуровневом мире Интернета, необходимы независимые программные единицы, выполняемые без каких-либо побочных эффектов для среды вызова. Автономные транзакции играют важную роль в этой области.
  •  Предотвращение каскадных триггерных ошибок. Такие ошибки возникают в ситу­ации, когда триггер уровня строки таблицы пытается читать или записывать данные в таблицу, из которой он сработал. Но если преобразовать триггер в автономную транзакцию, включив директиву PRAGMA AUTONOMOUS_TRANSACTION и закрепив изменения в теле триггера, код последнего сможет запрашивать содержимое таблицы, но при этом будет видеть только уже закрепленные изменения. Иначе говоря, в таблице не будут видны изменения, внесение которых привело к срабатыванию триггера. Кроме того, код триггера не сможет изменять содержимое таблицы.
  •  Вызов пользовательских функций в коде SQL, изменяющем таблицы. Oracle позволяет вызывать в командах SQL пользовательские функции — при условии, что функция не обновляет базу данных (и с некоторыми дополнительными ус­ловиями). Но если функция будет определена как автономная транзакция, вы можете выполнять в ней операции вставки, обновления и удаления в ходе запроса. Данная возможность продемонстрирована в сценарии trcfunc.sql на сайте книги.
  •  Счетчик попыток. Допустим, вы хотите предоставить пользователю N попыток обращения к ресурсу, причем количество попыток должно сохраняться между под­ключениями к базе данных. Для этого необходима команда COMMIT, независимая от транзакции. Примеры представлены в файлах retry.pkg и retry.tst на сайте книги.

 

Создание механизма автономного протоколирования

Во многих приложениях возникает необходимость в ведении журнала ошибок, про­исходящих в ходе выполнения транзакций. Самым удобным местом для хранения журнала является таблица базы данных: она всегда доступна и из нее удобно извлекать информацию для анализа средствами SQL.

Но у такого журнала имеется один недостаток: операции записи в журнал становятся частью транзакции. И если будет выполнена команда ROLLBACK, из журнала может быть удалена часть записей об ошибках. Конечно, это нежелательно. Можно попытаться с помощью точек сохранения выполнить откат таким образом, чтобы записи журнала оставались нетронутыми, но это крайне неудобно. Автономные транзакции суще­ственно упрощают протоколирование ошибок, делают его более удобным и надежным. Допустим, таблица-журнал определена следующим образом:

 

/* Файл в Сети: log.pkg */
CREATE TABLE logtab (
   code INTEGER, text VARCHAR2(4000), created_on DATE, created_by VARCHAR2(100), changed_on DATE, changed_by VARCHAR2(100)
);

Таблица будет использоваться для хранения информации об ошибках (функции SQLCODE и SQLERRM), а также для протоколирования других событий, не связанных с ошибками.

Как пользоваться журналом? Прежде всего рассмотрим неправильное решение:

 

EXCEPTION
WHEN OTHERS THEN
DECLARE
   v_code PLS_INTEGER := SQLCODE;
   v_msg VARCHAR2(1000) := SQLERRM;
   BEGIN
      INSERT INTO logtab VALUES (
         v_code, v_msg, SYSDATE, USER, SYSDATE, USER);
   END;
END;

Другими словами, никогда не реализуйте механизм протоколирования в разделе ис­ключений и других разделах основного кода приложения. Все операции с таблицей- журналом должны выполняться на отдельном программном уровне (это называется инкапсуляцией). Для этого есть несколько важных причин:

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

Наш очень простой пакет протоколирования состоит всего из двух процедур:

 

PACKAGE log IS
PROCEDURE putline (code_in IN INTEGER, text_in IN VARCHAR2);
PROCEDURE saveline (code_in IN INTEGER, text_in IN VARCHAR2);
END;

Чем putline отличается от saveline? Процедура log.saveline является автономной транзакцией, тогда как log. putline просто вставляет строку в таблицу. Вот тело пакета:

 

/* Файл в Сети: log.pkg */
PACKAGE BODY log IS
   PROCEDURE putline (
      code_in IN INTEGER, text_in IN VARCHAR2)
   IS
   BEGIN
      INSERT INTO logtab VALUES ( code_in, text_in,
         SYSDATE,
         USER,
         SYSDATE,
         USER
      );
   END;

   PROCEDURE saveline (
      code_in IN INTEGER, text_in IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      putline (code_in, text_in);
      COMMIT;
      EXCEPTION WHEN OTHERS THEN ROLLBACK;
   END;
END;

 

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

  •  Процедура putline выполняет обыкновенную вставку. Вероятно, вы захотите до­бавить в нее раздел обработки исключений, если пакет log будет взят за основу подсистемы протоколирования ошибок в реальном приложении.
  •  Процедура saveline вызывает процедуру putline, но делает это в контексте авто­номной транзакции.

При наличии описываемого пакета обработка ошибок значительно упрощается:

 

EXCEPTION
   WHEN OTHERS THEN
   log.saveline (SQLCODE, SQLERRM);
END;

Только и всего. Разработчикам не нужно задумываться о структуре таблицы журнала; более того, они могут вообще не знать, что запись производится в таблицу базы данных. А поскольку транзакция автономна, то что бы ни происходило в приложении — журнал останется в целости и сохранности.

 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3065 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6915 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Символьные функции и аргументы...
Символьные функции и аргументы... 10753 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Основы языка PL/SQL: использов...
Основы языка PL/SQL: использов... 2997 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03
Войдите чтобы комментировать

Myk аватар
Myk ответил в теме #9252 14 окт 2018 07:08
Очень хорошие примеры. Некоторые примеры работы с транзакциями были мне не известны! Спасибо!!
Borberd аватар
Borberd ответил в теме #9232 25 сен 2018 03:37
Автономные транзакции дают большие возможности программисту. Изучить PRAGMA нужно как следует для качественного кодинга!