Creating index ONLINE
On Informix 11.7, the poster found that running CREATE INDEX ... ONLINE caused concurrent SQL against the same table to fail with error 244 "Could not do a physical-order read" / ISAM error 113 "file is locked". Responders explained this isn't an abort: an online index build still needs very brief exclusive/buffer locks when updating the table structure, so sessions running with lock mode set to NOT WAIT (zero wait) fail immediately. The advice was to use SET LOCK MODE TO WAIT (even 1 second is usually enough) in the applications, and the poster confirmed this solved the problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix idc11.7FC8W1: when running create index statement using the ONLINE parameter , SQL executing on that same table will abort. Is this normal or is it a bug?
In my opinion, a bug. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Sep 7, 2016 at 7:49 AM, ZANELE MASANGO <masangozanele@gmail.com> wrote: > Informix idc11.7FC8W1: > > when running create index statement using the ONLINE parameter , SQL > executing > on that same table will abort. Is this normal or is it a bug? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1147062213ccf8053be9d534
What is the error? On Wed, Sep 7, 2016 at 1:49 PM, ZANELE MASANGO <masangozanele@gmail.com> wrote: > Informix idc11.7FC8W1: > > when running create index statement using the ONLINE parameter , SQL > executing > on that same table will abort. Is this normal or is it a bug? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c030be2c60092053be9f340
This is the error message.
244: Could not do a physical-order read to fetch next row.
113: ISAM error: the file is locked.
-The SQL is in the process of executing when I try to create the index.
Ahh, that's different from an "abort". You need to SET LOCK MODE TO WAIT
<nsecs>; in your applications. The locks an ONLINE index build sets are not
long lasting.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Sep 7, 2016 at 8:41 AM, ZANELE MASANGO <masangozanele@gmail.com>
wrote:
> This is the error message.
>
> 244: Could not do a physical-order read to fetch next row.>
> 113: ISAM error: the file is locked.>
> -The SQL is in the process of executing when I try to create the index.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01229126ff09a3053beadd85
The index build ONLINE doesn't require an exclusive lock as without the
ONLINE keyworl
But whenit needs to change the structure definition, it must have exclusive
access for an instant.
If you have a process constantly trying to access the table there's margin
for the error you're seeing.
But the index should have been built. Was it?
Or eventually you have long standing accesses to the table which prevent
the index creation to momentarly access the strucutre in exclusive mode.
In this case the index creation may prevent newer locks but I'd have to
check this.
In case of doubt and if you can reproduce the situation, open a PMR to get
clarification
Regards
On Wed, Sep 7, 2016 at 2:41 PM, ZANELE MASANGO <masangozanele@gmail.com>
wrote:
> This is the error message.
>
> 244: Could not do a physical-order read to fetch next row.>
> 113: ISAM error: the file is locked.>
> -The SQL is in the process of executing when I try to create the index.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a1145f732017bf3053beb3f1d
More often than not, you get this error because there was a buffer lock on the
page that was being accessed and lock wait was set to zero. Since a buffer
lock is held for an incredibly short period, I suspect that by just setting
the lockwait time to 1 second would address the problem.
Madison Pruet
Retired and Loving it
On Wednesday, September 7, 2016 7:41 AM, ZANELE MASANGO
<masangozanele@gmail.com> wrote:
This is the error message.
244: Could not do a physical-order read to fetch next row.
113: ISAM error: the file is locked.
-The SQL is in the process of executing when I try to create the index.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hey Guys, Thank you all for your help. I am manage to solve the problem. As subjected I used SET LOCK MODE TO WAIT and it worked. Thank you very much.