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 (9.2.0.1.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:
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);
COMMIT;
/* 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:
PRAGMA AUTONOMOUS_TRANSACTION
Example 2
CREATE OR REPLACE
PROCEDURE log_acct_txn ( p_acct_id IN NUMBER,
p_txn_type
IN VARCHAR2,
p_txn_amount
IN NUMBER)
IS
PRAGMA
AUTONOMOUS_TRANSACTION;
acct_id account_header.account_id%type;
txn_type VARCHAR2(4);
txn_amount NUMBER;
BEGIN
INSERT INTO txn_history@us.remotedb.com
( account_id, txn_type,amount, txn_date)
VALUES (acct_id,cr_type,txn_amount,SYSDATE);
COMMIT;
END;
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
DECLARE
acct_id NUMBER;
BEGIN
declare
pragma autonomous_transaction;
begin
Insert account_history@us.remotedb.com
(account_id, customer_id, account_type, acct_start_date)
values (1010,’C123′,’SB’, SYSDATE);
commit;
end;
END;
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 9.2.0.1.0) */
/*
Debit the customer’s account only if the minimum balance limit is met else rollback.
However, record the activity. */
DECLARE
var1 NUMBER;
var2 NUMBER;
BEGIN
—
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
COMMIT;
ELSE
ROLLBACK;
END IF;
—
more statements
—
END;
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.
