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

From: Joe Weinstein <joeNOSPAM_at_bea.com>
Date: Thu, 10 Nov 2005 09:17:58 -0800
Message-ID: <437380C6.1060809@bea.com>

Jonathan Lewis wrote:
> "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.

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.

   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? Joe Received on Thu Nov 10 2005 - 11:17:58 CST