IDS Forum

Re: To read only committed rows from a table

Posted By: John Miller iii
Date: Wednesday, 4 January 2012, at 1:33 p.m.

In Response To: Re: To read only committed rows from a table (Bill Hamilton)

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.
>

Messages In This Thread

• To read only committed rows from a table
  NAVEEN NAIK -- Tuesday, 3 January 2012, at 12:25 p.m.
  • Re: To read only committed rows from a table
    John Miller iii -- Tuesday, 3 January 2012, at 12:34 p.m.
    • Re: To read only committed rows from a table
      Fernando Nunes -- Tuesday, 3 January 2012, at 1:39 p.m.
      • Re: To read only committed rows from a table
        FRANK -- Tuesday, 3 January 2012, at 4:10 p.m.
        • Re: To read only committed rows from a table
          FRANK -- Tuesday, 3 January 2012, at 4:48 p.m.
          • Re: To read only committed rows from a table
            John Miller iii -- Tuesday, 3 January 2012, at 5:07 p.m.
          • Re: To read only committed rows from a table
            Fernando Nunes -- Tuesday, 3 January 2012, at 5:22 p.m.
            • Re: To read only committed rows from a table
              NAVEEN NAIK -- Wednesday, 4 January 2012, at 4:37 a.m.
              • Re: To read only committed rows from a table
                Fernando Nunes -- Wednesday, 4 January 2012, at 5:28 a.m.
                • Re: To read only committed rows from a table
                  Bill Hamilton -- Wednesday, 4 January 2012, at 1:21 p.m.
                  • Re: To read only committed rows from a table
                    John Miller iii -- Wednesday, 4 January 2012, at 1:33 p.m.
                    • Re: To read only committed rows from a table
                      Bill Hamilton -- Wednesday, 4 January 2012, at 1:40 p.m.
                  • Re: To read only committed rows from a table
                    Fernando Nunes -- Wednesday, 4 January 2012, at 2:04 p.m.
                    • Re: To read only committed rows from a table
                      Bill Hamilton -- Wednesday, 4 January 2012, at 3:42 p.m.
                      • Re: To read only committed rows from a table
                        NAVEEN NAIK -- Thursday, 5 January 2012, at 7:27 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.