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

Що таке оператори TCL у PL/SQL?

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

Транзакція буде завершена за таких сценаріїв:

• Коли видається будь-яка з вищезазначених заяв (окрім SAVEPOINT).
• Коли видаються оператори DDL (DDL – це оператори автоматичного фіксування).
• Коли видаються оператори DCL (DCL – це оператори автоматичного фіксування).

Використання SAVEPOINT та ROLLBACK TO

У таблиці вище представлено пункти SAVEPOINT та ROLLBACK TO, і разом вони надають вам частковий контроль над транзакцією. SAVEPOINT позначає іменовану точку всередині поточної транзакції. Пізніший ROLLBACK TO цієї точки збереження скасовує всі зміни, внесені після неї, зберігаючи при цьомуping робота, виконана до того, як вона залишилася недоторканою.

Це корисно, коли довга транзакція виконує кілька SQL кроки, і лише останній крок завершується невдачею. Замість того, щоб відкидати всю транзакцію, ви можете повернутися до останньої успішної точки збереження та продовжити.

Синтаксис:

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

Ключові моменти, які слід пам'ятати про точки збереження:

• Точка збереження існує лише всередині поточної транзакції; COMMIT або повний ROLLBACK видаляє всі точки збереження.
• Під час відкату до точки збереження всі точки збереження, створені після неї, видаляються, але точка збереження, до якої ви відкатуєтеся, зберігається.
• 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 або ROLLBACK.

Автономні проти звичайних транзакцій

Різниця між звичайною (основною) транзакцією та автономною транзакцією зводиться до обсягу та незалежності. У таблиці нижче їх порівнюють.

На відміну від звичайного вкладений блок, зміни якого завжди мають спільний результат транзакції, що його оточує, автономний блок існує сам по собі. Розуміння цієї різниці допомагає вам вирішити, коли блок має бути незалежним, а коли він має мати спільний результат основної транзакції.

Поширені запитання