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

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.

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

In his first example Session 2 runs this statement:

 SQL> update accounts
   2  set amount = amount + pending, pending = 0, cleared = sysdate
   3  where cleared is null;

when this becomes unblocked it updates two rows.  In his alternative
scenario, this statement:

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

updates three rows.  Why the difference?

Because the statements run in Session 1 change the value of PENDING
for acc #1, which is in the session 2 resultset.  When PENDING is in
the WHERE clause this change causes a re-start because Oracle realises
it needs to re-evaluate the predicates.  When the WHERE clause simply
uses CLEARED there is no re-start, because the value of CLEARED has
not changed for acc #1.  True, it has changed for acc #3, but acc #3
is not in the existing session 2 resultset, so no re-evaluation of the
predicate is required.

This is both consistent and sensible.  Sensible because the
alternative is to force a re-start for every blocked statement, which
I don't think is desirable.   Consistent because, well, consider this
alternative scenario:

 SQL> DROP TABLE accounts;

 Table dropped.

 SQL> CREATE TABLE accounts (
   2        acc number PRIMARY KEY,
   3        amount number,
   4        pending number,
   5        cleared date
   6  );

 Table created.

 SQL> INSERT INTO accounts VALUES (1,10,-2,sysdate);

 1 row created.

 SQL> INSERT INTO accounts VALUES (2,0,2,null);

 1 row created.

 SQL> INSERT INTO accounts VALUES (3,0,0,sysdate);

 1 row created.

 SQL> COMMIT;

 Commit complete.

 SQL> UPDATE accounts
   2  SET pending = pending - 1, cleared = NULL WHERE acc = 1;

 1 row updated.

 SQL> UPDATE accounts
   2  SET pending = pending - 1, cleared = SYSDATE WHERE acc = 2;

 1 row updated.

 SQL> UPDATE accounts
   2  SET pending = pending + 1, cleared = NULL WHERE acc = 3;

 1 row updated.

 SQL> SELECT * FROM accounts;

        ACC     AMOUNT    PENDING CLEARED
 ---------- ---------- ---------- ---------
          1         10         -3
          2          0          1 03-DEC-03 
          3          0          1

 --  over to Session 2:

 SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 3 10:02:41 2003

 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


 Connected to:
 Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
 With the Partitioning, OLAP and Oracle Data Mining options
 JServer Release 9.2.0.1.0 - Production

 SQL> UPDATE accounts
   2  SET amount = amount + pending, pending = 0, cleared = sysdate
   3  WHERE cleared IS NULL;

 -- we are blocked by Session 1 

 -- back to Session 1 
 -- can you guess what's going to happen in Session 2???

 SQL> COMMIT;

 Commit complete.

 SQL>

 --  ta-da!

  2 rows updated.

 SQL> SELECT * FROM accounts;

        ACC     AMOUNT    PENDING CLEARED
 ---------- ---------- ---------- ---------
          1          7          0 03-DEC-03
          2          0          0 03-DEC-03
          3          1          0 03-DEC-03

 SQL>

Did you guess right?

Cheers, APC

P.S.  I wish to withdraw my use of the word "censor" in regards to Tom
Kyte's publication policy.  Tom has to choose which posts to publish
and which to not.  I'm not saying whether I think he was right not to
publish Tengiz's post on AskTom - I don't know its contents, but I
doubt there was anything sinister underlying his decision.