About read consistency

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 15 Dec 2005 19:42:36 +0100
Message-ID: <43a1b993$0$27903$9b4e6d93@newsread4.arcor-online.net>

Simple testcase:

Step 1

SQL> create table CUSTOMER

2 (

   3    CUSTOMERID         NUMBER(19),
   4    LASTNAME           VARCHAR2(60 CHAR),
   5    PURGED             NUMBER(1),
   6    UPDATE_STATUS      NUMBER(3),

   7 PROCESSING_STARTED DATE
   8 )
   9 ;

Table created.

SQL> insert into CUSTOMER (CUSTOMERID, LASTNAME, PURGED, UPDATE_STATUS, PROCESSING_STARTED)
2 values (9342546, 'Niebuhr', 1, 0, to_date('10-12-2005 18:54:04', 'dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into CUSTOMER (CUSTOMERID, LASTNAME, PURGED, UPDATE_STATUS, PROCESSING_STARTED)
2 values (13373671, 'Kupetz', 1, 0, to_date('10-12-2005 18:54:04', 'dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into CUSTOMER (CUSTOMERID, LASTNAME, PURGED, UPDATE_STATUS, PROCESSING_STARTED)
2 values (14320146, 'Dahhan', 1, 0, to_date('10-12-2005 18:54:04', 'dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> commit;

Commit complete.

Step 2
Now in the Session A:

SQL> col customerid for 999999999
SQL> col update_status for 99
SQL> col processing_started for a19
SQL> col purged for 99
SQL> SELECT

2 customerid

   3  ,update_status
   4  ,processing_started
   5  ,purged

   6 FROM customer
   7 WHERE customerid IN
   8 (SELECT customerid

   9      FROM CUSTOMER
  10      WHERE (update_status=0
  11             OR
  12             ( update_status=1 AND processing_started < sysdate-1))
  13      AND purged=1

14 )
15 for update;

CUSTOMERID UPDATE_STATUS PROCESSING_STARTED PURGED

---------- ------------- ------------------- ------
    9342546             0 10-DEC-05                1
   13373671             0 10-DEC-05                1
   14320146             0 10-DEC-05                1

Step 3
In the Session B

SQL> col customerid for 999999999
SQL> col update_status for 99
SQL> col processing_started for a19
SQL> col purged for 99
SQL> col lastname for a15
SQL> col update_status for 99
SQL> SELECT

2 customerid

   3  ,update_status
   4  ,processing_started
   5  ,purged

   6 FROM customer c
   7 WHERE customerid IN
   8 (SELECT customerid

   9           FROM CUSTOMER
  10           WHERE (update_status=0
  11                  OR
  12                  (update_status=1 AND processing_started < sysdate-1)
  13                 )
  14           AND  purged=1

  15 )
  16 for update
  17 /
-- Session B waits now for locked rows

Step 4
In the Session A SQL> update customer set update_status=1, processing_started=sysdate 2 ;

3 rows updated.

SQL> commit;

Commit complete.

Step 5
In the Session B

no rows selected

SQL> rollback;

Rollback complete.

Sofar all as expected.
If i however change in the Step 3 the select list to only customerid ( or any other column not touched in the update statement, like lastname ) , i get all the rows back:

Step 3
In the Session B SQL> SELECT 2 customerid 3 -- ,update_status 4 -- ,processing_started 5 -- ,purged 6 FROM customer c 7 WHERE customerid IN 8 (SELECT customerid 9 FROM CUSTOMER 10 WHERE (update_status=0 11 OR 12 (update_status=1 AND processing_started < sysdate-1) 13 ) 14 AND purged=1 15 ) 16 for update 17 /
Step 5
In the Session B CUSTOMERID
9342546 13373671 14320146

SQL> rollback;

Rollback complete.

I've the same behaviour on 9.2.0.6 and 10.2.0.1 on Linux and on 9.2.0.6 on Solaris. Is it a (maybe well known) bug, or did i overlook something obvious ?
All insights appreciated.

Best regards

Maxim Received on Thu Dec 15 2005 - 12:42:36 CST