Transactions - Expert Oracle Database Architecture - page 279

■ the preceding code represents an exceedingly bad practice. in general, you should neither catch a
WHEN OTHERS nor explicitly code what oracle already provides as far as transaction semantics is concerned.

Caution

Here, by mimicking the work Oracle normally does for us with the SAVEPOINT , we are able to restore the original
behavior while still catching and “ignoring” the error. I provide this example for illustration only; this is an exceedingly
bad coding practice.

Transaction-Level Atomicity
The entire goal of a transaction, a set of SQL statements executed together as a unit of work, is to take the database
from one consistent state to another consistent state. To accomplish this goal, transactions are atomic as well—the
entire set of successful work performed by a transaction is either entirely committed and made permanent or rolled
back and undone. Just like a statement, the transaction is an atomic unit of work. Upon receipt of “success” from
the database after committing a transaction, you know that all of the work performed by the transaction has been
made persistent.

DDL and Atomicity
It is worth noting that there is a certain class of statements in Oracle that are atomic—but only at the statement level.
Data Definition Language (DDL) statements are implemented in a manner such that:

1. They begin by committing any outstanding work, ending any transaction you might

already have in place.

They perform the DDL operation, such as a CREATE TABLE .

2.

3. They commit the DDL operation if it was successful, or roll back the DDL operation
otherwise.
This means that any time you issue a DDL statement such as CREATE , ALTER and so on, you must expect your
existing transaction to be immediately committed and the subsequent DDL command to be performed and either
committed and made durable or rolled back in the event of any error. DDL does not break the ACID concepts in any
way, but the fact that it commits is something you definitely need to be aware of.

Durability
Normally, when a transaction is committed, its changes are permanent; you can rely on those changes being in the
database, even if the database crashed the instant after the commit completed. This is not true, however, in two
specific cases:

WRITE extensions (available in Oracle 10 g Release 2 and above) available in the
COMMIT statement.

• You use the

COMMIT s in a nondistributed (accesses only a single database, no database links)
PL/SQL block of code.

• You issue

We'll look at each in turn.