Re: Asynchronous Commit in Oracle Database 10g R2






"Thomas Kyte" <thomas.kyte@xxxxxxxxxx> wrote in message
news:135742346.00016d49.023.0001@xxxxxxxxxxxxxxxxxx
> In article <dfbma4$hg1$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>, 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



.

Relevant Pages

  • Re: BULK COLLECT - Can it be implemented for a few rows .
    ... So we designed a NEW PROC using BULK COLLECT. ... CURSOR CUR2IS SELECT DISTINCT ... COMMIT; ... FOR I IN CUR1LOOP ...
    (comp.databases.oracle.tools)
  • Re: Straight SQL always put perform PL/SQL?
    ... end loop; ... CURSOR test_cur IS ... PL/SQL procedure successfully completed. ...
    (comp.databases.oracle.server)
  • Re: [PATCH] x86: Reduce the default HZ value
    ... A simple tight loop experiment showed that with HZ=1000 we took about ... I can see at least one immediate downside: ... also talks about where these HZ based timeouts be used and ... Also the default HZ value was 250 before this commit ...
    (Linux-Kernel)
  • Re: 01555 and select statement
    ... end loop ... more than 1 million record and I think "rec" need a lot space to ... Oracle puts the before image in the undo segments or rollback ... However, as soon as you commit, the before image is a candidate to be ...
    (comp.databases.oracle.server)
  • Re: couldnt log on to my -CURRENT machine after upgrade to latest PAM
    ... Don Lewis writes: ... PAM_SUCCESS whether or not if found anything, but we want the loop to ... terminate when either an error is detected or if openpam_parse_chain ... Dag-Erling Smørgrav - des@xxxxxx ...
    (freebsd-current)