Re: Asynchronous Commit in Oracle Database 10g R2

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 3 Sep 2005 08:19:16 +0000 (UTC)
Message-ID: <dfbma4$hg1$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"VC" <boston103_at_hotmail.com> wrote in message news:JoudnUcHkP8gbIXeRVn-hQ_at_comcast.com...
> Hi,
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:df9tol$oli$1_at_nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>>
>>
>> This is an undocumented optimization.
>
> OK, I believe that the idea is that one does not care about potential data
> loss since the application has no control over commits anyway. All the
> application sees is whether the PL/SQL proc. committing in a loop
> succeeded or failed. If the PL/SQL proc. failed, the number of committed
> rows is unpredictable whether or not the commits are optimized.
>

VC -

It looks like you've answered the question you asked in the previous post. Yes, there is a risk of data loss, but in general the front-end doesn't know how much data has been lost if the call fails because the front-end doesn't know from moment to moment how many rows have been processed until the call ends and they've all been processed.

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.

As soon as you are able to get a message out of the black box that is the database (in this case pl/sql) call, you have the ability to detect a commit that is not recoverable.

-- 
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




 

Received on Sat Sep 03 2005 - 03:19:16 CDT