Re: problems with repeatable read - whole table is locked

From: harald_wehr@my-deja.com > >we have to use the isolation level "repeatable read" in our application. >First we have created following table: > >create table kunde(nr int primary key, name char(20)) lock mode row;> >Then we imported some rows and executed following transactions: > >set isolation to repeatable read;>begin work; >select * from kunde where nr < 4;> >After that we expect informix to lock only these selected rows. But when >we open another connection to the database and we execute next >statement: > >update kunde set name="mueller" where nr = 30> >we get this error messages: > > 243: Could not position within a table (u13210.kunde). > 113: ISAM error: the file is locked.> >It seems that informix locks the whole table. But we want informix only >to lock the selected rows. How can we solve this problem ? Informix will lock all rows it has to _look at_ to satisfy the query, not just the rows that actually satisfy the query. So, for example, if this SELECT ... FOR UPDATE does a sequential scan of the table, the whole table will be locked. Perhaps UPDATE STATISTICS might help, or optimizer directives. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Obnoxio The Clown wrote: > From: harald_wehr@my-deja.com > >... > >set isolation to repeatable read;> >begin work; > >select * from kunde where nr < 4;> > > >After that we expect informix to lock only these selected rows. But when > >we open another connection to the database and we execute next > >statement: > > > >update kunde set name="mueller" where nr = 30> > > >we get this error messages: > > > > 243: Could not position within a table (u13210.kunde). > > 113: ISAM error: the file is locked. > >... > Informix will lock all rows it has to _look at_ to satisfy the query, not > just the rows that actually satisfy the query. Surely, this can not be right. I can imagine the reverse, though, a situation as follows : The SELECT locks rows where nr < 4 (examine the session's "onstat -u" row to determine the number of locks taken). The UPDATE does a sequential scan (stats not updated) to update the row that matches "nr = 30". It runs into the 243 error because it can not look up the locked rows to determine whether they qualify to be updated. I agree with your solution, though - Update statistics before trying again. > So, for example, if this > SELECT ... FOR UPDATE does a sequential scan of the table, the whole table > will be locked. Perhaps UPDATE STATISTICS might help, or optimizer > directives. Rudy

In article <8gga89$8t5$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: harald_wehr@my-deja.com > > > >we have to use the isolation level "repeatable read" in our application. > >First we have created following table: > > > >create table kunde(nr int primary key, name char(20)) lock mode row;> > > >Then we imported some rows and executed following transactions: > > > >set isolation to repeatable read;> >begin work; > >select * from kunde where nr < 4;> > > >After that we expect informix to lock only these selected rows. But when > >we open another connection to the database and we execute next > >statement: > > > >update kunde set name="mueller" where nr = 30> > > >we get this error messages: > > > > 243: Could not position within a table (u13210.kunde). > > 113: ISAM error: the file is locked.> > > >It seems that informix locks the whole table. But we want informix only > >to lock the selected rows. How can we solve this problem ? > > Informix will lock all rows it has to _look at_ to satisfy the query, not > just the rows that actually satisfy the query. So, for example, if this > SELECT ... FOR UPDATE does a sequential scan of the table, the whole table > will be locked. Perhaps UPDATE STATISTICS might help, or optimizer > directives. > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com > > You can also force Informix to use a index Create a index on kunde.nr IXKUNNR then do update {+Index(kunde IXKUNNR)} kunde set name="mueller" where nr = 30 This is IDS 7.xx or beter only Otherwise use a update where CURRENT Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8ggrr0$eki$1@nnrp1.deja.com>, arthur_apw@my-deja.com says... >You can also force Informix to use a index >Create a index on kunde.nr IXKUNNR >then do >update {+Index(kunde IXKUNNR)} kunde set name="mueller" where nr = 30 >This is IDS 7.xx or beter only >Otherwise use a update where CURRENT That's 7.2 or better; 7.1 didn't have that capability. -- William Harris william@carsinfo.com

If you are going to be picky directives started in 7.24 undocumented and were first documented in 7.30. Art S. Kagel William Harris wrote: > > In article <8ggrr0$eki$1@nnrp1.deja.com>, arthur_apw@my-deja.com says... > >You can also force Informix to use a index > >Create a index on kunde.nr IXKUNNR > >then do > >update {+Index(kunde IXKUNNR)} kunde set name="mueller" where nr = 30 > >This is IDS 7.xx or beter only > >Otherwise use a update where CURRENT > > That's 7.2 or better; 7.1 didn't have that capability. > -- > William Harris william@carsinfo.com