Re: Asynchronous Commit in Oracle Database 10g R2

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:dfc0qd$ap2$1_at_nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>
>
>
>
>
> However, if I change the position of the commit
> in the loop:
>
>>> loop
>>> read value from row in database
>>> mark row as processed
>>> commit; -- moved up
>>> use extproc to transfer value to Swiss bank account
>>> end loop;
>
> it gives the impression that the BUSINESS is
> safer - worst case (apparently) is that we crash
> after a commit, and have just ONE record that
> says we have sent money that we have not sent -
> and the business could be happy with one client
> complaining about lack of payment.
>
> With the optimisation in place, we could crash
> after several fund transfers had been made but
> not written to the log file.
>
> The critical error is due to a design that does
> not have 2PC, but we think we have introduced
> an acceptable risk, when the optimisation has
> actually hidden an unacceptable risk.

If one is so smart as to use external procs from a P:/SQL block, one should be clever enough not to use commits in a loop (or even commits in a PL/SQL block altogether). On the other hand one can be too smart for one's own good ;)

Still, taking into account the fact that the effects of the optimisation are not documented, the optimisation is potentially dangerous as your example shows.

Regards. Received on Sat Sep 03 2005 - 08:37:54 CDT