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

Что такое операторы TCL в PL/SQL?

TCL расшифровывается как Transaction Control Statements (операторы управления транзакциями). Эти операторы либо сохраняют незавершенные транзакции, либо откатывают их. Они играют жизненно важную роль, поскольку, если транзакция не сохранена, изменения, внесенные через нее, не будут отменены. Операторы DML Эти данные не будут храниться в базе данных постоянно. Ниже приведены различные операторы TCL. PL/SQL.

Сделка будет завершена при следующих сценариях:

• При выдаче любого из вышеуказанных сообщений (кроме SAVEPOINT).
• Когда выполняются операторы DDL (DDL — это операторы автокоммита).
• Когда выполняются операторы DCL (DCL — это операторы автокоммита).

Использование точки сохранения и отката к

В таблице выше представлены термины SAVEPOINT и ROLLBACK TO, которые вместе дают частичный контроль над транзакцией. SAVEPOINT отмечает именованную точку внутри текущей транзакции. Последующий откат к этой точке сохранения отменяет все изменения, внесенные после нее, в то время как keeping проделанная ранее работа осталась неизменной.

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

Синтаксис:

SAVEPOINT <savepoint_name>;
   -- one or more DML statements
ROLLBACK TO <savepoint_name>;

Основные моменты, которые следует помнить о точках сохранения:

• Точка сохранения существует только внутри текущей транзакции; операция COMMIT или полный откат удаляют все точки сохранения.
• При откате к точке сохранения все точки сохранения, созданные после неё, удаляются, но точка сохранения, к которой вы вернулись, сохраняется.
• Команда ROLLBACK TO не завершает транзакцию; изменения, внесенные до точки сохранения, остаются в состоянии ожидания до тех пор, пока вы не выполните команду COMMIT или ROLLBACK.
• Если вы повторно используете имя точки сохранения, новая версия SAVEPOINT переместит маркер в более позднюю позицию.

Поскольку команда ROLLBACK TO оставляет транзакцию открытой, вы все равно в конце решаете, следует ли зафиксировать оставшиеся изменения или отменить их с помощью полного отката (ROLLBACK).

Что такое автономная транзакция

В PL/SQL все изменения данных называются транзакцией. Транзакция считается завершенной, когда к ней применяется команда сохранения или отмены. Если команда сохранения или отмены не указана, транзакция не считается завершенной, и изменения данных не будут зафиксированы на сервере.

По умолчанию PL/SQL рассматривает все изменения в течение сессии как единую транзакцию, и сохранение или отмена этой транзакции влияет на все ожидающие изменения в сессии. Автономная транзакция предоставляет разработчику возможность вносить изменения в отдельной транзакции и сохранять или отменять эту конкретную транзакцию, не затрагивая основную транзакцию сессии.

• Автономная транзакция может быть определена на уровне подпрограммы.
• Чтобы сделать любой подпрограмма Для работы в другой транзакции в декларативной секции соответствующего блока следует указать ключевое слово PRAGMA AUTONOMOUS_TRANSACTION.
• Это указывает компилятору рассматривать это как отдельную транзакцию, и сохранение или удаление данных внутри этого блока не будет отражаться в основной транзакции.
• Перед выходом из этой автономной транзакции и возвращением к основной транзакции обязательно необходимо выполнить команды COMMIT или ROLLBACK, поскольку в любой момент времени может быть активна только одна транзакция.
• Таким образом, после начала автономной транзакции ее необходимо сохранить и завершить, прежде чем управление сможет вернуться к основной транзакции.

Синтаксис:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
.
BEGIN
<execution_part>
[COMMIT|ROLLBACK]
END;
/

В приведенном выше синтаксисе блок преобразован в автономную транзакцию.

Пример 1: В этом примере мы разберемся, как работает автономная транзакция.

На скриншоте ниже показан пример автономной транзакции и её результат. Oracle.

DECLARE
   l_salary   NUMBER;
   PROCEDURE nested_block IS
   PRAGMA autonomous_transaction;
    BEGIN
     UPDATE emp
       SET salary = salary + 15000
       WHERE emp_no = 1002;
   COMMIT;
   END;
BEGIN
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;
   dbms_output.put_line('Before Salary of 1001 is'|| l_salary);
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
   dbms_output.put_line('Before Salary of 1002 is'|| l_salary);    
   UPDATE emp 
   SET salary = salary + 5000 
   WHERE emp_no = 1001;

nested_block;
ROLLBACK;

 SELECT salary INTO  l_salary FROM emp WHERE emp_no = 1001;
 dbms_output.put_line('After Salary of 1001 is'|| l_salary);
 SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
 dbms_output.put_line('After Salary of 1002 is '|| l_salary);
end;

Результат

Before:Salary of 1001 is 15000 
Before:Salary of 1002 is 10000 
After:Salary of 1001 is 15000 
After:Salary of 1002 is 25000

Code Объяснение:

• Code строка 2: Объявляем l_salary как NUMBER.
• Code строка 3: Объявление процедуры nested_block.
• Code строка 4: Преобразование процедуры nested_block в AUTONOMOUS_TRANSACTION.
• Code строки 7-9: Увеличение зарплаты сотруднику номер 1002 на 15000.
• Code строка 10: Осуществление автономной транзакции.
• Code строки 13-16: Вывод данных о заработной плате сотрудников 1001 и 1002 до внесения изменений.
• Code строки 17-19: Увеличение зарплаты сотруднику номер 1001 на 5000.
• Code строка 20: Вызов процедуры nested_block.
• Code строка 21: Отмена основной транзакции.
• Code строки 22-25: Вывод данных о заработной плате сотрудников 1001 и 1002 после внесения изменений.

Повышение заработной платы сотрудника № 1001 не отражено, поскольку основная транзакция была отменена. Повышение заработной платы сотрудника № 1002 отражено, поскольку эта операция была оформлена как отдельная транзакция и сохранена в конце.

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

Когда следует использовать автономные транзакции?

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

• Журнал аудита: Записывайте, кто изменил конфиденциальные данные, когда, а также старые и новые значения, чтобы журнал сохранился даже в случае отката основной транзакции.
• Регистрация ошибок: Записать запись об ошибке внутри исключение Обработчик и COMMIT сохранят диагностические данные, а неудачная транзакция будет отброшена.
• Счетчики и статистика: Необходимо установить счетчик использования или количество обращений, который должен сохраняться независимо от результата вызова.
• COMMIT внутри триггера: Триггер не может напрямую выполнить команду COMMIT; это единственный поддерживаемый способ сделать это с помощью автономной транзакции.

Избегайте автономных транзакций для обычных обновлений, судьба которых должна совпадать с судьбой основной транзакции. Чрезмерное их использование может скрывать данные за независимыми подтверждениями и затруднять отладку. Как правило, каждый автономный блок должен заканчиваться явным COMMIT или ROLLBACK.

Автономные и обычные транзакции

Разница между обычной (основной) транзакцией и автономной транзакцией сводится к объему и независимости. В таблице ниже приведено их сравнение.

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

Часто задаваемые вопросы (FAQ)