10gR2 New Feature: Asynchronous Commit

One of the primary tests for DBMS reliability is what's known as the ACID test. ACID-compliant systems are as close as you can get to guaranteed not to lose your data. Essentially, as long as your database files are intact, you are guaranteed that your data is consistent. This is not true for non-ACID compliant systems. Non-ACID-compliant systems are vulnerable to data inconsistency, and generally aren't taken seriously for any application where data integrity is important. Now, in 10gR2, Oracle offers us the option to break its ACID compliance.

ACID stands for Atomicity, Consistency, Isolation and Durability.
Atomicity, the first property, doesn't refer to the tiny particles that make up matter, but to the other meaning of the word "atomic": indivisible. Atomicity is a short-hand way of saying that when a transaction that changes the data occurs, either the whole transaction occurs, or none of the transaction occurs. In other words, each transaction is "atomic".

Atomicity is implemented in Oracle (and most other DBMSs) via commit-rollback. If I issue several update statements as part of a transaction, then commit that transaction, I am guaranteed that all of that transaction has gone through. Oracle does not return from a COMMIT statement until the redo log changes corresponding to that commit have been physically written to disk, not just to buffer. Similarly, if I issue a ROLLBACK, then none of the transaction goes through.

Durability, the final property in the ACID test, means that any changes made by committed transactions are persistent: they are permanent, and if a failure occurs, the information in the database will reflect all transactions committed before the failure. A durable DBMS must be atomic, but not all atomic DBMSs are durable.

In Oracle 10gR2, you can now choose to break this fourth property. Oracle's new asynchronous commit feature essentially allows you to trade durability for speed on a transaction-by-transaction basis. You can choose to break ACID compliance in this way by default, at the instance level; or you can choose to break it at the individual transaction level.

Asynchronous Commit

By default, Oracle's commits are durable. Oracle writes your changes to disk and doesn't return control of the session to you until it's done. The normal commit process is as follows:

1. User begins transaction.
2. While user is issuing DML, Oracle generates redo entries corresponding to the data changes. These redo entries are buffered in memory while the transaction is occurring.
3. When the user issues a COMMIT, Oracle immediately writes this buffered redo to disk, along with redo for the commit. Ie, a disk I/O is forced.
4. Oracle does not return from the commit until the redo has been completely written to disk (to the online redo log).

There are two important aspects to note here: the redo information is written to disk immediately, and the session waits for the process to complete before returning.

Oracle now lets you change both of these aspects. You can let the log writer write the redo information to disk in its own time, instead of immediately; and you can have the commit return to you before it's completed, instead of waiting.

Syntax

You can change Oracle's commit behavior at the statement level by simply specifying one or both of the new options above with your commit. Use the new WRITE clause of the commit statement for this purpose. For example:

COMMIT WRITE BATCH NOWAIT ;

will commit your transaction without forcing a disk I/O, and will return control to you without waiting for the commit operation to complete.

The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

By default, if no WRITE clause is specified, a naked COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

COMMIT_WRITE initialization parameter

You can even change the default behaviour of COMMIT at the instance level, so that the default behaviour of a naked COMMIT statement is not COMMIT WRITE IMMEDIATE WAIT. A new initialization parameter, COMMIT_WRITE, lets you set default commit immediacy to IMMEDIATE or BATCH, and default commit wait to WAIT or NOWAIT:

COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'

Benefits

Compromising the "D" in your database's ACID compliance gets you one thing: speed. A normal commit forces a disk I/O and does not return until the commit is complete. You'll save some time if you batch your commits (not forcing the disk I/O) and don't wait until the commits actually complete. If you don't actually care if the data you're writing is permanent, but you do care that your writes be rapid, then you might find this feature useful.

It's worth noting that this new feature is not the only way to reduce the amount of time your application spends committing data. A properly designed application should normally batch its commits by design, ie. your application should not be continually committing small amounts of data. If your application is continually issuing small commits, you should look at your code to see if this design is really necessary, or if you might be able to batch your commits in the code. For example, you might elect to save web-entered data to the database at the end of several screens instead of at each screen. Or, if your commits are taking a long time because of complex indexes and triggers, you might consider saving entered data to an intermediate, raw-data table and batching writes from that table to the schema tables, rather than writing directly to the schema tables while the user waits on the commit.

Dangers

The obvious danger of asynchronous commits is that your DBMS is no longer ACID complient. Specifically, in using an asynchronous commit, you have traded durability for speed. An asynchronous commit returns before the data has actually been written to disk, so if the database crashes before some buffered redo is written to the logs, or a file I/O problem prevents the log writer from writing buffered redo to a non-multiplexed log, then the commit is lost.

There is also a danger which applies only to Real Applications Clusters. The COMMIT_WRITE initialization parameter is documented as being able to hold different values for different instances in a RAC. This could be useful if you have services defined on the cluster and have one service for which durability is not important. However, I would advise extreme caution, because if COMMIT_WRITE has different values for different instances in your cluster, load balancing or failover of your services could have unexpected and disastrous results.

Potential drawbacks

Besides the above dangers, there's one potentially serious drawback to this new feature. As far as I can determine, there is no initialization parameter to let the DBA disable asynchronous commit. Nor does there seem to a way to keep any given database user from executing an asynchronous commit.

This could be a serious data reliability issue for systems that have knowledgeable ad-hoc users modifying data directly. Any user can issue an asynchronous commit for their work, which means that work could potentially disappear in case of system failure -- a real problem if outside processes assume the work has gone through. Fortunately, this kind of setup, with ad-hoc users directly issuing SQL against the database as part of the business workflow, is vanishingly rare today.

New feature -- or not?

There's some evidence that PL/SQL has been using asynchronous commits in a limited way before 10gR2. href="http://www.oracledba.co.uk/tips/lgwr_dilemma.htm">This article by Connor McDonald, at oracledba.co.uk, provides a case study against a pre-10gR2 database in which a series of successive commits within a loop appear to be batched. What prevents this from
breaking durability, suggests McDonald, seems to be that only the PL/SQL code block knows how many commits are supposed to have been executed at any given point -- as long as PL/SQL waits for the last commit to fully complete before the PL/SQL block returns, durability is, for all intents and purposes, preserved.

Further reading

• More on ACID compliance
• COMMIT_WAIT initialization parameter
• New COMMIT syntax