RE: update impossible in spite of row locking

Hey! If you're locking a row for update/delete and your other thread/app is trying to access the row, then you have your blocking condition. There is no way around the fact that the row will be locked/blocked for some period of time. The issue it appears is that you need to limit the amount of time it takes for the row to be blocked. That's the key. Look at the application(s) and see what is in the transaction and how long you have to hold the delete/update locks. Clearly if you're not doing table scans, then you avoid this potential problem for the majority of the time, however it doesn't mean that collisions still will not happen. Also sometimes even if you're using an index, on complex queries, you can only use one index per table, so if your first index limits your query to 100,000 rows, you then have to do a sequential scan on those rows. (XPS doesn't have this problem.) Again, I strongly suggest a code review of your application. HTH -G > From: RHabichtsberg@arz-emmendingen.de > To: informix-list@iiug.org > Subject: Re: update impossible in spite of row locking > Date: Wed, 24 Jun 2009 08:02:39 +0200 > > 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 > _______________________________________________ > Informix-list mailing list@@

Ian Michael Gumby wrote: > Hey! > > If you're locking a row for update/delete and your other thread/app is > trying to access the row, then you have your blocking condition. > > There is no way around the fact that the row will be locked/blocked for > some period of time. > The issue it appears is that you need to limit the amount of time it > takes for the row to be blocked. > > That's the key. Look at the application(s) and see what is in the > transaction and how long you have to hold the delete/update locks. Or move to 11.50 and take advantage of last committed reads. > > Clearly if you're not doing table scans, then you avoid this potential > problem for the majority of the time, however it doesn't mean that > collisions still will not happen. > > Also sometimes even if you're using an index, on complex queries, you > can only use one index per table, so if your first index limits your > query to 100,000 rows, you then have to do a sequential scan on those rows. > (XPS doesn't have this problem.) > > Again, I strongly suggest a code review of your application. > > HTH > > -G > > > > From: RHabichtsberg@arz-emmendingen.de > > To: informix-list@iiug.org > > Subject: Re: update impossible in spite of row locking > > Date: Wed, 24 Jun 2009 08:02:39 +0200 > > > > 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 > > > > > > > > > > > >@@NL

> From: mpruet1@verizon.net > Subject: Re: update impossible in spite of row locking > Date: Wed, 24 Jun 2009 14:07:07 +0000 > To: informix-list@iiug.org > > Ian Michael Gumby wrote: > > Hey! > > > > If you're locking a row for update/delete and your other thread/app is > > trying to access the row, then you have your blocking condition. > > > > There is no way around the fact that the row will be locked/blocked for > > some period of time. > > The issue it appears is that you need to limit the amount of time it > > takes for the row to be blocked. > > > > That's the key. Look at the application(s) and see what is in the > > transaction and how long you have to hold the delete/update locks. > > Or move to 11.50 and take advantage of last committed reads. Even still, you're focusing on half the problem. No matter how much you improve the engine, there will always be some time (t) that a lock will be held on a row as you change the row. You (IBM) can decrease this to a point where under normal transactional volume you should be ok. However, it doesn't mean that the holding of a lock will not be problematic if the front end is poorly designed. While DBAs struggle to improve efficiency, you will find that refactoring your code will increase efficiency more than what a great DBA can over a good DBA. Inefficient code is a killer. So hire the best of both DBAs and Developers and you'll save money in the long run. Sorry for the soap box message, just that the pointy haired bean counters want to cut corners. _________________________________________________________________ Bing™ 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

Ian Michael Gumby wrote: > > > > From: mpruet1@verizon.net > > Subject: Re: update impossible in spite of row locking > > Date: Wed, 24 Jun 2009 14:07:07 +0000 > > To: informix-list@iiug.org > > > > Ian Michael Gumby wrote: > > > Hey! > > > > > > If you're locking a row for update/delete and your other thread/app is > > > trying to access the row, then you have your blocking condition. > > > > > > There is no way around the fact that the row will be locked/blocked > for > > > some period of time. > > > The issue it appears is that you need to limit the amount of time it > > > takes for the row to be blocked. > > > > > > That's the key. Look at the application(s) and see what is in the > > > transaction and how long you have to hold the delete/update locks. > > > > Or move to 11.50 and take advantage of last committed reads. > > Even still, you're focusing on half the problem. > > No matter how much you improve the engine, there will always be some > time (t) that a lock will be held on a row as you change the row. I don't want to vote for bad application code, but I believe Madison's point was that the existence of the lock will not matter for people reading the row or index entry... It would of course still matter if you also want to change the same row. IDS 11.50 also facilitates the use of "optimistic locking", meaning you can easily test if the row was changed when you try to update it. So the classical "seat reservation" application would not block the row. This can be done in any database or any IDS version but it's easier and lighter to do on IDS 11.50. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

On 24 June, 14:38, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > Hey! > > If you're locking a row for update/delete and your other thread/app is trying to access the row, then you have your blocking condition. > > There is no way around the fact that the row will be locked/blocked for some period of time. > The issue it appears is that you need to limit the amount of time it takes for the row to be blocked. > > That's the key. Look at the application(s) and see what is in the transaction and how long you have to hold the delete/update locks. > > Clearly if you're not doing table scans, then you avoid this potential problem for the majority of the time, however it doesn't mean that collisions still will not happen. > > Also sometimes even if you're using an index, on complex queries, you can only use one index per table, so if your first index limits your query to 100,000 rows, you then have to do a sequential scan on those rows. > (XPS doesn't have this problem.) > > Again, I strongly suggest a code review of your application. > > HTH > > -G > > > > > From: RHabichtsb...@arz-emmendingen.de > > To: informix-l...@iiug.org > > Subject: Re: update impossible in spite of row locking > > Date: Wed, 24 Jun 2009 08:02:39 +0200 > > > Sorry, yes the table def is with lock mode row. > > > > -----Original Message----- > > > From: informix-list-boun...@iiug.org > > > [mailto:informix-list-boun...@iiug.org]On Behalf Of theBP > > > Sent: Tuesday, June 23, 2009 3:52 PM > > > To: informix-l...@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-boun...@iiug.org > > > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Ian > > > Michael Gumby > > > > Sent: Tuesday, June 23, 2009 2:19 PM > > > > To: th...@usenet-news.net; informix-l...@iiug.org > > > > Subject: RE: update impossible in spite of row locking > > > > >> From: th...@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&cre... > > > _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-l...@iiug.org