Oracle allows you to 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 10g Release 2 and is the default behavior in Oracle 10g 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 shut down 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.
