Re: Disk Blocks/pages, Consistent Reads, undo

Notes in line:

"markpapadakis" <markpapadakis_at_gmail.com> wrote in message news:8eeeab2a-9da6-4906-a62e-f580038df337_at_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
>

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