Re: Single-statement 'write consistency' on read committed. Oh, really?

tengizk_at_Hotmail.com (Tengiz Kharatishvili) wrote in message news:<3fcc40e7.0312010112.4ffa7d46_at_posting.google.com>...
>> And finally - I must confess: I recently tried to post the
original
>> detailed question on Tom's website, but he essentially refused to
>> answer and did not allow the question and his reply for public
view.

Really? Tom Kyte censored your posting? I must admit, I am shocked.

However, I think the behaviour you describe may no longer be an issue.

I think we want it to always be true that Oracle re-starts filter out rows from the original resultset that no longer match but always ignore new rows that now match. We may prefer some other behaviour but it's more important to know where we stand. Guaranteeing consistent behaviour is the bedrock of our industry.

What concerned me in in your original posting was the way some variations on the Session 2 query pick up new rows after Session 1 commits:

>> update accounts
>> set amount = amount + pending, pending = 0, cleared = sysdate
>> where cleared is null and pending <> 0;

This statement updates only two rows if issued before Session 1 updates the table but updates _three_ rows if issued in parallel but before Session 1 has committed. In other words, adding "and pending <> 0" to the statement changes the result, for no apparent reason. I was able to reproduce this behaviour on Oracle 9.0.1 for Solaris, after reading your OTN posting.

This morning, having followed Mark Powell's link to here I tried it again, this time on Oracle 9.2 for Windows but I could not reproduce the results. On this install adding "and pending <> 0" does not change the resultset: Session 2 still only updates _two_ rows.

So, this looks to me to be either a fixed bug (incidentally, why did you say you thought Oracle didn't want to fix it, "for non-technical reasons"?) or it's one of those funnies we need to add to our new installation testing framework.

Cheers, APC Received on Tue Dec 02 2003 - 05:10:59 CST