Re: serious, silent, transaction bug in Oracle

Hi All. I was quickly contacted by two very knowledgeable Oracle folks, who confirmed the bug, and who pointed me to their known bug #440317, shown to exist 7.3.4, 8.0.3, 8.0.6 and 8.1.5.

One of these folks said that this bug is going to be fixed in 8.2, though my tech support contact stepped me through this bug's documented history, which was:

January '97 - first reported/verified.
April   '97 - scheduled for fixing in 8.0.4
March   '98 - rescheduled for fixed in 8.1
April   '98 - rescheduled for fixing in 8.2.

There have been on updates to this issue for the last 20 months.

I would be very grateful to hear of a firm commitment to fixing it as opposed to rescheduling it. In the mean time, there is no workaround if you want an insert and an update like mine in a single transaction, unless the primary key constraint is dropped from the table so there is no index, or SERIALIZABLE isolation is dropped.

Joe Weinstein at BEA, the home of WebLogic

Joseph Weinstein wrote:

> Hi.
> I will be going to Oracle tech support to report this. Is anyone aware
> of this? The issue is that the SQL (shown in SQL-PLUS form)
>
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('143',100);
> update isolationtest set val = 200 where id = '143';
> COMMIT;
> END;
> /
>
> This SQL will *sometimes* silently fail, such that a subsequent
> query will show that the inserted row still has the 100 value in
> the val column. The bug occurs regularly and predictably for me.
> In a series of 143 such transactions, the 142nd one will silently
> fail. Attached is a script to pipe into SQL-PLUS, though I've
> duplicated the bug with JDBC too. At the end of the output I get:
>
> 'TOTALTRANSACTIONS COUNT(*)
> ------------------ ----------
> total transactions 143
>
> SQL>
> 'GOODTRANSACTIONS COUNT(*)
> ----------------- ----------
> good transactions 142
>
> SQL>
> 'BROKENTRANSACTIONS COUNT(*)
> ------------------- ----------
> broken transactions 1
>
> Joe Weinstein at BEA, the home of WebLogic
> --
>
> PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for
> people who want to work with Java and E-Commerce infrastructure products. Send
> resumes to joe_at_beasys.com
> --------------------------------------------------------------------------------
> 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
>
> ----------------------------------------------------------------------------------------------------
> create table isolationtest (id varchar(10) primary key, val float);
>
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('1',100);
> update isolationtest set val = 200 where id = '1';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('2',100);
> update isolationtest set val = 200 where id = '2';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('3',100);
> update isolationtest set val = 200 where id = '3';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('4',100);
> update isolationtest set val = 200 where id = '4';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('5',100);
> update isolationtest set val = 200 where id = '5';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('6',100);
> update isolationtest set val = 200 where id = '6';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('7',100);
> update isolationtest set val = 200 where id = '7';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('8',100);
> update isolationtest set val = 200 where id = '8';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('9',100);
> update isolationtest set val = 200 where id = '9';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('10',100);
> update isolationtest set val = 200 where id = '10';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('11',100);
> update isolationtest set val = 200 where id = '11';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('12',100);
> update isolationtest set val = 200 where id = '12';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('13',100);
> update isolationtest set val = 200 where id = '13';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('14',100);
> update isolationtest set val = 200 where id = '14';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('15',100);
> update isolationtest set val = 200 where id = '15';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('16',100);
> update isolationtest set val = 200 where id = '16';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('17',100);
> update isolationtest set val = 200 where id = '17';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('18',100);
> update isolationtest set val = 200 where id = '18';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('19',100);
> update isolationtest set val = 200 where id = '19';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('20',100);
> update isolationtest set val = 200 where id = '20';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('21',100);
> update isolationtest set val = 200 where id = '21';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('22',100);
> update isolationtest set val = 200 where id = '22';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('23',100);
> update isolationtest set val = 200 where id = '23';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('24',100);
> update isolationtest set val = 200 where id = '24';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('25',100);
> update isolationtest set val = 200 where id = '25';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('26',100);
> update isolationtest set val = 200 where id = '26';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('27',100);
> update isolationtest set val = 200 where id = '27';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('28',100);
> update isolationtest set val = 200 where id = '28';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('29',100);
> update isolationtest set val = 200 where id = '29';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('30',100);
> update isolationtest set val = 200 where id = '30';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('31',100);
> update isolationtest set val = 200 where id = '31';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('32',100);
> update isolationtest set val = 200 where id = '32';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('33',100);
> update isolationtest set val = 200 where id = '33';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('34',100);
> update isolationtest set val = 200 where id = '34';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('35',100);
> update isolationtest set val = 200 where id = '35';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('36',100);
> update isolationtest set val = 200 where id = '36';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('37',100);
> update isolationtest set val = 200 where id = '37';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('38',100);
> update isolationtest set val = 200 where id = '38';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('39',100);
> update isolationtest set val = 200 where id = '39';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('40',100);
> update isolationtest set val = 200 where id = '40';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('41',100);
> update isolationtest set val = 200 where id = '41';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('42',100);
> update isolationtest set val = 200 where id = '42';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('43',100);
> update isolationtest set val = 200 where id = '43';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('44',100);
> update isolationtest set val = 200 where id = '44';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('45',100);
> update isolationtest set val = 200 where id = '45';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('46',100);
> update isolationtest set val = 200 where id = '46';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('47',100);
> update isolationtest set val = 200 where id = '47';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('48',100);
> update isolationtest set val = 200 where id = '48';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('49',100);
> update isolationtest set val = 200 where id = '49';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('50',100);
> update isolationtest set val = 200 where id = '50';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('51',100);
> update isolationtest set val = 200 where id = '51';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('52',100);
> update isolationtest set val = 200 where id = '52';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('53',100);
> update isolationtest set val = 200 where id = '53';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('54',100);
> update isolationtest set val = 200 where id = '54';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('55',100);
> update isolationtest set val = 200 where id = '55';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('56',100);
> update isolationtest set val = 200 where id = '56';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('57',100);
> update isolationtest set val = 200 where id = '57';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('58',100);
> update isolationtest set val = 200 where id = '58';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('59',100);
> update isolationtest set val = 200 where id = '59';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('60',100);
> update isolationtest set val = 200 where id = '60';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('61',100);
> update isolationtest set val = 200 where id = '61';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('62',100);
> update isolationtest set val = 200 where id = '62';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('63',100);
> update isolationtest set val = 200 where id = '63';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('64',100);
> update isolationtest set val = 200 where id = '64';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('65',100);
> update isolationtest set val = 200 where id = '65';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('66',100);
> update isolationtest set val = 200 where id = '66';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('67',100);
> update isolationtest set val = 200 where id = '67';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('68',100);
> update isolationtest set val = 200 where id = '68';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('69',100);
> update isolationtest set val = 200 where id = '69';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('70',100);
> update isolationtest set val = 200 where id = '70';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('71',100);
> update isolationtest set val = 200 where id = '71';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('72',100);
> update isolationtest set val = 200 where id = '72';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('73',100);
> update isolationtest set val = 200 where id = '73';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('74',100);
> update isolationtest set val = 200 where id = '74';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('75',100);
> update isolationtest set val = 200 where id = '75';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('76',100);
> update isolationtest set val = 200 where id = '76';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('77',100);
> update isolationtest set val = 200 where id = '77';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('78',100);
> update isolationtest set val = 200 where id = '78';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('79',100);
> update isolationtest set val = 200 where id = '79';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('80',100);
> update isolationtest set val = 200 where id = '80';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('81',100);
> update isolationtest set val = 200 where id = '81';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('82',100);
> update isolationtest set val = 200 where id = '82';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('83',100);
> update isolationtest set val = 200 where id = '83';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('84',100);
> update isolationtest set val = 200 where id = '84';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('85',100);
> update isolationtest set val = 200 where id = '85';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('86',100);
> update isolationtest set val = 200 where id = '86';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('87',100);
> update isolationtest set val = 200 where id = '87';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('88',100);
> update isolationtest set val = 200 where id = '88';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('89',100);
> update isolationtest set val = 200 where id = '89';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('90',100);
> update isolationtest set val = 200 where id = '90';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('91',100);
> update isolationtest set val = 200 where id = '91';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('92',100);
> update isolationtest set val = 200 where id = '92';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('93',100);
> update isolationtest set val = 200 where id = '93';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('94',100);
> update isolationtest set val = 200 where id = '94';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('95',100);
> update isolationtest set val = 200 where id = '95';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('96',100);
> update isolationtest set val = 200 where id = '96';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('97',100);
> update isolationtest set val = 200 where id = '97';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('98',100);
> update isolationtest set val = 200 where id = '98';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('99',100);
> update isolationtest set val = 200 where id = '99';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('100',100);
> update isolationtest set val = 200 where id = '100';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('101',100);
> update isolationtest set val = 200 where id = '101';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('102',100);
> update isolationtest set val = 200 where id = '102';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('103',100);
> update isolationtest set val = 200 where id = '103';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('104',100);
> update isolationtest set val = 200 where id = '104';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('105',100);
> update isolationtest set val = 200 where id = '105';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('106',100);
> update isolationtest set val = 200 where id = '106';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('107',100);
> update isolationtest set val = 200 where id = '107';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('108',100);
> update isolationtest set val = 200 where id = '108';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('109',100);
> update isolationtest set val = 200 where id = '109';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('110',100);
> update isolationtest set val = 200 where id = '110';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('111',100);
> update isolationtest set val = 200 where id = '111';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('112',100);
> update isolationtest set val = 200 where id = '112';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('113',100);
> update isolationtest set val = 200 where id = '113';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('114',100);
> update isolationtest set val = 200 where id = '114';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('115',100);
> update isolationtest set val = 200 where id = '115';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('116',100);
> update isolationtest set val = 200 where id = '116';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('117',100);
> update isolationtest set val = 200 where id = '117';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('118',100);
> update isolationtest set val = 200 where id = '118';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('119',100);
> update isolationtest set val = 200 where id = '119';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('120',100);
> update isolationtest set val = 200 where id = '120';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('121',100);
> update isolationtest set val = 200 where id = '121';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('122',100);
> update isolationtest set val = 200 where id = '122';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('123',100);
> update isolationtest set val = 200 where id = '123';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('124',100);
> update isolationtest set val = 200 where id = '124';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('125',100);
> update isolationtest set val = 200 where id = '125';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('126',100);
> update isolationtest set val = 200 where id = '126';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('127',100);
> update isolationtest set val = 200 where id = '127';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('128',100);
> update isolationtest set val = 200 where id = '128';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('129',100);
> update isolationtest set val = 200 where id = '129';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('130',100);
> update isolationtest set val = 200 where id = '130';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('131',100);
> update isolationtest set val = 200 where id = '131';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('132',100);
> update isolationtest set val = 200 where id = '132';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('133',100);
> update isolationtest set val = 200 where id = '133';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('134',100);
> update isolationtest set val = 200 where id = '134';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('135',100);
> update isolationtest set val = 200 where id = '135';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('136',100);
> update isolationtest set val = 200 where id = '136';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('137',100);
> update isolationtest set val = 200 where id = '137';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('138',100);
> update isolationtest set val = 200 where id = '138';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('139',100);
> update isolationtest set val = 200 where id = '139';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('140',100);
> update isolationtest set val = 200 where id = '140';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('141',100);
> update isolationtest set val = 200 where id = '141';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('142',100);
> update isolationtest set val = 200 where id = '142';
> COMMIT;
> END;
> /
> BEGIN
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values ('143',100);
> update isolationtest set val = 200 where id = '143';
> COMMIT;
> END;
> /
> select * from isolationtest where id = '141';
> select * from isolationtest where id = '142';
> select * from isolationtest where id = '143';
>
> select 'total transactions', count(*) from isolationtest;
> select 'good transactions', count(*) from isolationtest where val = 200;
> select 'broken transactions', count(*) from isolationtest where val != 200;
>
> drop table isolationtest;

--

PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for people who want to work with Java and E-Commerce infrastructure products. Send resumes to joe_at_beasys.com

                    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