Re: serious, silent, transaction bug in Oracle

From: Joseph Weinstein <joe_at_weblogic.com>
Date: Tue, 25 Jan 2000 13:07:03 -0800
Message-ID: <388E1077.906CA4DC@weblogic.com>

DriftWood wrote:

> The actual context of the bug is very convoluted and not nearly as
> easy to reproduce as you imply. This might help explain why it hasn't
> been resolved yet.

Thanks for your input! It's not actually all that hard to understand: A serializable transaction that inserts and also updates a row may silently fail to do the update.

  It's also not difficult for an Oracle user to reproduce. I've improved the script to demonstrate the failure in 5 out of 16 transactions in an 85-line SQL script! Here are the simple steps you can follow:

Step 1: Put the small script below in a file 'bug.sql' in your current directory.
Step 2: Start SQL-PLUS
Step 3: At the 'SQL>' prompt, enter: start bug

As I said, this will run 16 very simple transactions, and then do a query to show how many of them failed. Do please let me know, and/or post your results.
Joe

• start of bug.sql (85 lines) ---- create table isolationtest (id char(250) primary key, val float);

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('1',100); update isolationtest set val = 200 where id = '1'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('2',100); update isolationtest set val = 200 where id = '2'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('3',100); update isolationtest set val = 200 where id = '3'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('4',100); update isolationtest set val = 200 where id = '4'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('5',100); update isolationtest set val = 200 where id = '5'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('6',100); update isolationtest set val = 200 where id = '6'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('7',100); update isolationtest set val = 200 where id = '7'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('8',100); update isolationtest set val = 200 where id = '8'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('9',100); update isolationtest set val = 200 where id = '9'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('10',100); update isolationtest set val = 200 where id = '10'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('11',100); update isolationtest set val = 200 where id = '11'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('12',100); update isolationtest set val = 200 where id = '12'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('13',100); update isolationtest set val = 200 where id = '13'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('14',100); update isolationtest set val = 200 where id = '14'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('15',100); update isolationtest set val = 200 where id = '15'; COMMIT; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into isolationtest (id,val) values ('16',100); update isolationtest set val = 200 where id = '16'; COMMIT;

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 Tue Jan 25 2000 - 15:07:03 CST