Re: Asynchronous Commit in Oracle Database 10g R2


"Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> wrote in message
news:dfc0qd$ap2$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
>
>
>
>
> 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.


.

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: Asynchronous Commit in Oracle Database 10g R2
    ... >> end loop; ... > This is soley due to the lack of a two phase commit between these data ... Jonathan Lewis ...
    (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)