Re: Oracle read consistency *inside*a transaction

VC wrote:
> Hi,
>
> I posted this elsewhere but did not get a satisfactory answer:
>

You probably need to read Oracle Concepts Manual, esp. chapter about transaction handling.

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

Doesn't matter if block is written to a disk or not. It was updated in cache in the first place.

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

Wrong. Transaction, if not rolled back, doesn't roll back anything. SCN is not for committed transactions only. And all rollback information
is stored, well, in rollback segments (or undo tablespace, which has internal rollback segments anyway).

> My assumption is that the undo reference *value* (probably the undo
block
> address) is used as a marker telling Oracle where in the undo chain
it has
> to stop.
>

That's interesting, Oracle doesn't publish details about rollback segment structure. It's probably some kind of know-how it doesn't want to publish. So your guess here is as good as anybody's.

> The same mecahnism must be at work when using 'savepoint a......
rolback to
> a'.
>
> Does anybody know what kind marker is used during such
intra-transactional
> mini-rollbacks ?
>
> Thanks.
>
> VC
Received on Mon Dec 06 2004 - 11:14:17 CST