Re: problems with repeatable read - whole table is locked
Instead of restarting the engine, for testing purpose you can make use of
OPTCOMPIND environment variable. Hence you can set it differently 0 or 2 for
different queries
Vinod
>From: Heiko Giesselmann <heiko.giesselmann@informix.com>
>Reply-To: Heiko Giesselmann <heiko.giesselmann@informix.com>
>To: informix-list@iiug.org
>Subject: Re: problems with repeatable read - whole table is locked
>Date: Thu, 25 May 2000 14:36:27 -0700
>
>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
>
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com