Re: Asynchronous Commit in Oracle Database 10g R2

In article <dfbma4$hg1$1_at_nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>, Jonathan Lewis says...
>
>
>VC -
>
>It looks like you've answered the question you asked
>in the previous post. Yes, there is a risk of data loss,
>but in general the front-end doesn't know how much
>data has been lost if the call fails because the front-end
>doesn't know from moment to moment how many rows
>have been processed until the call ends and they've all
>been processed.
>
>An example where this is very bad -
> loop
> read value from row in database
> mark row as processed
> use extproc to transfer value to Swiss bank account
> commit;
> end loop;
>
>If the database crashes, you could be in a position where
>the extproc has sent the funds, but the committed change
>was not written into the redo log FILE. When the database
>restarts, you have repeat a couple of transfers.

Yes, but even with or without this feature, that code above is subject to what you just described.

if after extproc finished
but before commit happened
the crash occurs, you are in a heap of trouble -- REGARDLESS.

This is soley due to the lack of a two phase commit between these data sources - and not due to async commit.

If that was pro*c code or java code, it would still be extremely buggy code (in my opinion).

Anything you do more than one transaction - but have an implicit dependency between them, you run this risk.

Async or not.

>
>As soon as you are able to get a message out of the black
>box that is the database (in this case pl/sql) call, you have
>the ability to detect a commit that is not recoverable.
>
>--
>Regards
>
>Jonathan Lewis
>
>Now waiting on the publishers: Cost Based Oracle - Volume 1
>
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>The Co-operative Oracle Users' FAQ
>
>http://www.jlcomp.demon.co.uk/appearances.html
>Public Appearances - schedule updated 8th July 2005

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation