Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Single-statement 'write consistency' on read committed. Oh, really?

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

From: VC <boston103_at_hotmail.com>
Date: Wed, 03 Dec 2003 22:48:44 GMT
Message-ID: <gLtzb.413266$Fm2.419314@attbi_s04>


Hello,

Please see my comments below.

"APC" <andrew.clarke_at_logicacmg.com <mailto:andrew.clarke_at_logicacmg.com>> wrote in message
<news:58428d02.0312030425.528c46a8_at_posting.google.com>...
> Following from previous posting: I was using a slightly different set
> of SQL than Tengiz used, which explains my different results on the
> 9.2 setup. Having re-done the tests using Tengiz's actual code I have
> duplicated his results.

I am glad that Oracle _is_ behaving consistently at least with respect to executing scenario 2 on different installations. Thank you for re-running the test.

>
> However, I now understand that this is the Oracle is supposed to work,
> and the results are *not* inconsistent.
>

Well, I am not so sure.

Firstly, regarding your test, it's easy enough to predict its outcome iff one knows about the Oracle re-start pecularity:

  1. Session 2's selects Row 2 for the update as satisfying the predicate;
  2. Session 2 is blocked by Session 1's update;
  3. Session 1 commits;
  4. Session 2 sees that row 2 no longer satisfies the predicate and restarts thereby getting two new rows (1 and 3) instead of the original row 2 as satisfying the predicate;
  5. Session 2 updates Rows 1 and 3.

Now why I am not happy about the observed behaviour. Briefly, the reasons are as follows:

  1. As I mentioned earlier, why bother with the restart at all since the READ COMMITTED IL promise would not be broken, restart or no restart. What's the rationale for the restarts ?
  2. Oracle's behaviour in scenarios 2 and 3, as well as in your example, is not intuitive. For example, in scenario 3, the second predicate ( ' (pending * 0) = 0' ) will always evaluate to 'true' assuming 'pending not null'. If the CBO were smart enough, it would exclude the predicate and then the re-scan would not happen thus producing a different result.
  3. The behaviour is not described anywhere in Oracle documentation.

<...skipped...>

Rgds. Received on Wed Dec 03 2003 - 16:48:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US