Re: Oracle has fixed the locking of unrelated data for pending XA transactions

From: Joe Weinstein <joeNOSPAM_at_bea.com>
Date: Fri, 11 Nov 2005 13:18:47 -0800
Message-ID: <43750AB7.1070803@bea.com>

Jonathan Lewis wrote:

> "Joe Weinstein" <joeNOSPAM_at_bea.com> wrote in message
> news:437380C6.1060809_at_bea.com...
>

>>
>>Jonathan Lewis wrote:
>>
>>>It's not 100% good news, of course.
>>>
>>>The original behaviour was probably deliberate
>>>to avoid inconsistent reporting - which is now
>>>possible.
>>>
>>>A process querying the block subject to a pending
>>>commit now gets a consistent read clone, which
>>>means it sees the data as it was before the actions
>>>of the pending transaction.
>>>
>>>Consider this option:
>>>
>>>Session 1
>>>    update row1_at_db1 (add 100 to local balance)
>>>    update row2_at_db2 (subtract 100 from local balance)
>>>    2PC prepare
>>>    2PC commit
>>>        db1 receives message and commits
>>>        db2 is delayed
>>>
>>>Session 2
>>>    Start distributed query
>>>        select row1_at_db1    -- gets NEW version of data
>>>        select row2_at_db2    -- gets OLD version of data
>>>
>>>Session 1
>>>    db2 gets message and commits
>>>
>>
>>Interesting. I am illiterate (though I am reading a Tom
>>Kyte book to begin fixing that) about Oracle internals. So
>>in the previous condition, Session 2 would block trying
>>to read row2_at_db2, and also, some other Session 3 would
>>block trying to read row3_at_db2 that got locked along
>>with row2_at_db2.

>
>
> Yes, prior to the change in code, session 2 would
> not be allowed to see row2_at_db2 at all because the
> owning instance would not know whether the
> global transaction from session 1 had committed
> or rolled back - it would know only that it should
> have had a commit or rollback, and that every
> other database involved may have had their final
> message already.
>
> And any session that started a query between
> the "prepare" and "commit" and tried to read ANY
> row in the same block as row2_at_db2 would suffer
> from the same wait.
>
>

>>  Oracle does have a way of blocking readers of a single
>>row. Isn't that (naively considered) what it should employ
>>during prepare, and be lifted during commit?

>
>
> Oracle does NOT have a way of blocking reads of
> a single row. Oracle "never" blocks readers - because
> the reader can always find the correct version of the
> block - except in this special case. The problem is that
> Oracle created the read-consistency code to operate at
> BLOCK level, not row-level, so the mechanism can't
> be used (legally) when there is a row in an unknown state.
>
> There is a parameter _row_cr that suggests Oracle is
> working on row-level consistent reads; but according to
> MetaLink, this should not be enabled in production systems
> yet.

Thanks for the valuable insight!
Joe Weinstein at BEA Systems Received on Fri Nov 11 2005 - 15:18:47 CST