Re: Alter Table non-exclusive
On Aug 18, 7:14 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz- emmendingen.de> wrote: > Fernando, > > we have DIRTY READERS on the tables, yes, we have a "mixed system" with > OLTP and decision support with queries which can last days. > > The IFX_DIRTY_WAIT variable may help for short dirty reads. I have to > think about it and will do some tests. Meanwhile I thank you for your > help. > > Reinhard. > > From: Fernando Nunes [mailto:domusonl...@gmail.com] > Sent: Thursday, August 18, 2011 2:02 PM > To: Habichtsberg, Reinhard > Cc: informix-l...@iiug.org > Subject: Re: Alter Table non-exclusive > > We're having a semantic problem. What you say is true. If an exclusive > lock is needed, than you can't get it while the table is in use. But > that's a general question regarding locks. That's why we have lock > mode... > If the sessions leave open cursors on the tables for a long period than > you'll have issues. > Again, having a downtime in production is a "scary way" of putting it. > Having to hold or interrupt a few sessions for a few seconds is much > more understandable. > As someone else already mentioned, if you're having DIRTY READERS on the > tables the situation is much more complex. > And as I reference in my blog article you can use a small trick to > prevent new sessions of getting into the way: Open a transaction, grant > some privilege to the table and then run the alter inside that > transaction. The grant will place a lock on the table's systable record > and this prevents other sessions (assuming they're in committed read and > lock mode wait) to read the table structure. So the sessions will wait > on systables and not on the table itself. > This is useless for dirty readers, but for those you'll have the > IFX_DIRTY_WAIT variable. > > Meanwhile, while searching interanlly for other things I found a feature > request for this. But the issue is complex and not easy to solve... > meaning there's no compromise that it will be implemented. It just means > that formally IBM knows about this concern. > > Regards. > > On Thu, Aug 18, 2011 at 6:34 AM, Habichtsberg, Reinhard > > <RHabichtsb...@arz-emmendingen.de> wrote: > > If an exclusive lock is required it is impossible to do the job while > the table is in use. And some tables in our environment are in use of > one or more session permanently. So it doesn't matter how long the alter > job lasts. It cannot be accomplished without a downtime of production. > > That is what we (better: my boss) wish(es) to improve. > > @Fernando: To find the sessions that use a table I found a tool in the > IIUG software corner long ago: > > 1 #!/usr/bin/ksh > > 2 # who-access.sh - Find out who is accessing a specified table. > > 3 # > > 4 # Author: Jacob Salomon > > 5 # Date: 07/15/1999 > > 6 # Credits: Original idea from a script by: > > 7 # Rick Bernstein ... > > It's really very usefull and reliable. > > Reinhard. > > From: Art Kagel [mailto:art.ka...@gmail.com] > Sent: Wednesday, August 17, 2011 1:26 PM > To: Habichtsberg, Reinhard > Cc: informix-l...@iiug.org > Subject: Re: Alter Table non-exclusive > > Some things already only require an exclusive lock momentarily. Most > alters that add or drop a column or change it's type, adding or dropping > an index, and others are accomplished in-place or online and do not lock > that table for long. What are you trying to do that's causing you > trouble? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog:http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated > nor those of the entities themselves. > > On Wed, Aug 17, 2011 at 6:17 AM, Habichtsberg, Reinhard > > <RHabichtsb...@arz-emmendingen.de> wrote: > > Hi all > > We have 24 x 7 production with our Informix databases. We all heard of > Informix Server that ran a year or longer without interruption > <http://dict.leo.org/ende?lp=ende&p=Ci4HO3kMAA&search=interruption&trest > r=0x8001> . > > What we wish is the ability to do "Alter tables" and other > administrative tasks that needs "exclusive locks" without this need. > Each change on the table structures which come continuous from our > software developement causes trouble with our production: Workflows has > to be stopped, dialog programs can't run. > > Is there any chance do these tasks without exclusive locks, perhaps in > futur? Or, is there any other way to do these tasks without a production > break? > > TIA, Reinhard. > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... Why do have queries that can last days? Can you either add indexes, denormalize or add most cpus/spindles/ fragments.? How many threads per query are you using now?