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

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

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005