Error message 243 - need help"Could not position w
A user on IDS 7.31/HP-UX 11.11 kept hitting error -243 ("Could not position within a table") when several parallel programs updated different rows of the same large table with row-level locking. Suggestions: check the ISAM error code and the query plan (SET EXPLAIN), since the optimizer may be doing a sequential scan and hitting rows locked by other sessions — add the right index or run UPDATE STATISTICS; use SET LOCK MODE TO WAIT (already in place) or dirty read for queries; and run oncheck -cI to rule out damaged/locked indexes. The poster couldn't supply the ISAM code or schema, and no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi I am getting the error 243 during my updating to database tables. The prblem is i have some parallel programs running updating different rows of the same table. my program logic ensures that they will never update the same row. and the locking mode is row. So i am still not able to understand how come i am getting this error time and again. Please let me know if there might other reasons for this error to appear. Regards Debadatta
The SQL that is the victim of the error is not reading 1 row in the table - there is not an index for it - but probably reading all the table. Add the correct index or update statistics. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DD MISHRA Sent: Tuesday, 6 June 2006 3:18 p.m. To: ids@iiug.org Subject: Error message 243 - need help"Could not positi.... [6880] Hi I am getting the error 243 during my updating to database tables. The prblem is i have some parallel programs running updating different rows of the same table. my program logic ensures that they will never update the same row. and the locking mode is row. So i am still not able to understand how come i am getting this error time and again. Please let me know if there might other reasons for this error to appear. Regards Debadatta **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Thanks for your reply but i am reading the table through a part of primary key. will it be still reading the whole table for it. Regards On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote: > > > The SQL that is the victim of the error is not reading 1 row in the table > - > there is not an index for it - but probably reading all the table. Add the > correct index or update statistics. > > MW > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DD > MISHRA > Sent: Tuesday, 6 June 2006 3:18 p.m. > To: ids@iiug.org > Subject: Error message 243 - need help"Could not positi.... [6880] > > Hi > > I am getting the error 243 during my updating to database tables. > The prblem is i have some parallel programs running updating different > rows > of the same table. > my program logic ensures that they will never update the same row. and the > locking mode is row. > > So i am still not able to understand how come i am getting this error time > and again. > > Please let me know if there might other reasons for this error to appear. > > Regards > Debadatta > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
OK. Do an explain on the SQL (set explain on) to confirm that it is reading by the primary key. Which version of Informix and which OS? What is the ISAM error code - does it point to anything different? MW _____ From: DD MISHRA [mailto:mishra.dd@gmail.com] Sent: Tuesday, 6 June 2006 3:48 p.m. To: ids@iiug.org; ifxmaillist@quanta.co.nz Subject: Re: Error message 243 - need help"Could not po.... [6881] Hi Thanks for your reply but i am reading the table through a part of primary key. will it be still reading the whole table for it. Regards On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote: The SQL that is the victim of the error is not reading 1 row in the table - there is not an index for it - but probably reading all the table. Add the correct index or update statistics. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org <mailto:ids-bounces@iiug.org> ] On Behalf Of DD MISHRA Sent: Tuesday, 6 June 2006 3:18 p.m. To: ids@iiug.org Subject: Error message 243 - need help"Could not positi.... [6880] Hi I am getting the error 243 during my updating to database tables. The prblem is i have some parallel programs running updating different rows of the same table. my program logic ensures that they will never update the same row. and the locking mode is row. So i am still not able to understand how come i am getting this error time and again. Please let me know if there might other reasons for this error to appear. Regards Debadatta **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks informix version is 7.31 and HPUX11.11 i am actually trying to get the ISAM code . any information would be helpful On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote: > > > OK. Do an explain on the SQL (set explain on) to confirm that it is > reading > by the primary key. > > Which version of Informix and which OS? > > What is the ISAM error code - does it point to anything different? > > MW > > _____ > > From: DD MISHRA [mailto:mishra.dd@gmail.com] > Sent: Tuesday, 6 June 2006 3:48 p.m. > To: ids@iiug.org; ifxmaillist@quanta.co.nz > Subject: Re: Error message 243 - need help"Could not po.... [6881] > > Hi > > Thanks for your reply > > but i am reading the table through a part of primary key. > > will it be still reading the whole table for it. > > Regards > > On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote: > > The SQL that is the victim of the error is not reading 1 row in the table > - > there is not an index for it - but probably reading all the table. Add the > correct index or update statistics. > > MW > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org > <mailto:ids-bounces@iiug.org> ] On Behalf Of DD > MISHRA > Sent: Tuesday, 6 June 2006 3:18 p.m. > To: ids@iiug.org > Subject: Error message 243 - need help"Could not positi.... [6880] > > Hi > > I am getting the error 243 during my updating to database tables. > The prblem is i have some parallel programs running updating different > rows > of the same table. > my program logic ensures that they will never update the same row. and the > locking mode is row. > > So i am still not able to understand how come i am getting this error time > and again. > > Please let me know if there might other reasons for this error to appear. > > Regards > Debadatta > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Hi,
My understanding of this error is that the index is locked or damaged. Use
oncheck -cI to see if all the indices are stable.Our mechanism to prevent this is as follows:
If the error occurs while reading data, you can easily prevent it by using
"set isolation to dirty read".
For inserts and updates, use "set lock mode to wait".
This will give you the best performance for reading (but keep in mind that
you will read uncommitted data) and no such errors on writing (but you might
have lock waits, depending on your transaction length).
All this might not be sufficient in case any index is damaged, so check this
first. There will probably an error in your online.log.
Marcus
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Murray
Wood....
Sent: Tuesday, June 06, 2006 5:51 AM
To: ids@iiug.org
Subject: RE: Error message 243 - need help"Could not po.... [6883]
OK. Do an explain on the SQL (set explain on) to confirm that it is reading
by the primary key.
Which version of Informix and which OS?
What is the ISAM error code - does it point to anything different?
MW
_____
From: DD MISHRA [mailto:mishra.dd@gmail.com]
Sent: Tuesday, 6 June 2006 3:48 p.m.
To: ids@iiug.org; ifxmaillist@quanta.co.nz
Subject: Re: Error message 243 - need help"Could not po.... [6881]
Hi
Thanks for your reply
but i am reading the table through a part of primary key.
will it be still reading the whole table for it.
Regards
On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote:
The SQL that is the victim of the error is not reading 1 row in the table -
there is not an index for it - but probably reading all the table. Add the
correct index or update statistics.
MW
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org
<mailto:ids-bounces@iiug.org> ] On Behalf Of DD MISHRA
Sent: Tuesday, 6 June 2006 3:18 p.m.
To: ids@iiug.org
Subject: Error message 243 - need help"Could not positi.... [6880]
Hi
I am getting the error 243 during my updating to database tables.
The prblem is i have some parallel programs running updating different rows
of the same table.
my program logic ensures that they will never update the same row. and the
locking mode is row.
So i am still not able to understand how come i am getting this error time
and again.
Please let me know if there might other reasons for this error to appear.
Regards
Debadatta
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Have you set Lock mode ?
You could use Lock mode to wait <Seconds> to avoid contention in those
cases.
No, is not going to wait 10 seconds after the it fails the first time,
that is the max time it will be retrying before giving up and return
that error.
Hospitably yours,
Walter Milan
DBA
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Marcus Haarmann
Sent: Tuesday, June 06, 2006 2:03 AM
To: ids@iiug.org
Subject: RE: Error message 243 - need help"Could not po.... [6886]
Hi,
My understanding of this error is that the index is locked or damaged.
Use
oncheck -cI to see if all the indices are stable.Our mechanism to prevent this is as follows:
If the error occurs while reading data, you can easily prevent it by
using
"set isolation to dirty read".
For inserts and updates, use "set lock mode to wait".
This will give you the best performance for reading (but keep in mind
that
you will read uncommitted data) and no such errors on writing (but you
might
have lock waits, depending on your transaction length).
All this might not be sufficient in case any index is damaged, so check
this
first. There will probably an error in your online.log.
Marcus
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Murray
Wood....
Sent: Tuesday, June 06, 2006 5:51 AM
To: ids@iiug.org
Subject: RE: Error message 243 - need help"Could not po.... [6883]
OK. Do an explain on the SQL (set explain on) to confirm that it is
reading
by the primary key.
Which version of Informix and which OS?
What is the ISAM error code - does it point to anything different?
MW
_____
From: DD MISHRA [mailto:mishra.dd@gmail.com]
Sent: Tuesday, 6 June 2006 3:48 p.m.
To: ids@iiug.org; ifxmaillist@quanta.co.nz
Subject: Re: Error message 243 - need help"Could not po.... [6881]
Hi
Thanks for your reply
but i am reading the table through a part of primary key.
will it be still reading the whole table for it.
Regards
On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote:
The SQL that is the victim of the error is not reading 1 row in the
table -
there is not an index for it - but probably reading all the table. Add
the
correct index or update statistics.
MW
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org
<mailto:ids-bounces@iiug.org> ] On Behalf Of DD MISHRA
Sent: Tuesday, 6 June 2006 3:18 p.m.
To: ids@iiug.org
Subject: Error message 243 - need help"Could not positi.... [6880]
Hi
I am getting the error 243 during my updating to database tables.
The prblem is i have some parallel programs running updating different
rows
of the same table.
my program logic ensures that they will never update the same row. and
the
locking mode is row.
So i am still not able to understand how come i am getting this error
time
and again.
Please let me know if there might other reasons for this error to
appear.
Regards
Debadatta
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
You are updating sequentially on a table. Someone has a lock on a row you are
about to update. This would/could cause this message.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
Walter Milan
Sent: Tuesday, June 06, 2006 11:04 AM
To: ids@iiug.org
Subject: RE: Error message 243 - need help"Could not po.... [6893]
Have you set Lock mode ?
You could use Lock mode to wait <Seconds> to avoid contention in those
cases.
No, is not going to wait 10 seconds after the it fails the first time,
that is the max time it will be retrying before giving up and return
that error.
Hospitably yours,
Walter Milan
DBA
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Marcus Haarmann
Sent: Tuesday, June 06, 2006 2:03 AM
To: ids@iiug.org
Subject: RE: Error message 243 - need help"Could not po.... [6886]
Hi,
My understanding of this error is that the index is locked or damaged.
Use
oncheck -cI to see if all the indices are stable.Our mechanism to prevent this is as follows:
If the error occurs while reading data, you can easily prevent it by
using
"set isolation to dirty read".
For inserts and updates, use "set lock mode to wait".
This will give you the best performance for reading (but keep in mind
that
you will read uncommitted data) and no such errors on writing (but you
might
have lock waits, depending on your transaction length).
All this might not be sufficient in case any index is damaged, so check
this
first. There will probably an error in your online.log.
Marcus
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Murray
Wood....
Sent: Tuesday, June 06, 2006 5:51 AM
To: ids@iiug.org
Subject: RE: Error message 243 - need help"Could not po.... [6883]
OK. Do an explain on the SQL (set explain on) to confirm that it is
reading
by the primary key.
Which version of Informix and which OS?
What is the ISAM error code - does it point to anything different?
MW
_____
From: DD MISHRA [mailto:mishra.dd@gmail.com]
Sent: Tuesday, 6 June 2006 3:48 p.m.
To: ids@iiug.org; ifxmaillist@quanta.co.nz
Subject: Re: Error message 243 - need help"Could not po.... [6881]
Hi
Thanks for your reply
but i am reading the table through a part of primary key.
will it be still reading the whole table for it.
Regards
On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote:
The SQL that is the victim of the error is not reading 1 row in the
table -
there is not an index for it - but probably reading all the table. Add
the
correct index or update statistics.
MW
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org
<mailto:ids-bounces@iiug.org> ] On Behalf Of DD MISHRA
Sent: Tuesday, 6 June 2006 3:18 p.m.
To: ids@iiug.org
Subject: Error message 243 - need help"Could not positi.... [6880]
Hi
I am getting the error 243 during my updating to database tables.
The prblem is i have some parallel programs running updating different
rows
of the same table.
my program logic ensures that they will never update the same row. and
the
locking mode is row.
So i am still not able to understand how come i am getting this error
time
and again.
Please let me know if there might other reasons for this error to
appear.
Regards
Debadatta
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi
Thanks for your reply.
the actual SQL is fetch for update.
Total number of records in the table would around 2-3million as this is
production data. I am not exactly in contact with the server or data.
another problem is our application doesnot store ISAM error code any where.
I have no idea about informix log though.
So it is very difficult to get the exact picture. it does not come in all
the places. very specific some places and not even consistent as you might
be knowing seeing the nature of the problem.
the table is table with around 100 columns
first two fields
key_1 - integer
key_2 - integer
what we do in program is call multiple parallel programs for a set of
key_1(examp 1- 100 by prg1 and 101- 200 by prg2) which will update those set
of records.
this problem comes in the above place where we use fetch for update cursor.
about set lock wait and row level locking both are already there.
let me know if any other thing we are missing, we should have taken care of.
regards
Debadatta
Hi
On 6/7/06, dharmendra sharma <dharmendrasharma@hotmail.com> wrote:
>
> DD, please supply following to me to determine what's going on...
>
> SELECT statement>
> UPDATE statement
>
> LIST OF all INDEXES exist on the table that involves in the issue
>
> TOTAL NUMBER OF RECORDS EXIST in the table.
>
> TOTAL NUMBER OF RECORDS HIT by the above SELECT..
>
>
>
> The reason I am asking is, sometime eventhough you have index/primary key,
> optimizer automatically chooses "sequential" path instead of "Index" path..
> The optimizer's choices
>
> based on all the above mentioned points and there are more...I can
> understand that you may not wish to share actual table schema, but without
> these information, it would be difficult to
>
> determine what's going on...
>
>
>
>
>
> ------------------------------
> From: *"DD MISHRA" <mishra.dd@gmail.com>*
> Reply-To: *ids@iiug.org*
> To: *ids@iiug.org*
> Subject: *Re: Error message 243 - need help"Could not po.... [6882]*
> Date: *Mon, 5 Jun 2006 23:44:33 -0400 (EDT)*
> Received: *from perform.iiug.org ([216.177.38.211]) by
> bay0-mc2-f12.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Mon,
> 5 Jun 2006 20:48:24 -0700*
> Received: *by perform.iiug.org (Postfix, from userid 60001)id E793AA13D;
> Mon, 5 Jun 2006 23:44:46 -0400 (EDT)*
> Received: *from perform.iiug.org (localhost [127.0.0.1])by
> perform.iiug.org (Postfix) with ESMTP id 268BFA0EF;Mon, 5 Jun 2006
> 23:44:41 -0400 (EDT)*
> Received: *by perform.iiug.org (Postfix, from userid 60001)id 3F12AA0EC;
> Mon, 5 Jun 2006 23:44:33 -0400 (EDT)*
>
>
> >Hi
> >Thanks for your reply
> >but i am reading the table through a part of primary key.
> >will it be still reading the whole table for it.
> >
> >Regards
> >
> >On 6/6/06, Murray Wood.... <ifxmaillist@quanta.co.nz> wrote:
> > >
> > >
> > > The SQL that is the victim of the error is not reading 1 row in the
> table
> > > -
> > > there is not an index for it - but probably reading all the table. Add
> the
> > > correct index or update statistics.
> > >
> > > MW
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> DD
> > > MISHRA
> > > Sent: Tuesday, 6 June 2006 3:18 p.m.
> > > To: ids@iiug.org
> > > Subject: Error message 243 - need help"Could not positi.... [6880]
> > >
> > > Hi
> > >
> > > I am getting the error 243 during my updating to database tables.
> > > The prblem is i have some parallel programs running updating different
> > > rows
> > > of the same table.
> > > my program logic ensures that they will never update the same row. and
> the
> > > locking mode is row.
> > >
> > > So i am still not able to understand how come i am getting this error
> time
> > > and again.
> > >
> > > Please let me know if there might other reasons for this error to
> appear.
> > >
> > > Regards
> > > Debadatta
> > >
> > >
> > >
> ****************************************************************************
> > > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
>
>
>*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> >
>
>
>*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ------------------------------
> Who will win Bollywood's most coveted IIFA awards? You decide! Cast your
> vote! <http://g.msn.com/8HMAENIN/2734??PS=47575>;
>