Re: Better demonstration of the serious transaction bug in Oracle

From: Juri V.Zaitsev <oracle_at_eximb.kiev.ua>
Date: Fri, 28 Jan 2000 09:58:13 +0300
Message-ID: <2.07b5.1NDNK.FP1A11@eximb.kiev.ua>

Hi!

Oracle 7.3.3.6 & 7.3.4.5 (HP-UX 10.20)
Oracle 7.3.3.4 & 7.3.3.5 (SCO OpenServer 5.0.4)

Total Transactions

---

                1000

Correct Transactions

---

                 821

Failed Transactions

---

                 179

Oracle 8.0.5.2.2 (HP-UX 10.20)

ERROR at line 1:

ORA-08177: can't serialize access for this transaction
ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "SCOTT.EXECUTE_IMMEDIATE", line 7
ORA-06512: at line 5

SQL> select count(*) "Total      Transactions" from isolationtest;

Total Transactions

---

                   9

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

---

                   8

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

---

                   1

> 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 Fri Jan 28 2000 - 00:58:13 CST