Autonomous and Distributed Transactions in Oracle 8i/9i
December 3, 2002
This article presents a basic introduction to transaction processing in PL/SQL and primarily focuses on the Autonomous transaction processing feature of PL/SQL and the changes that have occurred from Oracle 8i (8.1.7) to 9i (22.214.171.124.0).
In the following sections, I will define transaction and describe the use of distributed transactions and autonomous transactions, followed by a special case, the combined use of distributed and autonomous transactions, and the changes introduced in Oracle9i.
2. Transaction Defined
The simple definition of a transaction in a DBMS is to ADD, DELETE or UPDATE rows in the database.
The ANSI/ISO SQL standard definition of a transaction is a logical unit of work that comprises all the executable SQL statements executed by a single user that ends when an explicit COMMIT or ROLLBACK statement is issued by the user.
Transactions can be of 3 types:
transactions, which manipulate or query only a single database (the local database
where the user is logged in).
3. Distributed Transaction Defined
Unlike the non-distributed transactions that involve SQL statements against a single node of a database (the local node where the user is logged in), the Distributed transaction involves all DML (Data Manipulation Language) operations on two or more nodes of a distributed database.
The transaction in this case consists of two DML statements -- one DML in the local database and the next in the remote database identified by us.remotedb.com
4. Autonomous Transaction Defined
In PL/SQL, transaction processing takes place serially. That is, a new transaction can begin only when the earlier transaction ends, in which case the user issues an explicit Commit or Rollback statement.
Quite often, however, your application may require you to commit or rollback changes without affecting the main transaction in the session.
For example, consider a simple banking transaction in which a customer transfers funds from one account to another account. A single commit or rollback should end this transaction. (Obviously! The customer may not find it pleasant to see one of his accounts debited but the other show no credits in the case of some unexpected failure.) However, your application requires recording each activity (on each of the accounts) in a transaction history table located in a remote database.
Thus, your main transaction would debit the customer's account (A) with the specified transaction amount, record the transaction activity (an autonomous transaction whose commit should not affect the main transaction), and then credit account (B) with the new amount, record the update and then commit the main transaction.
From PL/SQL 8.1 onwards it is possible to achieve the goal of having to rollback some change in the main transaction while committing the log entry (the transaction history) by the issuing the PL/SQL compiler directive AUTONOMOUS_TRANSACTION.
In order to use autonomous transactions, you simply have to include the following statement in your declaration section:
CREATE OR REPLACE PROCEDURE log_acct_txn ( p_acct_id IN NUMBER,
p_txn_type IN VARCHAR2,
p_txn_amount IN NUMBER)
INSERT INTO firstname.lastname@example.org ( account_id, txn_type,amount, txn_date)
The block in this example now becomes an independent transaction, and the COMMIT issued in this block will not affect the SQL statements in the main transaction.
When you call this procedure log_acct_txn in a block, it acts as an independent block of code and suspends the Main transaction. The main transaction will resume once the procedure completes execution.
5. Rules of Autonomous Transaction
Typically, a program block can be made an autonomous transaction as shown in the preceding examples. However, some rules to follow in using autonomous transactions are:
1. Nested blocks cannot be declared as autonomous transactions.
results in the following error:
autonomous transaction must have a COMMIT or a ROLLBACK statement before it
completes execution. Otherwise the following error is generated:
the autonomous transaction is an independent transaction, you cannot rollback
to a savepoint defined in the main transaction. If you attempt to do so, you
will receive the error:
calling one autonomous transaction in the main transaction in your session
implies the session is concurrently running two sessions and so on. The maximum
number of such concurrent transactions in your session is determined by the
Oracle initialization parameter file (init.ora) parameter TRANSACTIONS. If you
use a large number of autonomous transaction programs in your application that exceed the
value set for the parameter, you will run into the following error:
cannot declare a package as an autonomous transaction to make all procedures/functions as autonomous transactions. If you do so, you will get this error:
if your autonomous transaction tries to access a resource held by the main
transaction that is suspended, it will result in a deadlock situation giving
6. Using Autonomous Transactions within a Distributed Transaction
Consider a scenario where you have a distributed transaction in which you require to commit or rollback a change in a remote table followed by an insert/update/query on your local database, or vice versa. In such a case, you would most likely take advantage of the autonomous feature described in the preceding section.
/* A simple example that I have tested on Oracle8.1.7 and Oracle 126.96.36.199.0) */
/* Debit the customer's account only if the minimum balance limit is met else rollback. However, record the activity. */
When this example is executed in Oracle8.1.6/8.1.7, it will result in the error:
ORA-00164: autonomous transaction disallowed within distributed transaction
This is because Oracle8i does not support autonomous transactions within a distributed query.
In Oracle 8.1.7, one of the ways to work around the limitation is to use the control statement SET TRANSACTION READY ONLY in your programs. However, the use of the SET TRANSACTION READ ONLY control statement means only a SELECT statement can follow the autonomous transaction and no other DML is permitted. Using SET TRANSACTION READ ONLY just after the BEGIN statement will not give any errors as the transaction is made READ ONLY and, as a result, no changes are made to the remote database.
7. New in Oracle9i (9.2.0)
As of version 9.2.0 of Oracle9i, this limitation has been removed to a limited extent. Example 4 in the preceding section, when run in Oracle 9i (9.2.0), will not give you any errors.
ORA-00164 is now redefined to:
ORA-00164 distributed autonomous transaction disallowed within migratable distributed transaction
In oracle 9i (9.2.0), this error is now defined in context to the use of Oracle XA library with Transaction Monitors (TM). Normally, Oracle acts as its own TM and manages its own commit and recovery. However, it also provides for using a standards-based TM.
Essentially, Oracle 9.2.0 has changed the way it treats the boundaries of a transaction between its own role as a TM and that of an external TM.
The concepts of migratable transactions and transaction monitors will be discussed in detail in future articles.