Database Reference
In-Depth Information
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.”
If you look at these application categories, you'll notice that all three of them are background, noninteractive
applications. They do not interact with a human being directly. Any application that does interact with a person—
that reports to the person “Commit complete”—should use the synchronous commit. Asynchronous commits are
not a tuning device for your online customer-facing applications. Asynchronous commits are applicable only to
batch-oriented applications, those that are automatically restartable upon failure. Interactive applications are not
restartable automatically upon failure—a human being must redo the transaction. Therefore, you have another flag
that tells you whether this capability can be considered—do you have a batch application or an interactive one?
Unless it is batch-oriented, synchronous commit is the way to go.
So, outside of those three categories of batch applications, this capability— COMMIT WRITE NOWAIT —should
probably not be used. If you do use it, you need to ask yourself what would happen if your application is told commit
processed , but later, the commit is undone. You need to be able to answer that question and come to the conclusion
that it will be OK if that happens. If you can't answer that question, or if a committed change being lost would have
serious repercussions, you should not use the asynchronous commit capability.
 
Search WWH ::




Custom Search