Transactions - Oracle Database Transactions and Locking Revealed - page 105

But we can also verify that our error log information has persisted, and in fact is committed:
EODA@ORA12CR1> rollback;
Rollback complete.
EODA@ORA12CR1> select * from error_log;
TS
---------------------------------------------------------------------------
ERR1
---------------------------------------------------------------------------
ERR2
---------------------------------------------------------------------------
09-MAY-14 05.11.03.818918 PM
ORA-02290: check constraint (EODA.SYS_C00204351) violated
ORA-06512: at "EODA.P1", line 5
ORA-06512: at "EODA.P2", line 6
ORA-06512: at line 4
In my experience, that is the only truly valid use of an autonomous transaction—to log errors or informational
messages in a manner that can be committed independently of the parent transaction.
Summary
In this chapter, we looked at many aspects of transaction management in Oracle. Transactions are among the major
features that set a database apart from a file system. Understanding how they work and how to use them is necessary
to implement applications correctly in any database. Understanding that in Oracle all statements are atomic
(including their side effects) and that this atomicity is extended to stored procedures is crucial. We saw how the
placement of a WHEN OTHERS exception handler in a PL/SQL block could radically affect what changes took place in
the database. As database developers, having a good understanding of how transactions work is crucial.
We took a look at the somewhat complex interaction between integrity constraints (unique keys, check
constraints, and the like) and transactions in Oracle. We discussed how Oracle typically processes integrity constraints
immediately after a statement executes, but that we can defer this constraint validation until the end of the transaction
if we wish. This feature is key in implementing complex multitable updates when the tables being modified are all
dependent on each other—the cascading update is an example of that.
We moved on to consider some of the bad transaction habits that people tend to pick up from working with
databases that “support” rather than “promote” the use of transactions. We looked at the cardinal rule of transactions:
they should be as short as they can be but as long as they need to be. Data integrity drives the transaction size —that
is a key concept to take away from this chapter. The only things that should drive the size of your transactions are the
business rules that govern your system. Not undo space, not locks—business rules.
We covered distributed transactions and how they differ from single database transactions. We explored the limitations
imposed upon us in a distributed transaction and discussed why they exist. Before you build a distributed system, you need
to understand these limitations. What works in a single instance might not work in a distributed database.
The chapter closed with a look at autonomous transactions and covered what they are and, more important,
when they should and should not be used. I would like to emphasize once again that the legitimate real-world use of
autonomous transactions is exceedingly rare. If you find them to be a feature you are using constantly, you'll want to
take a long, hard look at why.