Oracle read consistency *inside*a transaction

Hi,

I posted this elsewhere but did not get a satisfactory answer:

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

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.

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 Fri Dec 03 2004 - 17:12:50 CST