Re: commit or not commit inside a cursor loop

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:1j3cdvkad8aqg2rruaj45ejelpk64mm4dr_at_4ax.com...
> On Thu, 29 May 2003 09:04:39 +0100, "Andy" <andy.spaven_at_eps-hq.co.uk>
> wrote:
>
> >
> >It's not all hogwash. A rollback segment created by a transaction (A)
that
> >has !!committed!! is marked as available to be reused. If this rollback
> >segment is being used by another transaction (B) in order to create a
read
> >consistent view of some data there is a very real possibility of
transaction
> >B suffering a snapshot too old error. To get into this situation,
> >transaction B cannot have locked the rows before transaction A started
and
> >must have therefore started after A and in order to need the read
consistent
> >view using A's rollback segment(s) must have started before A has
finished.
> >Therefore the poster was correct re ora-1555 errors. What you might be
> >thinking of is the read consistent view that relies on rollback segments
> >belonging to the transaction requiring the read consistency. Until this
> >transaction commits it's rollback segments (that it and other
transactions
> >may be using for read consistency) cannot be overwritten. Only on commit
is
> >the segment(s) marked as available for reuse.
>
>
> You obviously didn't read the hogwash. He *RECOMMENDED* committing *IN
> THE LOOP* to *AVOID ORA-1555*.
> now that is ABSOLUTELY PLAIN CRAP.
> You, not reading the hogwash, describe why his hogwasg CAN"T WORK
>

Sybrand

I was not commenting on the commit inside a loop but responding to your incorrect comments about how rollbacks work in your response to Carsten. You claimed Carstens mail was hogwash and I was disagreeing. Looks like we both agree that commiting inside a loop won't avoid ora-1555 but I'm not sure anyone was recommending that anyhow. The most important point I was trying to get across was is that the comment "Data maintained for read-consistency purposes in a transaction will NEVER been overwritten" was actually incorrect because a rollback segment being so used could be from another transaction (and if that transaction has committed then the risk of ora-1555 raises it's ugly head).

Andy

>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Fri May 30 2003 - 03:21:41 CDT