Disk Blocks/pages, Consistent Reads, undo
markpapadakis
Whenever a transaction reads a block, it compares its SCN with the
transaction's SCN, and based on whether the block's SCN is >
transaction's SCN it will replay undo data until the final data are
consistent with the SCN of the said transaction.
However, if the above is true, and a transaction B wishes to update a
row in a block, and that transaction B was initiated some time ago,
while in the mean time other transactions have updated the block and
committed those changes thus having set the block's SCN to be >
transaction B's SCN, when transaction B reads the block, the data will
be consistent with the time transaction B began.
So transaction B, having retrieved the block data being in the state
they were the moment transaction B was initiated, will perhaps modify
the rows directory, rows data, ITL and other segments/properties of
the table and then commit that changes to the block, effectively
undoing whatever was done by the transactions that began after
transaction B which affected that block themselves ( because the block
data transaction B touched based using consistent reads did not
contain those changes ).
I am sure I am missing something here. Anyone who can help me
understand that is wrong that assumptions?
Thank you,
Mark Papadakis
Jonathan Lewis
"markpapadakis" <markpa...@gmail.com> wrote in message
news:6646154c-6de8-460e...@x69g2000hsx.googlegroups.com...
Mark,
The transaction will only change the current version of the
block. But it generates the read consistent version of the
block to make sure that the data it wants to change in the
current version is identical to the data as it was when the
transaction started.
For example:
I want to change col1 from X to Z in something that is
current row 3 of the block (update where co1 = 'X')
I create the read-consistent version of the block, and
find that the same row was there when my transaction
started - but the value of col1 was then 'Y'.
So I can't update it because when my transaction started
it wasn't a row that I was supposed to update.
For more details on how messy and expensive this can get
do a search on AskTom for "write consistency"
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
markpapadakis
wrote:
> "markpapadakis" <markpapada...@gmail.com> wrote in message
>
> news:6646154c-6de8-460e...@x69g2000hsx.googlegroups.com...
>
>
>
> Mark,
>
> The transaction will only change the current version of the
> block. But it generates the read consistent version of the
> block to make sure that the data it wants to change in the
> current version is identical to the data as it was when the
> transaction started.
>
> For example:
> I want to change col1 from X to Z in something that is
> current row 3 of the block (update where co1 = 'X')
>
> I create the read-consistent version of the block, and
> find that the same row was there when my transaction
> started - but the value of col1 was then 'Y'.
>
> So I can't update it because when my transaction started
> it wasn't a row that I was supposed to update.
>
> For more details on how messy and expensive this can get
> do a search on AskTom for "write consistency"
Thank you Jonathan,
I think I understand consistent reads works, in general. What puzzles
me, still, can perhaps be better demonstrated with the following
examples/scenarios:
TrA: Begins
TrB: Begins
TrB: Updates a row ( which is row #3 in block #100 ) and locks it
TrA: Wishes to update row #4 on the same block(block #100). So it
fetches the block. Because of the consistent read
properties, the block data it will fetch will be == block data when
TrA began.
It updates the value, set a lock on row #4 and Commits.
TrB: Commits the data on disk. That data will hold the updated row#3
value, but will overwrite #row4 updated ( by TrA) data.
Another example:
TrA: Begins
TrB: Begins
TrA: Updates a row ( row#3 on block #100) and before it does so, it
locks it
TrB: wishes to update the same row (row#3 on block #100) and before it
does so it attempts to lock it. Because
of read consistent read properties, TrB will fetch the block as it was
when TrB began, whereas there is no lock on row#3
So itself sets a lock on row#3.
It seem as for this example, it shouldn't rely on a consistent read
but get the latest data for that block, for the on-block row-level
locking
to work.
Thank you,
MarkP
xho...@gmail.com
> Thank you Jonathan,
>
> I think I understand consistent reads works, in general. What puzzles
> me, still, can perhaps be better demonstrated with the following
> examples/scenarios:
>
> TrA: Begins
> TrB: Begins
> TrB: Updates a row ( which is row #3 in block #100 ) and locks it
> TrA: Wishes to update row #4 on the same block(block #100). So it
> fetches the block. Because of the consistent read
> properties, the block data it will fetch will be == block data when
> TrA began.
OK.
> It updates the value,
consistent read blocks are read-only. It inspects the consistent read copy
of block 100, but when it goes to actually do the update that goes into the
*current* block 100, not the consistent read copy of it.
> set a lock on row #4 and Commits.
> TrB: Commits the data on disk.
Commits to disk occur through the redo logs, not the db block buffer.
> That data will hold the updated row#3
> value, but will overwrite #row4 updated ( by TrA) data.
>
> Another example:
> TrA: Begins
> TrB: Begins
> TrA: Updates a row ( row#3 on block #100) and before it does so, it
> locks it
> TrB: wishes to update the same row (row#3 on block #100) and before it
> does so it attempts to lock it. Because
> of read consistent read properties, TrB will fetch the block as it was
> when TrB began, whereas there is no lock on row#3
> So itself sets a lock on row#3.
Locks are set in the current block, not in consistent read copies of
blocks.
> It seem as for this example, it shouldn't rely on a consistent read
> but get the latest data for that block, for the on-block row-level
> locking
> to work.
It does both. It gets the consistent read copy, and assures that that
version of that row satisfies the where clause. Then it gets the current
block, and assures that the current version of the row there isn't locked
and hasn't changed since the consistent version, then locks the row in the
current block.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
Jonathan Lewis
Notes in line:
"markpapadakis" <markpa...@gmail.com> wrote in message
news:8eeeab2a-9da6-4906...@e6g2000prf.googlegroups.com...
>
> TrA: Begins
> TrB: Begins
> TrB: Updates a row ( which is row #3 in block #100 ) and locks it
> TrA: Wishes to update row #4 on the same block(block #100). So it
> fetches the block. Because of the consistent read
> properties, the block data it will fetch will be == block data when
> TrA began.
It gets the CURRENT block to update it.
It gets the CR block to check that row #3 has not changes since
the moment TrA started. It hasn't, so row #3 can be changed.
> It updates the value, set a lock on row #4 and Commits.
> TrB: Commits the data on disk. That data will hold the updated row#3
> value, but will overwrite #row4 updated ( by TrA) data.
>
>
> Another example:
> TrA: Begins
> TrB: Begins
> TrA: Updates a row ( row#3 on block #100) and before it does so, it
> locks it
> TrB: wishes to update the same row (row#3 on block #100) and before it
> does so it attempts to lock it. Because
> of read consistent read properties, TrB will fetch the block as it was
> when TrB began, whereas there is no lock on row#3
No, trB gets the current block and finds the row locked.
trB waits for the lock to disappear as trA commits.
When the lock disappears, trB gets the block in current mode
and locks the row, gets the block in read consistent mode (going
back to the point in time when trB started) and discovers that
row #3 has been changed in the interim. At this point it MAY
be possible to continue, it might be necessary for trB to roll back
and start again.
> So itself sets a lock on row#3.
> It seem as for this example, it shouldn't rely on a consistent read
> but get the latest data for that block, for the on-block row-level
> locking
> to work.
>
> Thank you,
> MarkP
>
--
markpapadakis
wrote:
> Notes in line:
>
> "markpapadakis" <markpapada...@gmail.com> wrote in message
>
> news:8eeeab2a-9da6-4906...@e6g2000prf.googlegroups.com...
I was missing the fact that the current block is used for updates,
then.
Thank you both,
Mark Papadakis