Re: Disk Blocks/pages, Consistent Reads, undo

markpapadakis <markpapadakis_at_gmail.com> wrote:

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