Message-ID: <43750AB7.1070803@bea.com> Date: Fri, 11 Nov 2005 13:18:47 -0800 From: Joe Weinstein User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server To: Jonathan Lewis Subject: Re: Oracle has fixed the locking of unrelated data for pending XA transactions References: <436fa6b8@news.beasys.com> <437380C6.1060809@bea.com> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit NNTP-Posting-Host: 216.148.48.12 X-Trace: news.beasys.com 1131743928 216.148.48.12 (11 Nov 2005 13:18:48 -0800) Organization: BEA SYSTEMS Inc Lines: 84 Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!nntp.abs.net!spool.news.uu.net!ash.uu.net!news.beasys.com!not-for-mail Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:255257 Jonathan Lewis wrote: > "Joe Weinstein" wrote in message > news:437380C6.1060809@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@db1 (add 100 to local balance) >>> update row2@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@db1 -- gets NEW version of data >>> select row2@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@db2, and also, some other Session 3 would >>block trying to read row3@db2 that got locked along >>with row2@db2. > > > Yes, prior to the change in code, session 2 would > not be allowed to see row2@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@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