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".
