problems with repeatable read - whole table is locked

Hello, 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 ? Thanks for your help harald Sent via Deja.com http://www.deja.com/ Before you buy.

harald_wehr@my-deja.com wrote: > Hello, > > 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 ? > > Thanks for your help > > harald > > Sent via Deja.com http://www.deja.com/ > Before you buy. Hi, I guess this is a test database with a small number of rows. With OPTCOMPIND in $ONCONFIG set to 2 (default) the optimizer may decide to scan all rows for the update instead of using the index. Try executing the same query with OPTCOMPIND set to 0 (the engine will have to be restarted to test this). And, as others suggested versions 7.3 and later support optimizer directives which can be used to direct optimizer operations for individual statements. Hope this helps, Heiko