Transactions - Expert Oracle Database Architecture - page 280

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 (what that entails exactly will be covered in detail in Chapter 9). 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
is no longer assured to be durable. That is, your application may get a response back from the database that the
asynchronous commit you submitted was received, other sessions may be able to see your changes, but later find that
the transaction you thought was committed was not. This situation will occur only in very rare cases and will always
involve a serious failure of the hardware or software. It requires the database to be shutdown abnormally in order for
an asynchronous commit to not be durable, meaning the database instance or computer the database instance is
running on would have to suffer a complete failure.
So, if transactions are meant to be durable, what is the potential use of a feature that might make them possibly
not durable? Raw performance. When you issue a COMMIT in your application, you are asking the LGWR process to take
the redo you've generated and ensure that it is written to the online redo log files. Performing physical I/O, which this
process involves, is measurably slow; it takes a long time, relatively speaking, to write data to disk. So, a COMMIT may
well take longer than the DML statements in the transaction itself! If you make the COMMIT asynchronous, you remove
the need to wait for that physical I/O in the client application, perhaps making the client application appear
faster—especially if it does lots of COMMITs .
This might suggest that you'd want to use this COMMIT WRITE NOWAIT all of the time—after all isn't performance
the most important thing in the world? No, it is not. Most of the time, you need the durability achieved by default with
COMMIT . When you COMMIT and report back to an end user “we have committed,” you need to be sure that the change
is permanent. It will be recorded in the database even if the database/hardware failed right after the COMMIT . If you
report to an end user that “Order 12352 has been placed,” you need to make sure that Order 12352 was truly placed
and persistent. So, for most every application, the default COMMIT WRITE WAIT is the only correct option (note that you
only need say COMMIT —the default setting is WRITE WAIT ).
When would you want to use this capability to commit without waiting then? Three scenarios come to mind:

• A custom data load program. It must be custom, since it will have additional logic to deal with

the fact that a commit might not persist a system failure.

• An application that processes a live data feed of some sort, say a stock quote feed from the

stock markets that inserts massive amounts of time-sensitive information into the database.
If the database goes offline, the data stream keeps on going and the data generated during
the system failure will never be processed (Nasdaq does not shut down because your database
crashed, after all!). That this data is not processed is OK, because the stock data is so
time-sensitive, after a few seconds it would be overwritten by new data anyway.

• An application that implements its own “queuing” mechanism, for example one that has

data in a table with a PROCESSED_FLAG column. As new data arrives, it is inserted with a
value of PROCESSED_FLAG='N' (unprocessed). Another routine is tasked with reading the
PROCESSED_FLAG='N' records, performing some small, fast transaction and updating
the PROCESSED_FLAG='N' to 'Y' . If it commits but that commit is later undone (by a system
failure), it is OK because the application that processes these records will just process the
record again—it is “restartable.”