G-3330: Avoid autonomous transactions.

Blocker

Reliability, Testability

Reason

Before we take a look at how autonomous transactions work, I’d like to emphasize that this type of transaction is a powerful and therefore dangerous tool when used improperly. The true need for an autonomous transaction is very rare indeed. I would be very suspicious of any code that makes use of them—that code would get extra examination. It is far too easy to accidentally introduce logical data integrity issues into a system using them. (page 300)

In my experience, that is the only truly valid use of an autonomous transaction—to log errors or informational messages in a manner that can be committed independently of the parent transaction. (page 305)

-- Kyte, Thomas (2013). Expert Oracle Database Architecture. Third Edition. Apress.

It is most likely not possible to distinguish legitimate uses of autonomous transactions from illegitimate ones via static code analysis. However, since we expect exactly one autonomous transaction per application, the number of false positives is manageable.

Example (bad)

create or replace package body dept_api is
   procedure ins_dept(in_dept_row in dept%rowtype) is
      pragma autonomous_transaction;
   begin
      insert into dept
      values in_dept_row;
      commit; -- required by autonomous transaction
   end ins_dept;
end dept_api;
/

Example (good)

create or replace package body dept_api is
   procedure ins_dept(in_dept_row in dept%rowtype) is
   begin
      insert into dept
      values in_dept_row;
      -- transaction is commited in calling module
      -- after the completion of the unit of work
   end ins_dept;
end dept_api;
/