Hello,
Sorry for the late reply, but i just saw this in my mailbox.
I ran the script at 2 different times with the output below:
I have never worked with SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
only what I have read in the documenation. Just curious, what situation are
you using this for?
What are the advantages/disadvantages as you see them?
SQL> BEGIN
2 for i in 1 .. 1000 loop
3 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
4 execute_immediate('insert into isolationtest (id,val) values
('''||i||''',100)' );
5 execute_immediate('update isolationtest set val = 200 where id
= '''||i||'''' );
6 COMMIT;
7 end loop;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) "Total Transactions" from isolationtest;
Total Transactions
--------------------
1000
SQL> select count(*) "Correct Transactions" from isolationtest where val =
200;
Correct Transactions
--------------------
1000
SQL> select count(*) "Failed Transactions" from isolationtest where val !=
200;
Failed Transactions
--------------------
0
----- Original Message -----
From: Joe Weinstein <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 28, 2000 6:27 PM
Subject: A better demonstrator of the Oracle SERIALIZABLE tx bug
> Hi. Some folks had trouble demonstrating the bug in Oracle's
> core DBMS, where a transaction running under SERIALIZABLE isolation,
> may silently fail in a way that incomplete or inconsistent data will
> be committed. I have included a much improved small 33-line script.
>
> I have gotten corroboration from Oracle employees that the bug
> exists in all versions of Oracle. The Oracle bug number is 440317.
> Their tech support has told me that this bug was first identified
> and recorded at Oracle in early '97.
>
> The bug scenario is:
> A transaction, running under serializable isolation inserts a row
> into a table, and then updates that row. If the insertion causes the
> index to allocate a new page for its entry, the transaction engine
> *loses sight of the new index page* for the duration of the transaction.
> Any update within the transaction which specifies the new key value will
> not find the new row. Because index growth depends on block size and
> index entry size, the frequency of this failure will vary.
>
> The script below will create a table and a procedure to do a simple
> insert-then-update transaction to the table. Then the script will loop
> through 1000 iterations of invoking the procedure. Lastly, it will
> query the table to present the number of correctly executed transactions.
>
> I get this output:
>
> ----------------------- sample output (29 lines) -------------------
> SQL*Plus: Release 3.3.4.0.1 - Production on Thu Jan 27 10:40:50 2000
> Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
>
> Connected to:
> Oracle7 Server Release 7.3.4.0.1 - Production
> PL/SQL Release 2.3.4.0.0 - Production
>
> SQL> @bug.sql
>
> Table created.
>
> Procedure created.
>
> PL/SQL procedure successfully completed.
>
>
> Total Transactions
> --------------------
> 1000
>
> Correct Transactions
> --------------------
> 821
>
> Failed Transactions
> --------------------
> 179
>
> Table dropped.
> ----------------- end of sample output ----------------
>
> To run the script, copy it to a file bug.sql in your current
> directory, bring up SQL-PLUS, and enter '@bug.sql' at the prompt.
>
> To those who run this test, do let me know your results at your
> earliest convenience.
>
> Thanks,
> Joe Weinstein at BEA
>
> ------------------ bug.sql SQL-PLUS script (33 lines) -------
> 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 script -----------------
>
> --------------------------------------------------------------------------
------
> 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
>
>
===========================================================================
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
>
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
