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

From: APC <andrew.clarke_at_logicacmg.com>
Date: 3 Dec 2003 04:25:07 -0800
Message-ID: <58428d02.0312030425.528c46a8@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.

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

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. Received on Wed Dec 03 2003 - 06:25:07 CST