Better demonstration of the serious transaction bug in Oracle

From: Joseph Weinstein <joe_at_weblogic.com>
Date: Wed, 26 Jan 2000 11:57:45 -0800
Message-ID: <388F51B8.21776DFA@weblogic.com>

Hi. While some folks were able to duplicate the Oracle transaction bug, At least one person did not, with the small simple script I sent. Here is a looping script which is much more compact, and intelligently written (not by me). This runs 1000 transactions, each one inserting a unique row to a table, then updating it. The bug is that the update part of the transaction may silently fail. I get:

Total Transactions

---

                1000

Correct Transactions

---

                 821

Failed Transactions

---

                 179

If anyone would try this, and post their results, it would be very helpful. In SQL-PLUS, enter:
SQL>@bug.sql

Thanks,
Joe

• bug.sql 36 lines ------------------------------------- set echo on create table isolationtest (id char(250) 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 count(*) "Total   Transactions" from isolationtest;
select count(*) "Correct Transactions" from isolationtest where val = 200;
select count(*) "Failed  Transactions" from isolationtest where val != 200;

drop table isolationtest;
------------------------------ end of bug.sql ---------------------------------
Received on Wed Jan 26 2000 - 13:57:45 CST