Re: Alter Table non-exclusive

Hmmmm..... The LOCK MODE is a session property. Different parts of the code (or even inside stored procedures), this can be changed and not put back. (By the way, I would love to see some extension to run each query in a particular isolation level and lock mode. I know ANSI doesn't allow it to change - SET TRANSACTION... - but our extension SET ISOLATION... can be used. So imagine a "SELECT .... WITH COMMITTED READ LOCK MODE WAIT 5" ) Besides what you found I don't see any explanation to what happened to you... Maybe others have some clue (although this can explain it...)? Regarding the change of isolation level and lock mode inside "functions" or procedures, this is also an interesting thing. The fact is that sometimes you want/need to change it and it's not easy to find what is the current one, so that you can reposition it once you're done. Sometime ago I dig into this and it's possible to have a procedure that implements it... I thought I had an article about this but I don't seem to find it... Regards On Thu, Sep 1, 2011 at 3:20 PM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > You got me right. **** > > ** ** > > I now monitored the sql of the running program with onstat –g ses sid –r 1. > Under thousands of queries the program produces I found exactely one (by > chance) that ran in modus „not wait“. From the code the program should not > change the lock mode. It should be always „wait“. But this could be the > reason for the crash.**** > > ** ** > > THX, Reinhard.**** > > ** ** > > *From:* Fernando Nunes [mailto:domusonline@gmail.com] > *Sent:* Thursday, September 01, 2011 1:10 PM > > *To:* Habichtsberg, Reinhard > *Cc:* informix-list@iiug.org > *Subject:* Re: Alter Table non-exclusive**** > > ** ** > > Correct me if I'm wrong: > > You managed to do the alter tables easier than you'd usually expect. You > saw some user's 4GL programs waiting while you did the ALTER TABLE(s). > That was the good part. The bad part is that one 4GL program instead of > waiting exited with and error and you want to understand why and how to > avoid it....? > > If the above is correct, I'm not certain about what may have caused it. > Some things to collect: > > 1- The query the 4GL was trying to process > 2- Is the query previously prepared? > > If my assumptions above are not correct, please clarify. > Regards > > **** > > On Thu, Sep 1, 2011 at 11: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:* Habicht