Re: Oracle read consistency *inside*a transaction

VC wrote:
> Please see below:
>
> <afilonov_at_yahoo.com> wrote in message
> news:1102353257.271395.188330_at_c13g2000cwb.googlegroups.com...
> >
> > VC wrote:
> >> Hi,
> >>
> >> I posted this elsewhere but did not get a satisfactory answer:
> >>
> >
>
> [AF]
> > You probably need to read Oracle Concepts Manual, esp. chapter
about
> > transaction handling.
>
> [VC]
> Everything is in the Book, eh ? I'll ignore your remark as
irrelevant.
> >
> >> "Let's assume for simplicity that there are no other transactions
but
> > ours
> >> modifying a table. We do the following:
> >>
> >> 1. insert a single row -- RowX
> >> 2. insert .. select
> >>
> >> Now, at step 2 our single user transaction sees all the rows
created
> > and
> >> committed by previous transactions *plus* the newly created RowX
in
> > step 1.
> >> Further, let's assume that the block containing RowX is not in
the
> > cache
> >> but has been written by DBWR to a disk, say, to block 100. Our
> > transaction
> >
>
> [AF]
> > Doesn't matter if block is written to a disk or not. It was updated
in
> > cache in the first place.
> >
>
> [VC] Another non-sequitur if I ever saw one.
>
> >> arrives at block 100, reads RowX, writes it (and possibly some
other
> > rows)
> >> to block 101. Then, the transaction arrives at block 101 and
*does
> > not* see
> >> the copy of RowX (and copies of other rows possibly written
alongside
> > with
> >> RowX).
> >>
> >> I imagine that the transaction, when it gets to block 101, rolls
> > back the
> >> changes it made to block 101 when it was scanning block 100. Now
the
> >
> >> question is how does the transaction know where exactly in the
undo
> > chain it
> >> has to stop ? It cannot possibly use SCN as a stop marker for
block
> > 101
> >> because there is no commit scn yet, besides even if there were an
> > SCN it
> >> would have been the same as for the original RowX in block 100. "
> >>
> >
>
> [AF]
> > Wrong. Transaction, if not rolled back, doesn't roll back anything.
>
> It may come to as a surprise but a 'read consistent' is, in fact, a
roll
> back operation.
>

True, but irrelevant for the transaction itself. It doesn't do consistent
read in this case. It would if other transactions modified the block, but
you said it's not the case. For the session in which transaction happened, all changes done by this transaction are visible, i.e. there is no need for rollback.

> The rest of your response is skipped as irrelevant.
>

Well, why are you asking then?

> VC
Received on Mon Dec 06 2004 - 17:56:51 CST