Re: Important note about asynchronous commit

  • From: "Juan Carlos Reyes Pacheco" <juancarlosreyesp@xxxxxxxxx>
  • To: "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx>
  • Date: Tue, 26 Sep 2006 17:26:25 -0400

Important quote
Reviewer:  Jonathan Lewis  from UK
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
<end quote>

I would phrase that somewhat differently - the commit has definitely happened,
in that your changes are published and visible to all other sessions as from
that SCN. However, as you say, the changes are not protected - if the system
crashes, then your transaction may not be recoverable because it has not got
into the log file. The D of ACID is no longer enforced.

Admittedly, when I talk about nologging operations I tend to make the rather
melodramatic statement "if it's not in the log file it never happened" but then
explain that as it's as far as the standby or recovery are concerned it never
happened.  For async commits, it happened, but there's a window (of up to about
3 seconds) where it won't guaranteeably recover or get to the standby.

We should also bear in mind that this makes official and choosable a phenomenon
that has been happening inside pl/sql calls for years - commits publish data,
but the transactions are not guaranteed to be synched to the log file until the
pl/sql call ends and returns control to the called

Other related posts: