Transactions - Oracle Database Transactions and Locking Revealed - page 100

There are some, but not many, limitations to what you can do in a distributed transaction, and they are
reasonable (to me, anyway, they seem reasonable). The big ones are as follows:

• You can't issue a

COMMIT over a database link. That is, you can't issue a COMMIT@remote_site .
You may commit only from the site that initiated the transaction.

• You can't do DDL over a database link. This is a direct result of the preceding issue. DDL

commits. You can't commit from any site other than the initiating site, hence you can't do
DDL over a database link.

• You can't issue a

SAVEPOINT over a database link. In short, you can't issue any transaction
control statements over a database link. All transaction control is inherited from the session
that opened the database link in the first place; you can't have different transaction controls in
place in the distributed instances in your transaction.

The lack of transaction control over a database link is reasonable, since the initiating site is the only one that has
a list of everyone involved in the transaction. If in our three-site configuration, Site 2 attempted to commit, it would
have no way of knowing that Site 3 was involved. In Oracle, only Site 1 can issue the commit command. At that point,
it is permissible for Site 1 to delegate responsibility for distributed transaction control to another site.
We can influence which site will be the actual commit site by setting the COMMIT_POINT_STRENGTH (a parameter)
of the site. A COMMIT_POINT_STRENGTH associates a relative level of importance to a server in a distributed transaction.
The more important the server (the more available the data needs to be), the more probable that it will coordinate
the distributed transaction. You might want to do this if you need to perform a distributed transaction between your
production machine and a test machine. Since the transaction coordinator is never in doubt as to the outcome of a
transaction, it's best if the production machine coordinated the distributed transaction. You don't care so much if your
test machine has some open transactions and locked resources. You certainly do care if your production machine does.
The inability to do DDL over a database link is actually not so bad at all. First, DDL is rare. You do it once at
installation or during an upgrade. Production systems don't do DDL (well, they shouldn't do DDL). Second, there is
a method to do DDL over a database link, in a fashion, using the job queue facility, DBMS_JOB or, in Oracle 10 g and
higher, the scheduler package, DBMS_SCHEDULER . Instead of trying to do DDL over the link, you use the link to schedule
a remote job to be executed as soon as you commit. In that fashion, the job runs on the remote machine, is not a
distributed transaction, and can do the DDL. In fact, this is the method by which the Oracle Replication Services
perform distributed DDL to do schema replication.

Autonomous Transactions
Autonomous transactions allow you to create a “transaction within a transaction” that will commit or roll back
changes independently of its parent transaction. They allow you to suspend the currently executing transaction, start
a new one, do some work, and commit or roll back—all without affecting the currently executing transaction state.
Autonomous transactions provide a new method of controlling transactions in PL/SQL and may be used in

• Top-level anonymous blocks

• Local (a procedure in a procedure), stand-alone, or packaged functions and procedures

• Methods of object types

• Database triggers

Before we take a look at how autonomous transactions work, I'd like to emphasize that this type of transaction is
a powerful and therefore dangerous tool when used improperly. The true need for an autonomous transaction is very
rare indeed. I would be very suspicious of any code that makes use of them—that code would get extra examination.
It is far too easy to accidentally introduce logical data integrity issues into a system using them. In the sections that
follow, we'll discuss when they may safely be used after seeing how they work.