Autonomous Transaction in Oracle PL/SQL

What are TCL Statements in PL/SQL?

TCL stands for Transaction Control Statements. These statements either save the pending transactions or roll back the pending transactions. They play a vital role, because unless a transaction is saved, the changes made through DML statements will not be stored permanently in the database. Below are the different TCL statements in PL/SQL.

The transaction will be complete under the following scenarios:

• When any of the above statements is issued (except SAVEPOINT).
• When DDL statements are issued (DDL are auto-commit statements).
• When DCL statements are issued (DCL are auto-commit statements).

Using SAVEPOINT and ROLLBACK TO

The table above introduces SAVEPOINT and ROLLBACK TO, and together they give you partial control over a transaction. A SAVEPOINT marks a named point inside the current transaction. A later ROLLBACK TO that savepoint undoes every change made after it, while keeping the work done before it intact.

This is useful when a long transaction performs several SQL steps and only the last step fails. Instead of discarding the entire transaction, you can roll back to the last good savepoint and continue.

Syntax:

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

Key points to remember about savepoints:

• A SAVEPOINT exists only inside the current transaction; a COMMIT or a full ROLLBACK erases every savepoint.
• When you roll back to a savepoint, any savepoints created after it are erased, but the savepoint you roll back to is kept.
• ROLLBACK TO does not end the transaction; the changes made before the savepoint stay pending until you COMMIT or ROLLBACK.
• If you reuse a savepoint name, the newer SAVEPOINT moves the marker to the later position.

Because ROLLBACK TO leaves the transaction open, you still decide at the end whether to COMMIT the remaining changes or discard them with a full ROLLBACK.

What is Autonomous Transaction

In PL/SQL, all the modifications done on data are termed a transaction. A transaction is considered complete when a save or discard is applied to it. If no save or discard is given, then the transaction is not considered complete, and the modifications done on the data will not be made permanent on the server.

By default, PL/SQL treats all the modifications during a session as a single transaction, and saving or discarding that transaction affects every pending change in the session. An autonomous transaction provides the developer with the ability to make changes in a separate transaction and to save or discard that particular transaction without affecting the main session transaction.

• An autonomous transaction can be specified at the subprogram level.
• To make any subprogram work in a different transaction, the keyword PRAGMA AUTONOMOUS_TRANSACTION should be given in the declarative section of that block.
• It instructs the compiler to treat this as a separate transaction, and saving or discarding inside this block will not reflect in the main transaction.
• Issuing COMMIT or ROLLBACK is mandatory before leaving this autonomous transaction and returning to the main transaction, because at any time only one transaction can be active.
• So once an autonomous transaction is started, it must be saved and completed before control can move back to the main transaction.

Syntax:

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

In the above syntax, the block has been made an autonomous transaction.

Example 1: In this example, we are going to understand how an autonomous transaction works.

The screenshot below shows this autonomous transaction example and its output in 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;

Output

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 Explanation:

• Code line 2: Declaring l_salary as NUMBER.
• Code line 3: Declaring the nested_block procedure.
• Code line 4: Making the nested_block procedure an AUTONOMOUS_TRANSACTION.
• Code line 7-9: Increasing the salary for employee number 1002 by 15000.
• Code line 10: Committing the autonomous transaction.
• Code line 13-16: Printing the salary details of employees 1001 and 1002 before the changes.
• Code line 17-19: Increasing the salary for employee number 1001 by 5000.
• Code line 20: Calling the nested_block procedure.
• Code line 21: Discarding the main transaction.
• Code line 22-25: Printing the salary details of employees 1001 and 1002 after the changes.

The salary increase for employee number 1001 is not reflected because the main transaction has been discarded. The salary increase for employee number 1002 is reflected because that block has been made a separate transaction and saved at the end.

So irrespective of the save or discard at the main transaction, the changes in the autonomous transaction are saved without affecting the main transaction.

When to Use Autonomous Transactions

Autonomous transactions are powerful, so it helps to know when they fit. Reserve them for work that must succeed or fail independently of the main transaction, not for core business logic. Common use cases include:

• Audit logging: Record who changed sensitive data, when, and the old and new values, so the log survives even if the main transaction rolls back.
• Error logging: Write an error record inside an exception handler and COMMIT it, so the diagnostic detail is kept while the failed transaction is discarded.
• Counters and statistics: Advance a usage counter or hit count that must persist regardless of the caller’s outcome.
• COMMIT inside a trigger: A trigger cannot issue COMMIT directly; an autonomous transaction is the only supported way to do so.

Avoid autonomous transactions for ordinary updates that should share the fate of the main transaction. Overusing them can hide data behind independent commits and make debugging harder. As a rule, every autonomous block must end with an explicit COMMIT or ROLLBACK.

Autonomous vs Regular Transactions

The difference between a regular (main) transaction and an autonomous transaction comes down to scope and independence. The table below compares them.

Unlike a regular nested block, whose changes always share the outcome of the enclosing transaction, an autonomous block stands on its own. Understanding this difference helps you decide when a block should be independent and when it should share the result of the main transaction.

FAQs