Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 3, 2002

Autonomous and Distributed Transactions in Oracle 8i/9i

By DatabaseJournal.com Staff

1.     Introduction

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 (

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:

1)       Non-Distributed transactions, which manipulate or query only a single database (the local database where the user is logged in).
2)       Distributed transactions, which manipulate or query more than one node in a distributed database.
3)       Remote transactions, which manipulate or query only a remotely located database.

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.

Example 1

/* The distributed transaction starts from here */
INSERT INTO local. account_header (account_id, customer_id, account_type, acct_start_date)
VALUES (1010,'C123','SB', SYSDATE);
INSERT account_history@us.remotedb.com
(account_id, customer_id, account_type, acct_start_date)
VALUES (1010,'C123','SB', SYSDATE);

/* The distributed transaction ends after the COMMIT that follows the INSERT statement */

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:



Example 2



p_txn_type IN VARCHAR2,

p_txn_amount IN NUMBER)



acct_id account_header.account_id%type;

txn_type VARCHAR2(4);

txn_amount NUMBER;


INSERT INTO txn_history@us.remotedb.com ( account_id, txn_type,amount, txn_date)

VALUES (acct_id,cr_type,txn_amount,SYSDATE);




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.

Example 3

    acct_id NUMBER;
        pragma autonomous_transaction;
        Insert account_history@us.remotedb.com
        (account_id, customer_id, account_type, acct_start_date)
        values (1010,'C123','SB', SYSDATE);

results in the following error:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here.

2.       An autonomous transaction must have a COMMIT or a ROLLBACK statement before it completes execution. Otherwise the following error is generated:
ORA-06519: active autonomous transaction detected and rolled back.

3.       As 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:
ORA-01086: savepoint ' specified savepoint' never established.

4.       Again, 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:
ORA-01574: maximum number of concurrent transactions exceeded.

5.       You 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:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here.

6.       Finally, 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 the error:
ORA-00054: resource busy and acquire with NOWAIT specified.

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.

Example 4


/* A simple example that I have tested on Oracle8.1.7 and Oracle */

/* Debit the customer's account only if the minimum balance limit is met else rollback. However, record the activity. */

    var1 NUMBER;
    var2 NUMBER;
SQL statements....

/* The procedure debits the amount from the account but does not commit/rollback any changes */

debit_acct (acct_id, txn_amount) ;

/* Calling the autonomous transaction that is shown in the preceding example to log the activity in a remote table txn_history */

log_acct_txn ( acct_id , txn_type , txn_amount) ;

/* Once again query a local table */

IF min_bal_maintained(acct_id) THEN
more statements

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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM