Re: Let me be the first
This is a discussion on Re: Let me be the first within the Informix forums, part of the Database Server Software category; --> I mean I'm facing the problem described on: http://www-1.ibm.com/support/docview... =utf-8&lang= 244: Could not do a physical-order read to fetch ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-20-2008, 01:07 PM
| ||||
| ||||
 Re: Let me be the first I mean I'm facing the problem described on: http://www-1.ibm.com/support/docview... =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. > 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,metat ypchyby_id,status,errorcode,importch_id) > VALUES (32979,'DOTAZ_0',null,'0',2531,11000958,1,10,0,nul l); > INSERT INTO dotaz > (id,nazev,poradi,koddotazu,idsablona,iddavky,metat ypchyby_id,status,errorcode,importch_id) > VALUES (32980,'DOTAZ_1',null,'1',2532,11000958,1,10,0,nul l); > > INSERT INTO dotaz > (id,nazev,poradi,koddotazu,idsablona,iddavky,metat ypchyby_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,metat ypchyby_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 >      |
|
04-20-2008, 01:07 PM
| |||
| |||
| 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... =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. Having gone through your test case with IDS 10.00.UC5 I agree you probably are since you can only select the rows without encountering a lock if OPTCOMPIND is set to 0 and the locking mode is row. So why not set OPTCOMPIND to 0 in your ONCONFIG and solve the problem? If you're in an OLTP environment it's highly desirable anyway and in a mixed environment it probably won't hurt. It's also highly desirable to set LOCK WAIT to something other than zero. Ben. |
|
04-20-2008, 01:14 PM
| |||
| |||
| 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... =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,metat ypchyby_id,status,errorcode,importch_id) >> VALUES (32979,'DOTAZ_0',null,'0',2531,11000958,1,10,0,nul l); >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metat ypchyby_id,status,errorcode,importch_id) >> VALUES (32980,'DOTAZ_1',null,'1',2532,11000958,1,10,0,nul l); >> >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metat ypchyby_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,metat ypchyby_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 >> >> > |
|
04-20-2008, 01:14 PM
| ||||
| ||||
| 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... =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,metat ypchyby_id,status,errorcode,importch_id) >> VALUES (32979,'DOTAZ_0',null,'0',2531,11000958,1,10,0,nul l); >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metat ypchyby_id,status,errorcode,importch_id) >> VALUES (32980,'DOTAZ_1',null,'1',2532,11000958,1,10,0,nul l); >> >> INSERT INTO dotaz >> (id,nazev,poradi,koddotazu,idsablona,iddavky,metat ypchyby_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,metat ypchyby_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 >> >> > |
Linear Mode
