Transactions - Oracle Database Transactions and Locking Revealed - page 84

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.

WRITE Extensions to COMMIT
Starting with Oracle 10 g Release 2 and above, you may add a WRITE clause to your COMMIT statements. The WRITE
clause allows the commit to either WAIT for the redo you generated to be written to disk (the default) or NOWAIT —to
not wait—for the redo to be written. The NOWAIT option is the capability—a capability that must be used carefully, with
forethought, and with understanding of exactly what it means.
Normally, a COMMIT is a synchronous process. Your application invokes COMMIT and then your application waits
for the entire COMMIT processing to be complete. This is the behavior of COMMIT in all the database releases before
Oracle 10 g Release 2 and is the default behavior in Oracle 10 g Release 2 and above.
In current releases of the database, instead of waiting for the commit to complete, which may take measurable
time since a commit involves a physical write—a physical I/O—to the redo log files stored on disk, you may have
the commit performed in the background, without waiting for it. That comes with the side-effect that your commit