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

From: VC <boston103_at_hotmail.com>
Date: Wed, 03 Dec 2003 01:44:39 GMT
Message-ID: <aebzb.402427$Tr4.1157830@attbi_s03>

Hello Andrew,

I apologize for a possibly incorrect follow-up attribution -- my newsreader is acting up.

You wrote:

> andrew.clarke_at_logicacmg.com (APC) wrote in message news:<58428d02.0312020310.57fea9ae_at_posting.google.com>...
> tengizk_at_Hotmail.com (Tengiz Kharatishvili) wrote in message
news:<3fcc40e7.0312010112.4ffa7d46_at_posting.google.com>...
> 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.
> .
> 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.
>

I re-produced the original poster's results on my Windows Oracle 9.2.0.4 installation (scenario 2 where all three rows are updated as opposed to the two-rows-update in scenario 1):

Session 1:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Dec 2 20:22:20 2003

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

SQL> drop table accounts;

Table dropped.

SQL> create table accounts (

  2        acc int primary key,
  3        amount int,
  4        pending int,
  5        cleared date

6 );

Table created.

SQL>
SQL> insert into accounts values(1,10,-2,null);

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>
SQL> update accounts
2 set pending = pending - 1, cleared = null where acc = 1;

1 row updated.

SQL>
SQL> update accounts
2 set pending = pending + 1, cleared = null where acc = 3;

1 row updated.

SQL>
SQL> select * from accounts;

ACC AMOUNT PENDING CLEARED ---------- ---------- ---------- ---------

         1         10         -3
         2          0          2
         3          0          1

In Session 2:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Dec 2 20:22:31 2003

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

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

Blocked by Session 1 ****

In Session 1:

SQL> commit;

Commit complete.

SQL> In Session 2:

3 rows updated.

SQL> select * from accounts;

ACC AMOUNT PENDING CLEARED

---------- ---------- ---------- ---------
         1          7          0 02-DEC-03
         2          2          0 02-DEC-03
         3          1          0 02-DEC-03

SQL> As you can see all the three rows have been updated. The reason for Oracle's change of mind is the 'pending <> 0' predicate presence in Scenario 2. Since the original set of rows is no longer a subset of the new set of rows satisfying the predicate Oracle has to rollback and restart, thereby getting an opportunity to update all the three rows. I've been able to reproduce this sort of behaviour under Unix (Linux, Solaris, AIX) as well as under Windows.

Please try running the experiment again and share your findings since, if your results are correct, it would appear that Oracle's behaviour is even more inconsistent than I'd thought before.

Thank you.

Rgds. Received on Tue Dec 02 2003 - 19:44:39 CST