Re: update impossible in spite of row locking
Sorry, yes the table def is with lock mode row.
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org]On Behalf Of theBP
> Sent: Tuesday, June 23, 2009 3:52 PM
> To: informix-list@iiug.org
> Subject: Re: update impossible in spite of row locking
>
>
> Habichtsberg, Reinhard wrote:
> > Hi,
> >
> > inspired of your numerous suggestions I made some more tests:
> >
> > I learned that I have to avoid sequential scans and I
> learned that if I
> > provide a unique index and primary key on the table update
> of different rows
> > in multiple sessions is possible. Precondition is that the
> access to the
> > rows happens via the (unique) index.
> >
> > For your interest:
> >
> > create table "informix".tab
> > (
> > keycol char(30) not null ,
> > xx integer not null ,
> > xy char(1024)
> > );
> >
> > create unique index "informix".tab_1 on "informix".tab
> > (keycol) using btree ;
> > alter table "informix".tab add constraint primary key
> > (keycol) constraint "informix".pk_tab ;
> >
> >
> > First session:
> > begin;
> > update tab
> > set xx = xx + 1
> > where keycol = "row_58";
> >
> > Second session:
> >
> > Example 1:
> > set explain on;
> > set isolation to dirty read;> > update tab
> > set xx = xx + 1
> > where keyrow = "row_59";
> > - Fails. if unique index and primary key are ommited
> >
> > Example 2:
> > set explain on;
> > set isolation to dirty read;> > update tab
> > set xx = xx +1
> > where keyrow != "row_58";
> > - Fails, though unique index and primary key exist! sqexplain shows
> > SEQUENTIAL SCAN
> >
> > Example 3:
> > set explain on;
> > set isolation to dirty read;
> > select keycol from tab
> > where keycol != "row_58"
> > into temp t1 with no log;> > update tab
> > set xx = xx + 1
> > where keycol in (select * from t1);
> > - runs without error! sqexplain shows INDEX PATH
> >
> > So it seems to me that the problem is solved. Thanks to all
> for your help.
> >
> > And the developer guys are glad that I packed away that club ;-)
> >
> > Regards,
> > Reinhard.
> >
> >
> > -----Original Message-----
> > From: informix-list-bounces@iiug.org
> > [mailto:informix-list-bounces@iiug.org]On Behalf Of Ian
> Michael Gumby
> > Sent: Tuesday, June 23, 2009 2:19 PM
> > To: thebp@usenet-news.net; informix-list@iiug.org
> > Subject: RE: update impossible in spite of row locking
> >
> >
> >
> >
> >
> >> From: theBP@Usenet-News.Net
> >
> >>> Can anybody help? It's rather urgend.
> >>>
> >>> TIA,
> >>> Reinhard.
> >> What is the table schema?
> >>
> >> What is you update statistics strategy?
> >>
> >> What is the query plan?
> >> _______________________________________________
> >
> > Well I think it could be query plans.
> >
> > The OP didn't say how many or which applications were
> hitting the database.
> >
> > Based on personal observations, I'd say that a majority
> causes for bad
> > performance is due to poor table design and poor
> application design and
> > coding. No offense to Lester and his "Fastest DBA"
> contest(s), but truly bad
> > programming logic will hurt you more.
> >
> > Using a hotel reservation system as an example... Suppose
> you're writing an
> > app where the user wants to rent a hotel room in NYC. Hyatt
> has several
> > properties. A really, really bad design would be to start a
> transaction here
> > and lock the hotels for the duration of the transaction.
> >
> > Even if you select a hotel room and then lock that row,
> while you search
> > other properties, you still had trouble. You're still
> holding a very long
> > transaction.
> >
> > A better idea would be to start a 'logical programatic
> transaction' by
> > putting a hold on a room that met the user's requirements,
> and continued
> > with the search. At the end of the 'logical programatic
> transaction', you
> > either have a session timeout which would then release the
> rooms back to the
> > vacancy list, or the user selects a room, and then releases
> the holds on
> > other rooms at other properties.
> >
> > (Again, I'm going from memory of a presentation that Dana
> L. gave.) If you
> > were around in the early 90's and knew any of the
> consultants in Chicago at
> > the time, you couldn't miss her. ;-) Along with Johnny W.,
> Eric O, Pete C,
> > Stephan B, El Stubbe, Mark J. and a couple of others that
> I'm probably
> > missing. ...
> >
> > So until Reinhard shares more information about the
> application(s), we
> > probably can't really help him.
> >
> >
> > But hey! What do I know? I'm an app developer so my first
> choice of where to
> > look is in the application.
> >
> > -G
> >
> >
> >
> > _____
> >
> > Bing(tm) brings you maps, menus, and reviews organized in
> one place. Try it
> > now.
> >
<http://www.bing.com/search?q=restaurants&form=MLOGEN&publ=WLHMTAG&crea=TEXT
> _MLOGEN_Core_tagline_local_1x1>
>
Stoopid comment, I *presume* you have LOCK MODE ROW against the table
definition (-ss option to see this with dbschema).
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list