Re: Important note about asynchronous commit

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: juancarlosreyesp@xxxxxxxxx, "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx>
  • Date: Tue, 26 Sep 2006 22:27:36 +0800

I wouldn't know how to write a very reliable application that guarantees that it IS ALWAYS able to replay transactions in case of Instance failure. === oh yes ! There is one such : The Oracle RDBMS Engine itself. To date (except for one particular buggy 6.0.28 (or was it 6.0.29 ?) release), I have seen Oracle Instances CRASH AND RECOVER consistently.

Play around with the guaranteed mechanism and you are playing
dangerous with your data.

and a quote there
Submitted by Zlatko Sirotic (not verified) on Fri, 2005-10-21 08:18.

To quote C.J.Date (Database In Depth, O'Reilly 2005, page 130-131):

"The emphasis in the commercial world always seems to be on performance, performance, performance;
other objectives, such as ease of use, data independence, and in particular integrity, seem so often to be sacrificed to
? or at best to take a back seat to ? that overriding goal.
But what's the point of a system performing well if we can't be sure the information we're getting from it is correct?
Frankly, I don't care how fast a system runs if I don't feel I can trust it to give me the right answers to my questions.".


Zlatko Sirotic

From some recent postings we have seen on this list, some people are really trying (or "playing around with" ?) BATCH,NOWAIT.

I wouldn't risk my database, even if I was running a benchmark (eg when
testing different hardware platforms).

I do know that, *in theory* in a batch environment you __should__ be
able to identify and replay transactions. I haven't seen a "batch only, no other
transactions" environment -- even the administrator just connecting via
sqlplus to update some setup / master table would be running a transaction
that may not be recoverable if the instance were to crash.
Tom Kyte's example at
is about
"What about a system that is processing incoming records as fast as possible, perhaps from a sensor or a network feed? This program's goal in life is to batch up a couple records, INSERT them, COMMIT them (to make them visible), and continue on. It doesn't really care to wait for the COMMIT to complete; in fact, by waiting, this program is not running as fast as it can. "
But there are many classes of applications­including high-speed data ingest programs whose only goal is to get the stream of data into the database­where commit-but-don't-wait is not only acceptable but very desirable performancewise. "

I would emphasis "only goal is to get the stream of data into the database" again.
Contrast that with the quote from C J Date, above.


At 09:12 PM Tuesday, Juan Carlos Reyes Pacheco wrote:
I want to share this because I didn't fully understood it,

Tom Kyte clarifyme the risk of using asynchronous commit

asynchronous commits should never be used in oltp.

async commits are most DEFINITELY not for transaction processing databases, they
basically mean "when you commit and you return from commit, the commit may or
may NOT have happened yet, if the system fails after you return from commit but

before the commit actually happens - you have to be prepared to REPLAY that
transaction if necessary - something you can do in a batch load easily, but not
so in a transactional system"

Hemant K Chitale

-- //

Other related posts: