Transaction management is a key functionality of database management systems (DBMS). Its main properties include Atomicity, Consistency, Isolation and Durability (ACID properties).
Due to the ACID properties, the DBMS persists the results of all the transaction’s commands or of none of them. But, what if you include some commands inside transactions (e.g. for logging, auditing or just to help debugging) that should persist even if the the transaction is rolledback? You can achieve that using Autonomous Transactions!
Rollback without autonomous transactions
Consider the following script, which does several inserts into two tables (TableA and TableB). After inserting some data into the first table (TableA), there is an insert command to store the current timestamp at a logging table (LoggingTable).
The last command in the script is a rollback. Therefore, all the inserts are rolledback, including the one on the logging table. Then, TableA, TableB and LoggingTable are empty.
Suppose you want to persist the logging table data even with the transaction rollback. In order to achieve that, you may use an antonomous transaction.
Using an autonomous transaction
When executing an autonomous transactions, your session leaves the context of the current transaction and executes a new independent one. Then, you may committ or rollback this new transaction, no matter the result of the original one. After executing the autonomous transaction, the system continues to execute the original one.
PRAGMA AUTONOMOUS_TRANSACTION
Autonomous transactions are identified by PRAGMA AUTONOMOUS_TRANSACTION and can be defined in top-level anonymous blocks, stored procedures and functions and in type methods.
Now, take a look at the following procedures, that executes an autonomous transaction to insert a line into the logging table.
Now, we will use the loggingProcedure in the data load script.
This time, all the inserts in tables TableA and TableB are rollback, but the logging line remains in the logging table.
Now, you can use the logging procedure to log/audit important blocks in your script/procedure, just like in the following.
And you will get a full registry of the script execution.
