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

Hello,

Please see my comments below.

"APC" <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
wrote in message
<news:[EMAIL PROTECTED]>...
> 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:

a. Session 2's selects Row 2 for the update as satisfying the predicate;
b. Session 2 is blocked by Session 1's update;
c. Session 1 commits;
d. 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;
e. 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.