13.4 Autonomous Transactions

Before the release of PL/SQL 8.1, each Oracle session could have at most one active transaction at a given time. In other words, any and all changes made in your session had to be either saved or erased in their entirety. This restriction has long been considered a drawback in the PL/SQL world. Developers have requested the ability to execute and save or cancel certain DML statements (INSERT, UPDATE, DELETE) without affecting the overall session's transaction.

You can now accomplish this goal with the autonomous transaction feature of PL/SQL 8.1 and above. When you define a PL/SQL block (anonymous block, procedure, function,packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. That block becomes an independent transaction that is started by another transaction, referred to as the main transaction.

Within the autonomous transaction block, the main transaction is suspended. You perform your SQL operations, commit or roll back those operations, and resume the main transaction. This flow of transaction control is illustrated in Figure 13-4.

Figure 13-4. Flow of transaction control between main, nested, and autonomous transactions

13.4.1 Defining Autonomous Transactions

There isn't much involved in defining a PL/SQLblock as an autonomous transaction. You simply include the following statement in your declaration section:

PRAGMA AUTONOMOUS_TRANSACTION;

The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. For the purposes of the autonomous transaction, a PL/SQL block can be any of the following:

• Top-level (but not nested) anonymous PL/SQL blocks

• Functions and procedures, defined either in a package or as standalone programs

• Methods (functions and procedures) of an object type

• Database triggers

You can put the autonomous transaction pragma anywhere in the declaration section of your PL/SQL block. You would probably be best off, however, placing it before any data structure declarations. That way, anyone reading your code will immediately identify the program as an autonomous transaction.

This pragma is the only syntax change made to PL/SQL to support autonomous transactions. COMMIT, ROLLBACK, the DML statements—all the rest is as it was before. However, these statements have a different scope of impact and visibility when executed within an autonomous transaction, and you will need to include a COMMIT or ROLLBACK in your program.

13.4.2 When to Use Autonomous Transactions

Where would you find autonomous transactions useful in your applications? First, let's reinforce the general principle: you will want to define your program module as an autonomous transaction whenever you want to isolate the changes made in that module from the caller's transaction context.

Here are some specific ideas:

Logging mechanism

On the one hand, you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries. What's a person to do? Go autonomous!

Commits and rollbacks in your database triggers

If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code. Developers have been asking for this capability for a long time.

Retry counter

Suppose that you want to let a user try to get access to a resource N times before an outright rejection; you also want to keep track of attempts between connections to the database. This persistence requires a COMMIT, but one that should remain independent of the transaction. For an example of such a utility, see retry.pkg and retry.tst on the O'Reilly site.

Software usage meter

You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application.

Reusable application components

This usage goes to the heart of the value of autonomous transactions. As we move more and more into the dispersed, multi-layered world of the Internet, it becomes ever more important to be able to offer standalone units of work (also known as cartridges) that get their job done without any side effects on the calling environment. Autonomous transactions will play a crucial role in this area.

Before we look at how you might use autonomous transactions for these scenarios, let's get a clearer picture about what you can and cannot do with autonomous transactions.