To read only committed rows from a table
A 4GL app reading a table hit error -245/-144 (lock conflict) because another application held locks for 1-2 minutes during inserts. Setting COMMITTED READ with LOCK MODE NOT WAIT still errors by design. Replies recommended "SET ISOLATION TO COMMITTED READ LAST COMMITTED" (plus row-level locking on the table), but that requires version 11+ and the poster was on IDS 9.4. For 9.4 the suggestions were to shorten the locking transaction, filter on an indexed timestamp, use WAIT n, or tolerate/retry on the error. A side question noted lock mode can't be set in onconfig but can be via a public sysdbopen() procedure.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi I am trying a read committed rows from a table. I have one application which inserts records into a table(The application takes as long as 1 to 2 mins to commit the transaction ) I am developing another application using informix 4gl, to read any record which has been committed to this table. While reading the record from the table, if there is any record which is in a transaction I get the error -245/-144(ISAM) while fetching data. I tried setting the isolation level to committed read and setting the lock mode to not wait, but I got the same error. Please can anyone let me know, if there is a way to read records without getting any error without doing the dirty read. Thanks and Regards, Naveen
I would try the "set isolation committed read last committed" for this
select statement.
Also how long did you set the application to wait? Based on what you said
below it would have to wait at least 2 minutes.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic51775.gif)
ids-bounces@iiug.org wrote on 01/03/2012 09:25:12 AM:
> From: "NAVEEN NAIK" <navnaik@gmail.com>
> To: ids@iiug.org
> Date: 01/03/2012 09:26 AM
> Subject: To read only committed rows from a table [25799]
> Sent by: ids-bounces@iiug.org
>
> Hi
>
> I am trying a read committed rows from a table.
>
> I have one application which inserts records into a table(The application
> takes as long as 1 to 2 mins to commit the transaction )
>
> I am developing another application using informix 4gl, to read any
record
> which has been committed to this table. While reading the record from the
> table, if there is any record which is in a transaction I get the error
> -245/-144(ISAM) while fetching data.
>
> I tried setting the isolation level to committed read and setting the
lock
> mode to not wait, but I got the same error.
>
> Please can anyone let me know, if there is a way to read records without
> getting any error without doing the dirty read.
>
> Thanks and Regards,
> Naveen
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I believe the OP wrote that it set it to NOT WAIT. So the behavior is
expectable and the solution is the COMMITTED READ LAST COMMITTED READ.
Note that this only appeared on version 11.
Depending on your data, sometimes other solutions can be used like using
dirty read but setting conditions on timestamp data (is this is present)
Regards.
On Tue, Jan 3, 2012 at 5:34 PM, John Miller iii <miller3@us.ibm.com> wrote:
> I would try the "set isolation committed read last committed" for this
> select statement.>
> Also how long did you set the application to wait? Based on what you said
> below it would have to wait at least 2 minutes.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
> (Embedded image moved to file: pic51775.gif)
>
> ids-bounces@iiug.org wrote on 01/03/2012 09:25:12 AM:
>
> > From: "NAVEEN NAIK" <navnaik@gmail.com>
> > To: ids@iiug.org
> > Date: 01/03/2012 09:26 AM
> > Subject: To read only committed rows from a table [25799]
> > Sent by: ids-bounces@iiug.org
> >
> > Hi
> >
> > I am trying a read committed rows from a table.
> >
> > I have one application which inserts records into a table(The application
>
> > takes as long as 1 to 2 mins to commit the transaction )
> >
> > I am developing another application using informix 4gl, to read any
> record
> > which has been committed to this table. While reading the record from the
>
> > table, if there is any record which is in a transaction I get the error
> > -245/-144(ISAM) while fetching data.
> >
> > I tried setting the isolation level to committed read and setting the
> lock
> > mode to not wait, but I got the same error.
> >
> > Please can anyone let me know, if there is a way to read records without
> > getting any error without doing the dirty read.
> >
> > Thanks and Regards,
> > Naveen
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--002354470f445969d204b5a4059a
Also do not forget set lock mode row even with LAST COMMITTED READ?
Frank
On Tue, Jan 3, 2012 at 1:39 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> I believe the OP wrote that it set it to NOT WAIT. So the behavior is
> expectable and the solution is the COMMITTED READ LAST COMMITTED READ.
> Note that this only appeared on version 11.
>
> Depending on your data, sometimes other solutions can be used like using
> dirty read but setting conditions on timestamp data (is this is present)
>
> Regards.
>
> On Tue, Jan 3, 2012 at 5:34 PM, John Miller iii <miller3@us.ibm.com>
> wrote:
>
> > I would try the "set isolation committed read last committed" for this
> > select statement.> >
> > Also how long did you set the application to wait? Based on what you said
> > below it would have to wait at least 2 minutes.
> >
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> > (Embedded image moved to file: pic51775.gif)
> >
> > ids-bounces@iiug.org wrote on 01/03/2012 09:25:12 AM:
> >
> > > From: "NAVEEN NAIK" <navnaik@gmail.com>
> > > To: ids@iiug.org
> > > Date: 01/03/2012 09:26 AM
> > > Subject: To read only committed rows from a table [25799]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > Hi
> > >
> > > I am trying a read committed rows from a table.
> > >
> > > I have one application which inserts records into a table(The
> application
> >
> > > takes as long as 1 to 2 mins to commit the transaction )
> > >
> > > I am developing another application using informix 4gl, to read any
> > record
> > > which has been committed to this table. While reading the record from
> the
> >
> > > table, if there is any record which is in a transaction I get the error
> > > -245/-144(ISAM) while fetching data.
> > >
> > > I tried setting the isolation level to committed read and setting the
> > lock
> > > mode to not wait, but I got the same error.
> > >
> > > Please can anyone let me know, if there is a way to read records
> without
> > > getting any error without doing the dirty read.
> > >
> > > Thanks and Regards,
> > > Naveen
> > >
> > >
> > >
> >
> >
> >
>
>
*******************************************************************************
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --002354470f445969d204b5a4059a
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6dd85999b3c7c04b5a61fc2
Yes.
Just verified , you should set "lock mode row" for the table even you
adopt ,
set isolation to committed read last committed ;
Good luck!
Frank
On Tue, Jan 3, 2012 at 4:10 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Also do not forget set lock mode row even with LAST COMMITTED READ?
> Frank
>
> On Tue, Jan 3, 2012 at 1:39 PM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > I believe the OP wrote that it set it to NOT WAIT. So the behavior is
> > expectable and the solution is the COMMITTED READ LAST COMMITTED READ.
> > Note that this only appeared on version 11.
> >
> > Depending on your data, sometimes other solutions can be used like using
> > dirty read but setting conditions on timestamp data (is this is present)
> >
> > Regards.
> >
> > On Tue, Jan 3, 2012 at 5:34 PM, John Miller iii <miller3@us.ibm.com>
> > wrote:
> >
> > > I would try the "set isolation committed read last committed" for this
> > > select statement.> > >
> > > Also how long did you set the application to wait? Based on what you
> said
> > > below it would have to wait at least 2 minutes.
> > >
> > > John F. Miller III
> > > STSM, Embedability Architect
> > > miller3@us.ibm.com
> > > 503-578-5645
> > > IBM Informix Dynamic Server (IDS)
> > > (Embedded image moved to file: pic51775.gif)
> > >
> > > ids-bounces@iiug.org wrote on 01/03/2012 09:25:12 AM:
> > >
> > > > From: "NAVEEN NAIK" <navnaik@gmail.com>
> > > > To: ids@iiug.org
> > > > Date: 01/03/2012 09:26 AM
> > > > Subject: To read only committed rows from a table [25799]
> > > > Sent by: ids-bounces@iiug.org
> > > >
> > > > Hi
> > > >
> > > > I am trying a read committed rows from a table.
> > > >
> > > > I have one application which inserts records into a table(The
> > application
> > >
> > > > takes as long as 1 to 2 mins to commit the transaction )
> > > >
> > > > I am developing another application using informix 4gl, to read any
> > > record
> > > > which has been committed to this table. While reading the record from
> > the
> > >
> > > > table, if there is any record which is in a transaction I get the
> error
> > > > -245/-144(ISAM) while fetching data.
> > > >
> > > > I tried setting the isolation level to committed read and setting the
> > > lock
> > > > mode to not wait, but I got the same error.
> > > >
> > > > Please can anyone let me know, if there is a way to read records
> > without
> > > > getting any error without doing the dirty read.
> > > >
> > > > Thanks and Regards,
> > > > Naveen
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > >
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --002354470f445969d204b5a4059a
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0016e6dd85999b3c7c04b5a61fc2
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04428cf45b6d6404b5a6aaa7
Yes, you must have row level locking as last committed only works
with row level locking.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic29443.gif)
ids-bounces@iiug.org wrote on 01/03/2012 01:48:59 PM:
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org
> Date: 01/03/2012 01:50 PM
> Subject: Re: To read only committed rows from a table [25803]
> Sent by: ids-bounces@iiug.org
>
> Yes.
>
> Just verified , you should set "lock mode row" for the table even you
> adopt ,
>
> set isolation to committed read last committed ;>
> Good luck!
> Frank
>
> On Tue, Jan 3, 2012 at 4:10 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Also do not forget set lock mode row even with LAST COMMITTED READ?
> > Frank
> >
> > On Tue, Jan 3, 2012 at 1:39 PM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > I believe the OP wrote that it set it to NOT WAIT. So the behavior is
> > > expectable and the solution is the COMMITTED READ LAST COMMITTED
READ.
> > > Note that this only appeared on version 11.
> > >
> > > Depending on your data, sometimes other solutions can be used like
using
> > > dirty read but setting conditions on timestamp data (is this is
present)
> > >
> > > Regards.
> > >
> > > On Tue, Jan 3, 2012 at 5:34 PM, John Miller iii <miller3@us.ibm.com>
> > > wrote:
> > >
> > > > I would try the "set isolation committed read last committed" for
this
> > > > select statement.> > > >
> > > > Also how long did you set the application to wait? Based on what
you
> > said
> > > > below it would have to wait at least 2 minutes.
> > > >
> > > > John F. Miller III
> > > > STSM, Embedability Architect
> > > > miller3@us.ibm.com
> > > > 503-578-5645
> > > > IBM Informix Dynamic Server (IDS)
> > > > (Embedded image moved to file: pic51775.gif)
> > > >
> > > > ids-bounces@iiug.org wrote on 01/03/2012 09:25:12 AM:
> > > >
> > > > > From: "NAVEEN NAIK" <navnaik@gmail.com>
> > > > > To: ids@iiug.org
> > > > > Date: 01/03/2012 09:26 AM
> > > > > Subject: To read only committed rows from a table [25799]
> > > > > Sent by: ids-bounces@iiug.org
> > > > >
> > > > > Hi
> > > > >
> > > > > I am trying a read committed rows from a table.
> > > > >
> > > > > I have one application which inserts records into a table(The
> > > application
> > > >
> > > > > takes as long as 1 to 2 mins to commit the transaction )
> > > > >
> > > > > I am developing another application using informix 4gl, to read
any
> > > > record
> > > > > which has been committed to this table. While reading the record
from
> > > the
> > > >
> > > > > table, if there is any record which is in a transaction I get the
> > error
> > > > > -245/-144(ISAM) while fetching data.
> > > > >
> > > > > I tried setting the isolation level to committed read and setting
the
> > > > lock
> > > > > mode to not wait, but I got the same error.
> > > > >
> > > > > Please can anyone let me know, if there is a way to read records
> > > without
> > > > > getting any error without doing the dirty read.
> > > > >
> > > > > Thanks and Regards,
> > > > > Naveen
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > >
> > > > > Forum Note: Use "Reply" to post a response in the discussion
forum.
> > > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --002354470f445969d204b5a4059a
> > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e6dd85999b3c7c04b5a61fc2
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d04428cf45b6d6404b5a6aaa7
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
True... I missed that one because I don't work any other way :)
Regards.
On Tue, Jan 3, 2012 at 9:48 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Yes.
>
> Just verified , you should set "lock mode row" for the table even you
> adopt ,
>
> set isolation to committed read last committed ;>
> Good luck!
> Frank
>
> On Tue, Jan 3, 2012 at 4:10 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Also do not forget set lock mode row even with LAST COMMITTED READ?
> > Frank
> >
> > On Tue, Jan 3, 2012 at 1:39 PM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > I believe the OP wrote that it set it to NOT WAIT. So the behavior is
> > > expectable and the solution is the COMMITTED READ LAST COMMITTED READ.
> > > Note that this only appeared on version 11.
> > >
> > > Depending on your data, sometimes other solutions can be used like
> using
> > > dirty read but setting conditions on timestamp data (is this is
> present)
> > >
> > > Regards.
> > >
> > > On Tue, Jan 3, 2012 at 5:34 PM, John Miller iii <miller3@us.ibm.com>
> > > wrote:
> > >
> > > > I would try the "set isolation committed read last committed" for
> this
> > > > select statement.> > > >
> > > > Also how long did you set the application to wait? Based on what you
> > said
> > > > below it would have to wait at least 2 minutes.
> > > >
> > > > John F. Miller III
> > > > STSM, Embedability Architect
> > > > miller3@us.ibm.com
> > > > 503-578-5645
> > > > IBM Informix Dynamic Server (IDS)
> > > > (Embedded image moved to file: pic51775.gif)
> > > >
> > > > ids-bounces@iiug.org wrote on 01/03/2012 09:25:12 AM:
> > > >
> > > > > From: "NAVEEN NAIK" <navnaik@gmail.com>
> > > > > To: ids@iiug.org
> > > > > Date: 01/03/2012 09:26 AM
> > > > > Subject: To read only committed rows from a table [25799]
> > > > > Sent by: ids-bounces@iiug.org
> > > > >
> > > > > Hi
> > > > >
> > > > > I am trying a read committed rows from a table.
> > > > >
> > > > > I have one application which inserts records into a table(The
> > > application
> > > >
> > > > > takes as long as 1 to 2 mins to commit the transaction )
> > > > >
> > > > > I am developing another application using informix 4gl, to read any
> > > > record
> > > > > which has been committed to this table. While reading the record
> from
> > > the
> > > >
> > > > > table, if there is any record which is in a transaction I get the
> > error
> > > > > -245/-144(ISAM) while fetching data.
> > > > >
> > > > > I tried setting the isolation level to committed read and setting
> the
> > > > lock
> > > > > mode to not wait, but I got the same error.
> > > > >
> > > > > Please can anyone let me know, if there is a way to read records
> > > without
> > > > > getting any error without doing the dirty read.
> > > > >
> > > > > Thanks and Regards,
> > > > > Naveen
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > >
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --002354470f445969d204b5a4059a
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e6dd85999b3c7c04b5a61fc2
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d04428cf45b6d6404b5a6aaa7
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--00248c6a6a429f21a004b5a722d4
Thanks all for your replays. I should have mentioned the Informix version I am using. Apologies for that. I am using the IDS 9.4 The last committed is not working here :-( Is there any other way around this. I am also retrieving based on time stamp, but I am using a multi user environment. Some rows are committed before others. Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed Row2 <Data> | 04-01-2012 10:33:34 | - Committed Sometimes, Row2 with a greater time stamp gets committed before the another row which has lesser time stamp. And since row 1 is not committed I am getting the error. I was under the assumption that SET ISOLATION TO COMMITTED READ should have worked. Still wondering why it is not working. Regards, Naveen Naik
Ths committed read is working as expected. It tells the engine that it should not ignore other session locks (like dirty read does) and at the same time that it should not lock the rows it reads (like repeatable read does). The session behavior when working in committed read, when it finds a lock depends on the LOCK MODE. If LOCK MODE is set to NOT WAIT the session immediately receives an error. If it's WAIT n, then it waits at most "n" seconds. If after "n" seconds the lock is still there, the error is raised. Finally it you SET LOCK MODE TO WAIT; (without a number) then the session will wait forever. This is how it works, and it's explained in the manual. In your specific case I think you need to check why the other process is holding locks for 2 minutes and try to reduce that time. The condition on the timestamp could be used if you set it to "timestamp_field < CURRENT - 2 UNITS MINUTE" for example... but for this to work you'd probably need an index on that column... If you're doing a full scan for example, you'd hit the locks anyway... Or you could "accept" the error and process whatever lines you get... And retry later hoping that you'll grab some more rows and so on... This could be a way to do it assuming both processes are run frequently and concurrently. If not, than simply offset the execution times of both processes. To end this, this is a clear example of the costs of being stuck with old versions... Regards. On Wed, Jan 4, 2012 at 9:37 AM, NAVEEN NAIK <navnaik@gmail.com> wrote: > Thanks all for your replays. > > I should have mentioned the Informix version I am using. Apologies for > that. > > I am using the IDS 9.4 > > The last committed is not working here :-( > > Is there any other way around this. > > I am also retrieving based on time stamp, but I am using a multi user > environment. > Some rows are committed before others. > > Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed > Row2 <Data> | 04-01-2012 10:33:34 | - Committed > > Sometimes, Row2 with a greater time stamp gets committed before the another > row which has lesser time stamp. And since row 1 is not committed I am > getting > the error. > > I was under the assumption that SET ISOLATION TO COMMITTED READ should have > worked. Still wondering why it is not working. > > Regards, > Naveen Naik > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3063e337f5f83804b5b145ff
Speaking of lock mode --- is there STILL no way to set it in onconfig file to default to "wait X" or do we still have to set after each connection ? ( I am speaking of 11.70.TC3IE) It is wasteful to execute the "set" statement (in c++ or c# or whatever) upon each connection. If "not wait" is set then one must loop to retry an update ( also wasteful ). If you don't, then things rollback on a busy server and users get unhappy. With all the improvements done to IDS one would think that the lock mode default would be put into onconfig. Was IBM just to busy to fix this, or do they like us to suffer or make our own code harder to read? I did have several programs (written by departed programmers) that blew out at busy times. When I added "set lock mode to wait 10" the problems went away completely. Ten seconds is like infinity and the next waiter gets the row. ( all tables use row locking) -----Original Message----- From: Fernando Nunes Sent: Wednesday, January 04, 2012 4:28 AM To: ids@iiug.org Subject: Re: To read only committed rows from a table [25807] Ths committed read is working as expected. It tells the engine that it should not ignore other session locks (like dirty read does) and at the same time that it should not lock the rows it reads (like repeatable read does). The session behavior when working in committed read, when it finds a lock depends on the LOCK MODE. If LOCK MODE is set to NOT WAIT the session immediately receives an error. If it's WAIT n, then it waits at most "n" seconds. If after "n" seconds the lock is still there, the error is raised. Finally it you SET LOCK MODE TO WAIT; (without a number) then the session will wait forever. This is how it works, and it's explained in the manual. In your specific case I think you need to check why the other process is holding locks for 2 minutes and try to reduce that time. The condition on the timestamp could be used if you set it to "timestamp_field < CURRENT - 2 UNITS MINUTE" for example... but for this to work you'd probably need an index on that column... If you're doing a full scan for example, you'd hit the locks anyway... Or you could "accept" the error and process whatever lines you get... And retry later hoping that you'll grab some more rows and so on... This could be a way to do it assuming both processes are run frequently and concurrently. If not, than simply offset the execution times of both processes. To end this, this is a clear example of the costs of being stuck with old versions... Regards. On Wed, Jan 4, 2012 at 9:37 AM, NAVEEN NAIK <navnaik@gmail.com> wrote: > Thanks all for your replays. > > I should have mentioned the Informix version I am using. Apologies for > that. > > I am using the IDS 9.4 > > The last committed is not working here :-( > > Is there any other way around this. > > I am also retrieving based on time stamp, but I am using a multi user > environment. > Some rows are committed before others. > > Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed > Row2 <Data> | 04-01-2012 10:33:34 | - Committed > > Sometimes, Row2 with a greater time stamp gets committed before the > another > row which has lesser time stamp. And since row 1 is not committed I am > getting > the error. > > I was under the assumption that SET ISOLATION TO COMMITTED READ should > have > worked. Still wondering why it is not working. > > Regards, > Naveen Naik > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3063e337f5f83804b5b145ff ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Bill:
While you can not set it in the onconfig you can make it a default setting
of a database.
In your desired database, create the following procedure. This will be
called every
time a user connect to the database.
CREATE PROCEDURE public.sysdbopen()
SET LOCK MODE TO WAIT 10;END PROCEDURE;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic28929.gif)
ids-bounces@iiug.org wrote on 01/04/2012 10:21:11 AM:
> From: "Bill Hamilton" <garage_dba@hotmail.com>
> To: ids@iiug.org
> Date: 01/04/2012 10:24 AM
> Subject: Re: To read only committed rows from a table [25813]
> Sent by: ids-bounces@iiug.org
>
> Speaking of lock mode --- is there STILL no way to set it in onconfig
file
> to default to "wait X" or do we still have to set after each connection ?
> ( I am speaking of 11.70.TC3IE)
> It is wasteful to execute the "set" statement (in c++ or c# or whatever)
> upon each connection.
> If "not wait" is set then one must loop to retry an update ( also
> wasteful ). If you don't, then things rollback on a busy server and users
> get unhappy.
> With all the improvements done to IDS one would think that the lock mode
> default would be put into onconfig.
> Was IBM just to busy to fix this, or do they like us to suffer or make
our
> own code harder to read?
> I did have several programs (written by departed programmers) that blew
out
> at busy times.
> When I added "set lock mode to wait 10" the problems went away
completely.
> Ten seconds is like infinity and the next waiter gets the row.
> ( all tables use row locking)
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Wednesday, January 04, 2012 4:28 AM
> To: ids@iiug.org
> Subject: Re: To read only committed rows from a table [25807]
>
> Ths committed read is working as expected. It tells the engine that it
> should not ignore other session locks (like dirty read does) and at the
> same time that it should not lock the rows it reads (like repeatable read
> does). The session behavior when working in committed read, when it finds
a
> lock depends on the LOCK MODE.
> If LOCK MODE is set to NOT WAIT the session immediately receives an
error.
> If it's WAIT n, then it waits at most "n" seconds. If after "n" seconds
the
> lock is still there, the error is raised. Finally it you SET LOCK MODE TO
> WAIT; (without a number) then the session will wait forever.
>
> This is how it works, and it's explained in the manual.
> In your specific case I think you need to check why the other process is
> holding locks for 2 minutes and try to reduce that time.
> The condition on the timestamp could be used if you set it to
> "timestamp_field < CURRENT - 2 UNITS MINUTE" for example... but for this
to
> work you'd probably need an index on that column... If you're doing a
full
> scan for example, you'd hit the locks anyway...
>
> Or you could "accept" the error and process whatever lines you get... And
> retry later hoping that you'll grab some more rows and so on... This
could
> be a way to do it assuming both processes are run frequently and
> concurrently. If not, than simply offset the execution times of both
> processes.
>
> To end this, this is a clear example of the costs of being stuck with old
> versions...
> Regards.
>
> On Wed, Jan 4, 2012 at 9:37 AM, NAVEEN NAIK <navnaik@gmail.com> wrote:
>
> > Thanks all for your replays.
> >
> > I should have mentioned the Informix version I am using. Apologies for
> > that.
> >
> > I am using the IDS 9.4
> >
> > The last committed is not working here :-(
> >
> > Is there any other way around this.
> >
> > I am also retrieving based on time stamp, but I am using a multi user
> > environment.
> > Some rows are committed before others.
> >
> > Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed
> > Row2 <Data> | 04-01-2012 10:33:34 | - Committed
> >
> > Sometimes, Row2 with a greater time stamp gets committed before the
> > another
> > row which has lesser time stamp. And since row 1 is not committed I am
> > getting
> > the error.
> >
> > I was under the assumption that SET ISOLATION TO COMMITTED READ should
> > have
> > worked. Still wondering why it is not working.
> >
> > Regards,
> > Naveen Naik
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3063e337f5f83804b5b145ff
>
>
>
*******************************************************************************
> 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, John.
I will try that.
-----Original Message-----
From: John Miller iii
Sent: Wednesday, January 04, 2012 12:33 PM
To: ids@iiug.org
Subject: Re: To read only committed rows from a table [25814]
Bill:
While you can not set it in the onconfig you can make it a default setting
of a database.
In your desired database, create the following procedure. This will be
called every
time a user connect to the database.
CREATE PROCEDURE public.sysdbopen()
SET LOCK MODE TO WAIT 10;END PROCEDURE;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
(Embedded image moved to file: pic28929.gif)
ids-bounces@iiug.org wrote on 01/04/2012 10:21:11 AM:
> From: "Bill Hamilton" <garage_dba@hotmail.com>
> To: ids@iiug.org
> Date: 01/04/2012 10:24 AM
> Subject: Re: To read only committed rows from a table [25813]
> Sent by: ids-bounces@iiug.org
>
> Speaking of lock mode --- is there STILL no way to set it in onconfig
file
> to default to "wait X" or do we still have to set after each connection ?
> ( I am speaking of 11.70.TC3IE)
> It is wasteful to execute the "set" statement (in c++ or c# or whatever)
> upon each connection.
> If "not wait" is set then one must loop to retry an update ( also
> wasteful ). If you don't, then things rollback on a busy server and users
> get unhappy.
> With all the improvements done to IDS one would think that the lock mode
> default would be put into onconfig.
> Was IBM just to busy to fix this, or do they like us to suffer or make
our
> own code harder to read?
> I did have several programs (written by departed programmers) that blew
out
> at busy times.
> When I added "set lock mode to wait 10" the problems went away
completely.
> Ten seconds is like infinity and the next waiter gets the row.
> ( all tables use row locking)
>
> -----Original Message-----
> From: Fernando Nunes
> Sent: Wednesday, January 04, 2012 4:28 AM
> To: ids@iiug.org
> Subject: Re: To read only committed rows from a table [25807]
>
> Ths committed read is working as expected. It tells the engine that it
> should not ignore other session locks (like dirty read does) and at the
> same time that it should not lock the rows it reads (like repeatable read
> does). The session behavior when working in committed read, when it finds
a
> lock depends on the LOCK MODE.
> If LOCK MODE is set to NOT WAIT the session immediately receives an
error.
> If it's WAIT n, then it waits at most "n" seconds. If after "n" seconds
the
> lock is still there, the error is raised. Finally it you SET LOCK MODE TO
> WAIT; (without a number) then the session will wait forever.
>
> This is how it works, and it's explained in the manual.
> In your specific case I think you need to check why the other process is
> holding locks for 2 minutes and try to reduce that time.
> The condition on the timestamp could be used if you set it to
> "timestamp_field < CURRENT - 2 UNITS MINUTE" for example... but for this
to
> work you'd probably need an index on that column... If you're doing a
full
> scan for example, you'd hit the locks anyway...
>
> Or you could "accept" the error and process whatever lines you get... And
> retry later hoping that you'll grab some more rows and so on... This
could
> be a way to do it assuming both processes are run frequently and
> concurrently. If not, than simply offset the execution times of both
> processes.
>
> To end this, this is a clear example of the costs of being stuck with old
> versions...
> Regards.
>
> On Wed, Jan 4, 2012 at 9:37 AM, NAVEEN NAIK <navnaik@gmail.com> wrote:
>
> > Thanks all for your replays.
> >
> > I should have mentioned the Informix version I am using. Apologies for
> > that.
> >
> > I am using the IDS 9.4
> >
> > The last committed is not working here :-(
> >
> > Is there any other way around this.
> >
> > I am also retrieving based on time stamp, but I am using a multi user
> > environment.
> > Some rows are committed before others.
> >
> > Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed
> > Row2 <Data> | 04-01-2012 10:33:34 | - Committed
> >
> > Sometimes, Row2 with a greater time stamp gets committed before the
> > another
> > row which has lesser time stamp. And since row 1 is not committed I am
> > getting
> > the error.
> >
> > I was under the assumption that SET ISOLATION TO COMMITTED READ should
> > have
> > worked. Still wondering why it is not working.
> >
> > Regards,
> > Naveen Naik
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3063e337f5f83804b5b145ff
>
>
>
*******************************************************************************
> 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.
I feel your pain... And there will always be unhappy people because some features were not implemented yet... But there are a few improvements that can help: 1- You can set it in sysdbopen() 2- You can set it in Java URls 3- I beleive you can set it in ODBC connection strings (I should have checked this) 4- You could automatically move all COMMITTED READ to COMMITTED READ LAST COMMITTED (do you dare to do it?..) But yes... I would vote for that... Although I'm not sure if many customers would use it... It could break things... Regards. On Wed, Jan 4, 2012 at 6:21 PM, Bill Hamilton <garage_dba@hotmail.com>wrote: > Speaking of lock mode --- is there STILL no way to set it in onconfig file > to default to "wait X" or do we still have to set after each connection ? > ( I am speaking of 11.70.TC3IE) > It is wasteful to execute the "set" statement (in c++ or c# or whatever) > upon each connection. > If "not wait" is set then one must loop to retry an update ( also > wasteful ). If you don't, then things rollback on a busy server and users > get unhappy. > With all the improvements done to IDS one would think that the lock mode > default would be put into onconfig. > Was IBM just to busy to fix this, or do they like us to suffer or make our > own code harder to read? > I did have several programs (written by departed programmers) that blew out > at busy times. > When I added "set lock mode to wait 10" the problems went away completely. > Ten seconds is like infinity and the next waiter gets the row. > ( all tables use row locking) > > -----Original Message----- > From: Fernando Nunes > Sent: Wednesday, January 04, 2012 4:28 AM > To: ids@iiug.org > Subject: Re: To read only committed rows from a table [25807] > > Ths committed read is working as expected. It tells the engine that it > should not ignore other session locks (like dirty read does) and at the > same time that it should not lock the rows it reads (like repeatable read > does). The session behavior when working in committed read, when it finds a > lock depends on the LOCK MODE. > If LOCK MODE is set to NOT WAIT the session immediately receives an error. > If it's WAIT n, then it waits at most "n" seconds. If after "n" seconds the > lock is still there, the error is raised. Finally it you SET LOCK MODE TO > WAIT; (without a number) then the session will wait forever. > > This is how it works, and it's explained in the manual. > In your specific case I think you need to check why the other process is > holding locks for 2 minutes and try to reduce that time. > The condition on the timestamp could be used if you set it to > "timestamp_field < CURRENT - 2 UNITS MINUTE" for example... but for this to > work you'd probably need an index on that column... If you're doing a full > scan for example, you'd hit the locks anyway... > > Or you could "accept" the error and process whatever lines you get... And > retry later hoping that you'll grab some more rows and so on... This could > be a way to do it assuming both processes are run frequently and > concurrently. If not, than simply offset the execution times of both > processes. > > To end this, this is a clear example of the costs of being stuck with old > versions... > Regards. > > On Wed, Jan 4, 2012 at 9:37 AM, NAVEEN NAIK <navnaik@gmail.com> wrote: > > > Thanks all for your replays. > > > > I should have mentioned the Informix version I am using. Apologies for > > that. > > > > I am using the IDS 9.4 > > > > The last committed is not working here :-( > > > > Is there any other way around this. > > > > I am also retrieving based on time stamp, but I am using a multi user > > environment. > > Some rows are committed before others. > > > > Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed > > Row2 <Data> | 04-01-2012 10:33:34 | - Committed > > > > Sometimes, Row2 with a greater time stamp gets committed before the > > another > > row which has lesser time stamp. And since row 1 is not committed I am > > getting > > the error. > > > > I was under the assumption that SET ISOLATION TO COMMITTED READ should > > have > > worked. Still wondering why it is not working. > > > > Regards, > > Naveen Naik > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf3063e337f5f83804b5b145ff > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300fae91c8356904b5b87bc5
1. I am trying that out 2. I don't do Java 3. I looked for that but could not find 4. I guess you mean "USELASTCOMMITTED ALL" or "USELASTCOMMITTED COMMITTED READ" in onconfig file . I use ALL (tough guy). What sort of breakage would I look for? 5. I would vote for it but now that John Miller has educated me on public.sysdbopen() I suppose I don't need it. 6. I wonder if there would be any overhead difference the other way (onconfig variable) . 7. What breakage would you see for WAIT 10 as opposed to NOT WAIT ? It should not slow down since the wait occurs only when a lock is hit. Maybe a program could be looking for a stuck lock to do some other work? Hard to imagine unless it is the dba. -----Original Message----- From: Fernando Nunes Sent: Wednesday, January 04, 2012 1:04 PM To: ids@iiug.org Subject: Re: To read only committed rows from a table [25816] I feel your pain... And there will always be unhappy people because some features were not implemented yet... But there are a few improvements that can help: 1- You can set it in sysdbopen() 2- You can set it in Java URls 3- I beleive you can set it in ODBC connection strings (I should have checked this) 4- You could automatically move all COMMITTED READ to COMMITTED READ LAST COMMITTED (do you dare to do it?..) But yes... I would vote for that... Although I'm not sure if many customers would use it... It could break things... Regards. On Wed, Jan 4, 2012 at 6:21 PM, Bill Hamilton <garage_dba@hotmail.com>wrote: > Speaking of lock mode --- is there STILL no way to set it in onconfig file > to default to "wait X" or do we still have to set after each connection ? > ( I am speaking of 11.70.TC3IE) > It is wasteful to execute the "set" statement (in c++ or c# or whatever) > upon each connection. > If "not wait" is set then one must loop to retry an update ( also > wasteful ). If you don't, then things rollback on a busy server and users > get unhappy. > With all the improvements done to IDS one would think that the lock mode > default would be put into onconfig. > Was IBM just to busy to fix this, or do they like us to suffer or make our > own code harder to read? > I did have several programs (written by departed programmers) that blew > out > at busy times. > When I added "set lock mode to wait 10" the problems went away completely. > Ten seconds is like infinity and the next waiter gets the row. > ( all tables use row locking) > > -----Original Message----- > From: Fernando Nunes > Sent: Wednesday, January 04, 2012 4:28 AM > To: ids@iiug.org > Subject: Re: To read only committed rows from a table [25807] > > Ths committed read is working as expected. It tells the engine that it > should not ignore other session locks (like dirty read does) and at the > same time that it should not lock the rows it reads (like repeatable read > does). The session behavior when working in committed read, when it finds > a > lock depends on the LOCK MODE. > If LOCK MODE is set to NOT WAIT the session immediately receives an error. > If it's WAIT n, then it waits at most "n" seconds. If after "n" seconds > the > lock is still there, the error is raised. Finally it you SET LOCK MODE TO > WAIT; (without a number) then the session will wait forever. > > This is how it works, and it's explained in the manual. > In your specific case I think you need to check why the other process is > holding locks for 2 minutes and try to reduce that time. > The condition on the timestamp could be used if you set it to > "timestamp_field < CURRENT - 2 UNITS MINUTE" for example... but for this > to > work you'd probably need an index on that column... If you're doing a full > scan for example, you'd hit the locks anyway... > > Or you could "accept" the error and process whatever lines you get... And > retry later hoping that you'll grab some more rows and so on... This could > be a way to do it assuming both processes are run frequently and > concurrently. If not, than simply offset the execution times of both > processes. > > To end this, this is a clear example of the costs of being stuck with old > versions... > Regards. > > On Wed, Jan 4, 2012 at 9:37 AM, NAVEEN NAIK <navnaik@gmail.com> wrote: > > > Thanks all for your replays. > > > > I should have mentioned the Informix version I am using. Apologies for > > that. > > > > I am using the IDS 9.4 > > > > The last committed is not working here :-( > > > > Is there any other way around this. > > > > I am also retrieving based on time stamp, but I am using a multi user > > environment. > > Some rows are committed before others. > > > > Row1 <Data> | 04-01-2012 10:33:33 | - Not yet committed > > Row2 <Data> | 04-01-2012 10:33:34 | - Committed > > > > Sometimes, Row2 with a greater time stamp gets committed before the > > another > > row which has lesser time stamp. And since row 1 is not committed I am > > getting > > the error. > > > > I was under the assumption that SET ISOLATION TO COMMITTED READ should > > have > > worked. Still wondering why it is not working. > > > > Regards, > > Naveen Naik > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf3063e337f5f83804b5b145ff > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300fae91c8356904b5b87bc5 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks all for your help.
I am doing a work around for the issue I am facing.
I am doing a dirty read to retrieve the records and once I get all the records,
I am checking the same table (with the isolation set to committed read).
If I get the lock error, then I am ignoring it and checking the next record
(got by doing the dirty read)
So,
SET ISOLATION TO DIRTY READ;
Select ID from table where state = <SOMEVALUE>
SET ISOLATION TO COMMITTED READ;
select * from table where id = <ID VALUE from previous Query>
If error
ignore
else
Continue
Its working for me now, should I be looking for something which could break my
application.
Because my application will not stop once it is started (will be doing the
same thing in an infinite loop)
I was also wondering would my performance get hampered if I do 30 to 50
transaction commits in a second or any issue would be caused because of this,
in IDS 9.40C2 <which I can not upgrade :-( >