Re: Let me be the first

Lukas Barton wrote: > I mean I'm facing the problem described on: > > http://www-1.ibm.com/support/docview.wss?rs=0&context=SSGU5D&context=SSHMMC&context=SSGU8G&context=SSGKNY&context=SSGU5Y&context=SSCRW7&context=SSGHZP&context=SSVT2J&q1=ISAM+error%3a+record+is+locked.&uid=swg21152738&loc=en_US&cs=utf-8&lang= > > 244: Could not do a physical-order read to fetch next row, 107: ISAM> error: record is lock > > The number of rows in the table is small enough to cause the database > server to perform a sequential scan, and an index scan on a table with > lock mode configured to row. This may also be because you do not have up-to-date statistics and distributions for the optimizer to use to identify that an indexed retrieval would be best. It is also an artifact of a very small artificial data set. Try updating statistics according to the protocol described in the Performance Guide or get and use my dostats utility which automates the protocols. Art S. Kagel >> Mark Jamison wrote: >> >>> Hi Lukas, >>> >>> Are you saying that you have 1 composite index on col1, and col2 ? >>> >>> Or are you saying that you have 1 index on col1 and 1 index on col2? >> >> I have two indexes. >> One on col1 and the other on col2. >> >>> >>> >>> For that matter, currently how large is each of your indices? >> >> There are only two values for each index. >> >> See my test SQL (table1 = dotaz, col1=status, col2=iddavky, all >> indexed columns == foreing key): >> >> begin work; >> set isolation to committed read; >> update dotaz set status=10 where iddavky=11000958;>> commit work; >> >> >> begin work; >> set isolation to committed read; >> select * from dotaz where iddavky=11000969; -- waits until the>> first thread commits >> commit work; >> >> >> >> >> >> CREATE TABLE dotaz >> ( >> id decimal(10) PRIMARY KEY, >> nazev varchar(50), >> poradi decimal(10), >> koddotazu varchar(20), >> idsablona decimal(10), >> iddavky decimal(10), >> metatypchyby_id decimal(10), >> status decimal(10), >> errorcode decimal(3), >> importch_id decimal(10) >> );>> >> >> >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metatypchyby_id,status,errorcode,importch_id) >> VALUES (32979,'DOTAZ_0',null,'0',2531,11000958,1,10,0,null); >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metatypchyby_id,status,errorcode,importch_id) >> VALUES (32980,'DOTAZ_1',null,'1',2532,11000958,1,10,0,null);>> >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metatypchyby_id,status,errorcode,importch_id) >> VALUES (33026,'DOTAZ_23',null,'23',2532,11000969,1,110,0,null); >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metatypchyby_id,status,errorcode,importch_id) >> VALUES (33027,'DOTAZ_24',null,'24',2531,11000969,1,110,0,null);>> >> >> CREATE INDEX 109_96 ON dotaz(status); >> CREATE INDEX 109_95 ON dotaz(idsablona); >> CREATE INDEX 109_94 ON dotaz(iddavky); >> CREATE INDEX 109_93 ON dotaz(importch_id); >> CREATE INDEX 109_92 ON dotaz(metatypchyby_id);>> >>------------------------------------------------------------------------ >> >>_______________________________________________ >>Informix-list mailing list >>Informix-list@iiug.org >>http://www.iiug.org/mailman/listinfo/informix-list >> >> >