Re: Asynchronous Commit in Oracle Database 10g R2

"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