Re: Alter Table non-exclusive

You should not get an ISAM error -106 with LOCK MODE WAIT set with no timeout value! 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 Thu, Sep 1, 2011 at 6:45 AM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > Hi Fernando,**** > > ** ** > > I now had the situation to do an „alter table“ on a highly frequented table > in production system.**** > > ** ** > > I did the following:**** > > **- **Waited for dialog free time (got up very early in the morning > ;-) )**** > > **- **Killed some dialog sessions from users, which didn’t log out** > ** > > **- **Monitored some sessions, which which where connected to the > table in question. I found 6 sessions which were active (you saw current sql > statement changing every second). These are workflow batches which run all > the time and one 4gl-program that produces certain reports.**** > > **- **set IFX_DIRTY_WAIT=300**** > > **- **Ran the following sql commands:**** > > ** ** > > 1 set lock mode to wait;**** > > 2 begin work;**** > > 3 -- lock table table1 in exclusive mode;**** > > 4 grant select on table1 to public;**** > > 5 grant select on table2 to public;**** > > 6 alter table table1**** > > 7 add (column1 char(1))**** > > 8 ;**** > > 9**** > > 10 --lock table table2 in exclusive mode;**** > > 11 alter table table2**** > > 12 add (column1 char(1))**** > > 13 ;**** > > 14**** > > 15 drop view view1;**** > > 16**** > > 17 create view view1**** > > 18 as select**** > > 19 table1.column_x,**** > > ….**** > > 470**** > > 471 grant select on view1 to public ;**** > > 472 grant select on view2 to public;**** > > 473**** > > 474 commit work;**** > > ** ** > > I didn’t believe it before but the „alter table“-statements ran > successfully. The ALTER TABLE of table1 lasted some minutes. I believe > because table1 has 548.675.744 rows fragmented in round robin. I monitored > locks with waiters and found, that the 4gl-program waited for systable which > was locked by the alter table session.**** > > ** ** > > Unfortunately the 4gl-program crashed with an error:**** > > ** ** > > Date: 01.09.2011 Time: 06:32:51 **** > > Program error at "ka_st_sl.4gl", line number 2076. **** > > SQL statement error number -243. **** > > Could not position within a table (informix.table1).**** > > SYSTEM error number -106. **** > > ISAM error: non-exclusive access. ****> > ** ** > > The 4gl-programm ran in modus: lockmode wait. I asked the developer and > controlled the code by myself. The time to wait was not restricted. **** > > ** ** > > How can we avoid the crash of a program under these circumstances?**** > > ** ** > > TIA,**** > > Reinhard.**** > > ** ** > > *From:* Fernando Nunes [mailto:domusonline@gmail.com] > *Sent:* Thursday, August 18, 2011 5:22 PM > *To:* Habichtsberg, Reinhard > *Cc:* informix-list@iiug.org > *Subject:* Re: Alter Table non-exclusive**** > > ** ** > > Don't get me wrong. I think every Informix customer has "dirty readers". > And currently most of the OLTP systems are also used for decision support > queries (or at least for ETL). > In that case, and someone else already explained, IFX_DIRTY_WAIT will > certainly help. But, back to basics, you may have a problem if you have > queries on the affected tables that last for days. I'm saying this because > all this talk comes down to one thing: identify and stop the sessions that > prevent the access. > > For a complete personal overview about this topic check: > > > http://informix-technology.blogspot.com/2006/10/when-exclusive-is-not-really-exclusive.html > > Regards.**** > > On Thu, Aug 18, 2011 at 3:14 PM, Habichtsberg, Reinhard < > RHabichtsberg@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:domusonline@gmail.com] > *Sent:* Thursday, August 18, 2011 2:02 PM**** > > > *To:* Habichtsberg, Reinhard > *Cc:* informix-list@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 < > RHabichtsberg@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 m