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

"Joe Weinstein" <joeNOSPAM_at_bea.com> wrote in message news:436fa6b8_at_news.beasys.com...
> Good news for any who may have suffered hanging applications
> because of Oracle's locking of logically unrelated data along
> with the relevant data for any pending prepared XA transaction.
> Oracle has removed the block-level locking:
>
> https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=3566945.8
>
> There is a patch available for
> 9.2.0.6 (Server Patch Set)
> 10.1.0.3 (Server Patch Set)
> 10.2.0.1 (Base Release)
>
> For any Oracle RAC customers using WebLogic and XA
> transactions, the appropriate DBMS patch for this
> 'enhancement' is recommended.
>
> Joe Weinstein at BEA
>
>

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

I know of one site that had to do some
re-engineering because of the old behaviour, and it would have helped them most of the time - but they would almost certainly have ended up generating some incorrect data if new behaviour had been in place at the time.

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