2.1 Transaction Management in PL/SQL

PL/SQL is tightly integrated with the Oracle RDBMS; that is, after all, why it is called "PL/SQL" -- procedural language extensions to SQL. When you perform operations in the database, you do so within the context of a transaction , a series of one or more SQL statements that perform a logical unit of work. A transaction can have associated with it a variety of locks on resources (rows of data, program units, etc.). These locks define the context of the transaction; the context also contains the actual data.

To appreciate transactions in Oracle, consider the "ACID" principle: a transaction has atomicity, consistency, isolation, and durability, which are defined as follows:

Atomic

A transaction's changes to a state are atomic: either they all happen or none happens.

Consistent

A transaction is a correct transformation of state. The actions taken as a group do not violate any integrity constraints associated with that state.

Isolated

Even though many transactions may be executing concurrently, from any given transaction's point of view, other transactions appear to have executed before or after its execution.

Durable

Once a transaction completes successfully, the changes to the state are made permanent, and they survive any subsequent failures.

A transaction can either be saved by performing a COMMIT or erased by requesting a ROLLBACK. In either case, the affected locks on resources are released (a ROLLBACK TO might only release some of the locks). The session can then start a new transaction.

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. 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 then resume the main transaction. This flow of transaction control is illustrated in Figure 2.1 .

Figure 2.1: Flow of transaction control between main, nested, and autonomous transactions