Informix online index creation/deletion mechanism
Hello my friends. I´ve being searching for any documentation about the online index creation/deletion mechanism, but found nothing specific yet. I´d like to know if in any phase of online index manipulation, DML commands are stopped, waited to complete, in order for the index to proceed it´s execution, or any other kind of mechanism Informix does use to complete the operation. Thanks for any information. Regards.
There is a brief lock on systables and sysindices when the index is finally added to the system. Might affect clients that do not have SET LOCK MODE TO WAIT set. The locks are transient lasting only a few fractions of a second. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Mon, Apr 14, 2014 at 1:27 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > Hello my friends. > I´ve being searching for any documentation about the online index > creation/deletion mechanism, but found nothing specific yet. > > I´d like to know if in any phase of online index manipulation, DML commands > are stopped, waited to complete, in order for the index to proceed it´s > execution, or any other kind of mechanism Informix does use to complete the > operation. > > Thanks for any information. > Regards. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1132ee802232fe04f7043417
Ok Art, thanks a lot for your explaining. I am comparing with other engines mechanism, that´s why I´m asking, Art. PostgreSQL for example, waits for any transaction to complete, before the index creation, and does a full table scan 2 times, in order to be able to do a concurrently operation. SQL Server seems to do it a little more like Informix, but blocks any DML in the beginning, and at the end of the process. So there is a remote chance of a DML being rejected, only in the end of index creation, while the exclusive lock is being acquired/released, right? Thanks.
That is my understanding, yes. If you place a SET LOCK MODE TO WAIT 1; in the sysdbopen() function all sessions will be protected. The only issue would be if an app handles a lockout error but not a lock timeout error and it was hit by a longer lived lock on some other table. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Mon, Apr 14, 2014 at 1:57 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > Ok Art, thanks a lot for your explaining. > > I am comparing with other engines mechanism, that´s why I´m asking, Art. > PostgreSQL for example, waits for any transaction to complete, before the > index creation, and does a full table scan 2 times, in order to be able to > do > a concurrently operation. SQL Server seems to do it a little more like > Informix, but blocks any DML in the beginning, and at the end of the > process. > > So there is a remote chance of a DML being rejected, only in the end of > index > creation, while the exclusive lock is being acquired/released, right? > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c355b250cca504f7052de9
Ok Art, thanks a lot! Regards.