Here bellow is a draft list of resources and thoughts about how Oracle implements multi versioning.
But I had to add this to
to Jonatahn Lewis comments at:
Jonathan is probably the first in the above mentioned links to talk about COMMIT scn not just CHANGE scn – who cares about change SCN anyway ?
But he makes one interesting statement:
“When all the ITL entries show SCNs no later than your query SCN the clone is read-consistent and the data can be used by the query.”
I’d like to know what was ment by that. My interpretation is:
one can reconstruct a consistent read of a particular row at query start SCN. Or one can reconstruct a consistent read of the whole block at query start SCN.
As oracle operates “in blocks” then I assume this is how oracle reconstructs the consistent read of a block: ALL ITL’s have to be delt with to have every row in a block rollbacked to commit SCN just before query start SCN.
Mark J Bobak tells what is really needed to complete the picture:
“Then, at the row level, in the row directory, there’s a lock byte. The value of the lock byte will be either 0, if there is no lock on the row, or it will be the number of the slot in the ITL that corresponds to the transaction that’s making a change, or just holding a lock, on that particular row. So, the locked row in the block points to the ITL slot, and the ITL slot points back at the undo segment/slot/sequence. “
Here, IMHO, the structure is complete:
Row Lock byte points to ITL, ITL points to transaction commit SCN and undo record.
Then everything adds up: row level locking, transaction isolation, all ITL’s traversal.
The block oriented nature of oracle is explained by Tom Kyte:
“o we cache blocks
o we read blocks
o blocks have transaction tables on them, this is what drives the multi-versioning
relational databases tend to do things “to blocks”, it is basically the way they work.
We do not multi-version ROWS.
We multi-version BLOCKS.
Quite a list to have somehow complete picture :) , isn’t it ?
The last but not least:
“If you can wait until November, then Jonathan Lewis’es new book will be the best book ever to explain this :-)”
Let’s wait and see this: http://jonathanlewis.wordpress.com/2011/06/17/author-author/