Re: update impossible in spite of row locking

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>

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