Re: Better demonstration of the serious transaction bug in Oracle

Oracle 8.1.5, NT 4.0 SP 6a:

Total Transactions

                 109

SQL> select count(*) "Correct Transactions" from isolationtest where val = 200;

Correct Transactions

                 104

SQL> select count(*) "Failed Transactions" from isolationtest where val != 200;

Failed Transactions

                   5

Martin

Joseph Weinstein wrote:
>
> 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 - 16:34:50 CST