serious, silent, transaction bug in Oracle

From: Joseph Weinstein <joe_at_weblogic.com>
Date: Mon, 17 Jan 2000 11:46:45 -0800
Message-ID: <388371A5.5431222B@weblogic.com>

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; Received on Mon Jan 17 2000 - 13:46:45 CST