Re: serious, silent, transaction bug in Oracle

From: Joseph Weinstein <joe_at_weblogic.com>
Date: Wed, 26 Jan 2000 11:19:05 -0800
Message-ID: <388F48A9.6902CA93@weblogic.com>

DriftWood wrote:

> Sure, here is the result:

Thank you very much! If you could do/answer the following, it would be of great service.

1 - On what OS is the DBMS, and has there been any reconfiguration that would allow 16 of those primary keys to fit on one index page? What is your block size? If it is bigger than the default, you may need more tx's to duplicate it.
2 - Would you run some more tx's? (I've attached a longer one for you). The bug occurs when an insert causes an index page to split. The new row on the new index page becomes invisible to the transaction. Only when enough transactions have occurred to split the page, will you see the bug.

I appreciate your concerns, and I hope you understand that if Oracle bug #440317 is reproducible at all, ever, it is very serious. After all, exactly what percentage of our clients transactions do we want to be correct? Exactly how many are we willing to accept failing silently?

Thomas Kyte <tkyte_at_us.oracle.com>, one of the best Oracle folks on the net, has verified it in , in 7.3.4, 8.0.3, 8.0.6 and 8.1.5 and emailed this in response to my initial post:

"I believe this is related to bug #440317 (reference that with support -- that one is about an INSERT followed by a select producing no data found on the newly inserted data). I reproduced in 7.3.4, 8.0.3, 8.0.6 and 8.1.5... Here is a much smaller testcase i ran in all for you to give to support:

set echo on
drop table isolationtest;
create table isolationtest (id varchar(10) primary key, val float);

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

    if ( rows_processed <> 1 )
    then

        dbms_output.put_line( 'Rows Processed = ' || rows_processed );
        dbms_output.put_line( sql_stmt );

    end if;
end;
/

BEGIN
    for i in 1 .. 1000 loop

        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        execute_immediate('insert into isolationtest (id,val) ' ||
                          ' values ('''||i||''',100)' );
        execute_immediate('update isolationtest set val = 200 ' ||
                          ' where id = '''||i||'''' );
        COMMIT;

    end loop;
END;
/

select 'total transactions',  count(*)   from isolationtest;
select 'good transactions',   count(*)   from isolationtest where val = 200;
select 'broken transactions', count(*)   from isolationtest where val != 200;

select * from isolationtest where val != 200;

It'll depend on your blocksize I believe as it took 500+ iterations to blow out for me. It appears to be the index and the maintenance thereof and our read consistency that is the culprit. When the index tree splits (hence the blocksize factor), we don't see the newly inserted row sometimes."

> H:\hold>sqlplus scott/tiger_at_V815
>
> SQL*Plus: Release 8.1.5.0.0 - Production on Wed Jan 26 12:20:57 2000
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> SQL> @bug.sql
>
> Table created.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Transaction set.
>
> 1 row created.
>
> 1 row updated.
>
> Commit complete.
>
> Total Transactions
> --------------------
> 16
>
> Correct Transactions
> --------------------
> 16
>
> Failed Transactions
> --------------------
> 0
>
> Table dropped.
>
> SQL>
--

PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for people who want to work with Java and E-Commerce infrastructure products. Send resumes to joe_at_beasys.com

---

                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm








application/x-unknown-content-type-sql_auto_file attachment: bug_script.sql

Received on Wed Jan 26 2000 - 13:19:05 CST