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.
• 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 thoroughly into the dispersed, multilayered 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 take a 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.

Another Tool in the Toolbox

As Oracle database technology matures and broadens, we can sometimes (often?) feel overwhelmed by all of its features, its opportunities, and its increasing sophistication and complexity. How can we keep up with it all? Well, perhaps we don't have to keep up with it all. Perhaps we can take a different approach: develop a basic familiarity with the latest and greatest stuff.

Recognize this feature or that as just another potential tool you can use to implement your applications. Learn to distill out from the technobabble of daily life the core advantages of a feature. Then, when you encounter a particular requirement, you can evaluate which of all these tools can best be applied to come up with an optimal solution.

Autonomous transactions are not going to be useful or applicable in every one (or even very many) of your programs. The autonomous transaction feature is, however, a very handy new tool in the PL/SQL developer's toolbox. Keep it in mind, and pull it out as needed.