Re: Asynchronous Commit in Oracle Database 10g R2

Jonathan Lewis wrote:
>
> "Thomas Kyte" <thomas.kyte_at_oracle.com> wrote in message
> news:135742346.00016d49.023.0001_at_drn.newsguy.com...
> > In article <dfbma4$hg1$1_at_nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>, Jonathan
> > Lewis
> >>
> >>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.
> >
>
> True - it is the distributed transaction without
> two-phase commit that is really the issue.
>
> 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.
>
> --
> 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

interestingly, back when JL first showed me this, I managed to concoct a "proof" of the "issue" with utl_file and commit within a loop, followed by a kill -9 part way through. That was on 8.1 on a sun server. Since v9 came along, I've never been able to get the demo to repeat (ie the utl-file writes also marry with the committed records) ...

I wonder if some additional smarts have been added, or whether I've just been (un)lucky...

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------