Re: Important note about asynchronous commit

  • From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
  • To: juancarlosreyesp@xxxxxxxxx
  • Date: Tue, 26 Sep 2006 16:50:00 +0200

When you issue a *synchronous* commit, Oracle will wait until all
the redo records produced by your transaction are physically
written to the redo logs files, and then returns control to you.
So, even if the instance crashes immediately after you commit,
upon restart you are 100%  guaranteed that you will see the
modifications made by your transaction,
since they're safely on disk, not in memory only.

When you issue an *asynchronous* commit, Oracle won't wait for
the redo records to be physically written to the redo logs files
before returning control to you, and only writes
the redo records "as soon as possible"; if in the meanwhile
the instance crashes, you'll lose the modifications made by your transaction.

That certainly makes your applications harder to write, since
upon restart, you'll have to check which transaction was
"committed to disk" and which not, and replay the latter;
which is not possible if eg the transaction was made by
an operator or a web customer (the classic example
of an OLTP system).

On 9/26/06, Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx> 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,
basically mean "when you commit and you return from commit, the commit may
may NOT have happened yet, if the system fails after you return from commit

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
so in a transactional system"
Alberto Dell'Era
"Per aspera ad astra"

Other related posts: