Can't dirty read uncommitted rows?

Hi, I've got a bit of a puzzler where I want to dirty read some rows in database before the transaction is committed. I can do this in our testing database (IDS 11.70TC7GE on 32bit windows server 2003) successfully, but the same thing is failing on our later databases - just returns zero rows found (empty result set). Tried IDS 11.70FC5GE 64bit on Windows Server 2008r2 and also IDS 12.10FC1WE 64bit on Windows Server 2012r2. All servers tried are domain member servers but stand alone - no replication set up. Maybe there is some new configuration setting I'm missing to enable this? Onconfig has: USELASTCOMMITTED ALL DEF_TABLE_LOCKMODE row All tables are row level locking. Our applications use "set isolation to committed read last committed" My testing below, was using DBA accounts: Session A - create table bryce_tmp ( b_id serial ) lock mode row; set isolation to committed read last committed;begin work; insert into bryce_tmp values ("0"); insert into bryce_tmp values ("0"); insert into bryce_tmp values ("0"); meanwhile, in Session B, different user - set isolation to dirty read; select * from bryce_tmp; ==>> no rows found. It should have found the three rows I just inserted. Does anyone know why this would no longer work for me? Regards, Bryce Stenberg.

With: USELASTCOMMITTED ALL any session on DIRTY READ or COMMITTED READ is automatically "promoted" to COMMITTED READ LAST COMMITED. You must change that parameter. Regards On Feb 19, 2015 4:46 AM, "BRYCE STENBERG" <bryce@hrnz.co.nz> wrote: > Hi, > > I've got a bit of a puzzler where I want to dirty read some rows in > database > before the transaction is committed. > I can do this in our testing database (IDS 11.70TC7GE on 32bit windows > server > 2003) successfully, but the same thing is failing on our later databases - > just returns zero rows found (empty result set). > > Tried IDS 11.70FC5GE 64bit on Windows Server 2008r2 and also IDS 12.10FC1WE > 64bit on Windows Server 2012r2. All servers tried are domain member servers > but stand alone - no replication set up. > > Maybe there is some new configuration setting I'm missing to enable this? > > Onconfig has: > USELASTCOMMITTED ALL > DEF_TABLE_LOCKMODE row> > All tables are row level locking. Our applications use "set isolation to > committed read last committed" > > My testing below, was using DBA accounts: > > Session A - > create table bryce_tmp ( b_id serial ) lock mode row; > set isolation to committed read last committed;> begin work; > insert into bryce_tmp values ("0"); > insert into bryce_tmp values ("0"); > insert into bryce_tmp values ("0");> > meanwhile, in Session B, different user - > set isolation to dirty read; > select * from bryce_tmp;> > ==>> no rows found. > > It should have found the three rows I just inserted. > > Does anyone know why this would no longer work for me? > > Regards, > Bryce Stenberg. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b5d41daa09212050f6c3d3e

Bryce, The use of "USELASTCOMMITTED" in the config is instructing Session B that when it hits a lock then return the last value committed to the database. The rows inserted in Session A have not been committed, so Session B returns no records. If you set USELASTCOMMITTED to NONE or 'COMMITTED READ', and then restart Session B (and still using dirty read), then you'll see the inserted records. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BRYCE STENBERG Sent: Wednesday, February 18, 2015 9:45 PM To: ids@iiug.org Subject: Can't dirty read uncommitted rows? [34702] Hi, I've got a bit of a puzzler where I want to dirty read some rows in database before the transaction is committed. I can do this in our testing database (IDS 11.70TC7GE on 32bit windows server 2003) successfully, but the same thing is failing on our later databases - just returns zero rows found (empty result set). Tried IDS 11.70FC5GE 64bit on Windows Server 2008r2 and also IDS 12.10FC1WE 64bit on Windows Server 2012r2. All servers tried are domain member servers but stand alone - no replication set up. Maybe there is some new configuration setting I'm missing to enable this? Onconfig has: USELASTCOMMITTED ALL DEF_TABLE_LOCKMODE row All tables are row level locking. Our applications use "set isolation to committed read last committed" My testing below, was using DBA accounts: Session A - create table bryce_tmp ( b_id serial ) lock mode row; set isolation tocommitted read last committed; begin work; insert into bryce_tmp values ("0"); insert into bryce_tmp values ("0"); insert into bryce_tmp values ("0"); meanwhile, in Session B, different user - set isolation to dirty read; select * from bryce_tmp; ==>> no rows found. It should have found the three rows I just inserted. Does anyone know why this would no longer work for me? Regards, Bryce Stenberg. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Fernanado and Mike, I thought it would be something basic. I missed seeing that USELASTCOMMITTED was set different in testing database. Regards, Bryce.