Error -244
A user on IDS 11.1/Linux got intermittent -244/-107 "record is locked" errors during sequential scans. Replies diagnosed it as a locking/concurrency issue and suggested: set an appropriate isolation level (e.g. Last Committed), use lock mode row instead of page, check indexes/optimizer behaviour, and especially issue SET LOCK MODE TO WAIT 5 so transient locks don't cause immediate failure. The poster traced the cause to a Java/Hibernate batch job on Windows whose killed sessions left connections and locks behind; advice was to kill stale sessions with onmode -z and use the documented idle-user timeout technique (IDS team blog). He accepted both suggestions.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
Running IDS 11.1 / Linux...
I keep getting:
SQL Error: -244, SQLState: IX000
Could not do a physical-order read to fetch next row.
SQL Error: -107, SQLState: IX000
ISAM error: record is locked.
It seems to be coming randomly... most things seem to be working fine, but
from time to time, it dumps this error...
I have updated statistics quite a few time...
Thanks,
jgp
It seems a concurrency/locking problem.
Without see the code its difficult but it looks that you are trying to
access to a row that is locked.
Jean Georges Perrin escreveu:
> Hi,
>
> Running IDS 11.1 / Linux...
>
> I keep getting:
>
> SQL Error: -244, SQLState: IX000
> Could not do a physical-order read to fetch next row.
> SQL Error: -107, SQLState: IX000
> ISAM error: record is locked.>
> It seems to be coming randomly... most things seem to be working fine, but
> from time to time, it dumps this error...
>
> I have updated statistics quite a few time...
>
> Thanks,
>
> jgp
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi,
a) check the isolation level used (by the statements or even better
the whole application). "Last Committed" could be what you
want/need. Generally it should improve locking behaviour.
b) Check the lock mode of tables. "Lock mode row" usually gives
finer granularity than the default "lock mode page" and thus less
locking issues.
c) Check whether it is a spcific table causing the issues.
If so, use sqexplain output to check which index(es) are used.
[ There was an APAR IC56446 recently:
QUERY OPTIMIZER SHOULD PREFER UNIQUE INDEXES OVER
NON-UNIQUE INDEXES
which meanwhile got fixed in IDS-11.10.xC2W3. Though I think it was
hitting only with update stats low or without update stats.]
Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management
IBM Deutschland Research & Development GmbH
Chairman of the Supervisory Board: Martin Jetter
Board of Management: Herbert Kircher
Corporate Seat: Boeblingen, Germany
Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294
"Jean Georges Perrin" <jgp@jgp.net>
Sent by: ids-bounces@iiug.org
17.07.2008 10:52
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Error -244 [12732]
Hi,
Running IDS 11.1 / Linux...
I keep getting:
SQL Error: -244, SQLState: IX000
Could not do a physical-order read to fetch next row.
SQL Error: -107, SQLState: IX000
ISAM error: record is locked.
It seems to be coming randomly... most things seem to be working fine, but
from time to time, it dumps this error...
I have updated statistics quite a few time...
Thanks,
jgp
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I think I have isolated my problem...
I have an app running on Windows Server 2003, Informix is on a linux box...
The Windows app is a Java + Hibernate batch-kind app, triggered by the
Windows scheduler... the windows scheduler is starting the batch every 24hr,
if the application gets nuts, the windows scheduler should kill it before it
starts another instance of the app... for some reason, it seems that either
the killing of the app does not work or is not very clean, as after a few
days, we have plenty of sessions on the engine...
most of the sessions are stalled / dead, but the locks remain...
is there a way to set a timeout on the sessions?
I am running 11.10fc1...
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sérgio
Ferreira
Sent: Thursday, July 17, 2008 11:06
To: ids@iiug.org
Subject: Re: Error -244 [12733]
It seems a concurrency/locking problem.
Without see the code its difficult but it looks that you are trying to
access to a row that is locked.
Jean Georges Perrin escreveu:
> Hi,
>
> Running IDS 11.1 / Linux...
>
> I keep getting:
>
> SQL Error: -244, SQLState: IX000
> Could not do a physical-order read to fetch next row.
> SQL Error: -107, SQLState: IX000
> ISAM error: record is locked.>
> It seems to be coming randomly... most things seem to be working fine, but
> from time to time, it dumps this error...
>
> I have updated statistics quite a few time...
>
> Thanks,
>
> jgp
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
This question comes up from time to time
Last one On June 9th John Miller posted this address which could help you out.
http://www.ibm.com/developerworks/blogs/page/idsteam?entry=terminate_idle_users_
with_the
Walter Milan
DBA
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jean
Georges Perrin
Sent: Thursday, July 17, 2008 6:20 AM
To: ids@iiug.org
Subject: RE: Error -244 [12738]
I think I have isolated my problem...
I have an app running on Windows Server 2003, Informix is on a linux box...
The Windows app is a Java + Hibernate batch-kind app, triggered by the
Windows scheduler... the windows scheduler is starting the batch every 24hr,
if the application gets nuts, the windows scheduler should kill it before it
starts another instance of the app... for some reason, it seems that either
the killing of the app does not work or is not very clean, as after a few
days, we have plenty of sessions on the engine...
most of the sessions are stalled / dead, but the locks remain...
is there a way to set a timeout on the sessions?
I am running 11.10fc1...
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sérgio
Ferreira
Sent: Thursday, July 17, 2008 11:06
To: ids@iiug.org
Subject: Re: Error -244 [12733]
It seems a concurrency/locking problem.
Without see the code its difficult but it looks that you are trying to
access to a row that is locked.
Jean Georges Perrin escreveu:
> Hi,
>
> Running IDS 11.1 / Linux...
>
> I keep getting:
>
> SQL Error: -244, SQLState: IX000
> Could not do a physical-order read to fetch next row.
> SQL Error: -107, SQLState: IX000
> ISAM error: record is locked.>
> It seems to be coming randomly... most things seem to be working fine, but
> from time to time, it dumps this error...
>
> I have updated statistics quite a few time...
>
> Thanks,
>
> jgp
>
>
>
****************************************************************************
***
> 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.
Your query is executing a sequential scan and encountering a row that
someone else is looking at and has a momentary lock on. You do note have
your locking mode set to wait for transient locks to clear, so it is
erroring out. Just do: SET LOCK MODE TO WAIT 5; in your session shortly
after connecting and this should be good - UNLESS your applications are
using pessimistic locking protocols and someone is holding locks for long
periods. In that case redesign your applications to use optimistic locking
protocols.
Art
On Thu, Jul 17, 2008 at 4:52 AM, Jean Georges Perrin <jgp@jgp.net> wrote:
> Hi,
>
> Running IDS 11.1 / Linux...
>
> I keep getting:
>
> SQL Error: -244, SQLState: IX000
> Could not do a physical-order read to fetch next row.
> SQL Error: -107, SQLState: IX000
> ISAM error: record is locked.>
> It seems to be coming randomly... most things seem to be working fine, but
> from time to time, it dumps this error...
>
> I have updated statistics quite a few time...
>
> Thanks,
>
> jgp
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
On the server: onmode -z <sessid>
Windows is very BAD about releasing network connections that are not
properly shutdown before the application exits. In the IDS side, if the
engine is waiting for the client to make a new request (even a FETCH) it
will wait forever and never find out that the client is gone. Eventually,
if the client disconnected or shutdown/rebooted, the connection will time
out but that can take a VERY long time on some OSes and won't happen at all
if the client machine is still keeping the connection alive.
Art
On Thu, Jul 17, 2008 at 7:19 AM, Jean Georges Perrin <jgp@jgp.net> wrote:
> I think I have isolated my problem...
>
> I have an app running on Windows Server 2003, Informix is on a linux box...
>
> The Windows app is a Java + Hibernate batch-kind app, triggered by the
> Windows scheduler... the windows scheduler is starting the batch every
> 24hr,
> if the application gets nuts, the windows scheduler should kill it before
> it
> starts another instance of the app... for some reason, it seems that either
> the killing of the app does not work or is not very clean, as after a few
> days, we have plenty of sessions on the engine...
>
> most of the sessions are stalled / dead, but the locks remain...
>
> is there a way to set a timeout on the sessions?
>
> I am running 11.10fc1...
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Sérgio
> Ferreira
> Sent: Thursday, July 17, 2008 11:06
> To: ids@iiug.org
> Subject: Re: Error -244 [12733]
>
> It seems a concurrency/locking problem.
> Without see the code its difficult but it looks that you are trying to
> access to a row that is locked.
>
> Jean Georges Perrin escreveu:
> > Hi,
> >
> > Running IDS 11.1 / Linux...
> >
> > I keep getting:
> >
> > SQL Error: -244, SQLState: IX000
> > Could not do a physical-order read to fetch next row.
> > SQL Error: -107, SQLState: IX000
> > ISAM error: record is locked.> >
> > It seems to be coming randomly... most things seem to be working fine,
> but
>
> > from time to time, it dumps this error...
> >
> > I have updated statistics quite a few time...
> >
> > Thanks,
> >
> > jgp
> >
> >
> >
>
> ****************************************************************************
> ***
> > 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.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
excellent! thanks!
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walter
Milan
Sent: Thursday, July 17, 2008 16:27
To: ids@iiug.org
Subject: RE: Error -244 [12741]
This question comes up from time to time
Last one On June 9th John Miller posted this address which could help you
out.
http://www.ibm.com/developerworks/blogs/page/idsteam?entry=terminate_idle_us
ers_with_the
Walter Milan
DBA
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jean
Georges Perrin
Sent: Thursday, July 17, 2008 6:20 AM
To: ids@iiug.org
Subject: RE: Error -244 [12738]
I think I have isolated my problem...
I have an app running on Windows Server 2003, Informix is on a linux box...
The Windows app is a Java + Hibernate batch-kind app, triggered by the
Windows scheduler... the windows scheduler is starting the batch every 24hr,
if the application gets nuts, the windows scheduler should kill it before it
starts another instance of the app... for some reason, it seems that either
the killing of the app does not work or is not very clean, as after a few
days, we have plenty of sessions on the engine...
most of the sessions are stalled / dead, but the locks remain...
is there a way to set a timeout on the sessions?
I am running 11.10fc1...
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sérgio
Ferreira
Sent: Thursday, July 17, 2008 11:06
To: ids@iiug.org
Subject: Re: Error -244 [12733]
It seems a concurrency/locking problem.
Without see the code its difficult but it looks that you are trying to
access to a row that is locked.
Jean Georges Perrin escreveu:
> Hi,
>
> Running IDS 11.1 / Linux...
>
> I keep getting:
>
> SQL Error: -244, SQLState: IX000
> Could not do a physical-order read to fetch next row.
> SQL Error: -107, SQLState: IX000
> ISAM error: record is locked.>
> It seems to be coming randomly... most things seem to be working fine, but
> from time to time, it dumps this error...
>
> I have updated statistics quite a few time...
>
> Thanks,
>
> jgp
>
>
>
****************************************************************************
***
> 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.
Thanks Art, this will be very useful too...
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Thursday, July 17, 2008 17:01
To: ids@iiug.org
Subject: Re: Error -244 [12742]
Your query is executing a sequential scan and encountering a row that
someone else is looking at and has a momentary lock on. You do note have
your locking mode set to wait for transient locks to clear, so it is
erroring out. Just do: SET LOCK MODE TO WAIT 5; in your session shortly
after connecting and this should be good - UNLESS your applications are
using pessimistic locking protocols and someone is holding locks for long
periods. In that case redesign your applications to use optimistic locking
protocols.
Art
On Thu, Jul 17, 2008 at 4:52 AM, Jean Georges Perrin <jgp@jgp.net> wrote:
> Hi,
>
> Running IDS 11.1 / Linux...
>
> I keep getting:
>
> SQL Error: -244, SQLState: IX000
> Could not do a physical-order read to fetch next row.
> SQL Error: -107, SQLState: IX000
> ISAM error: record is locked.>
> It seems to be coming randomly... most things seem to be working fine, but
> from time to time, it dumps this error...
>
> I have updated statistics quite a few time...
>
> Thanks,
>
> jgp
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.