Table locked after applying foreign key ~ Answered: amber (solid confidence) — Art Kagel gives a precise technical explanation (LOG_INDEX_BUILDS causes the primary to hold locks until the HDR secondary finishes building its own index) and a fix, but the asker only says they will try it, without confirming.

Advisory only.

Hi all, we just encountered a problem with a database, where after execution of a sql script (which succeeded without error) a table was locked exclusively. Setup is a HDR paid using Linux IDS 11.70FC5GE. We added a new table and a foreign key constraint to an existing table, which was not modified. After successful execution, the referenced table was locked in all databases where the script added the foreign key to (multiple, having the same structure). There was no open transaction, sysmaster did not report any locks. On another server, where we applied the same DDL, there was no lock. But that one (same IDS release, same OS) does not have HDR, but is a standalone instance, running on another machine. We tried to set primary server in standard mode, but the lock was still there. online.log did not show any errors (despite of the messages that server was now in standard mode). Only restarting the DB engine did release the lock. After setting primary role again, HDR came up without any error. Anybody encountered this before ? Should we interrupt HDR before applying such a table modification ? Thanks for your help ! Marcus Haarmann

Creating a foreign key creates an index unless one already exists. Unless you have LOG_INDEX_BUILDS set to 1 the HDR secondary will build its own index after the primary finishes, but it will maintain the locks needed for the index build on the primary until the secondary's index finishes. The solution? Set LOG_INDEX_BUILDS to 1 in your onconfig file and bounce the server or set it dynamically using: "onmode -wf LOG_INDEX_BUILDS=1" then the primary will ship the log records needed to create the index on the secondary at the same time the primary does so which will cause the lock on the table to be released almost immediately after the operation completes on the primary. 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 Thu, Jan 23, 2014 at 12:01 PM, Marcus Haarmann <marcus.haarmann@midoco.de > wrote: > Hi all, > > we just encountered a problem with a database, where after execution of a > sql > script (which succeeded without error) > a table was locked exclusively. > Setup is a HDR paid using Linux IDS 11.70FC5GE. > We added a new table and a foreign key constraint to an existing table, > which > was not modified. > After successful execution, the referenced table was locked in all > databases > where the script > added the foreign key to (multiple, having the same structure). > There was no open transaction, sysmaster did not report any locks. > On another server, where we applied the same DDL, there was no lock. > But that one (same IDS release, same OS) does not have HDR, but is a > standalone instance, > running on another machine. > > We tried to set primary server in standard mode, but the lock was still > there. > online.log did not show any errors (despite of the messages that server was > now in standard mode). > > Only restarting the DB engine did release the lock. After setting primary > role > again, HDR came up > without any error. > > Anybody encountered this before ? > Should we interrupt HDR before applying such a table modification ? > > Thanks for your help ! > > Marcus Haarmann > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c234a6cae0bb04f0a6e335

Thanks Art, we will try that. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "Art Kagel" <art.kagel@gmail.com> An: ids@iiug.org Gesendet: Donnerstag, 23. Januar 2014 18:51:29 Betreff: Re: Table locked after applying foreign key [32302] Creating a foreign key creates an index unless one already exists. Unless you have LOG_INDEX_BUILDS set to 1 the HDR secondary will build its own index after the primary finishes, but it will maintain the locks needed for the index build on the primary until the secondary's index finishes. The solution? Set LOG_INDEX_BUILDS to 1 in your onconfig file and bounce the server or set it dynamically using: "onmode -wf LOG_INDEX_BUILDS=1" then the primary will ship the log records needed to create the index on the secondary at the same time the primary does so which will cause the lock on the table to be released almost immediately after the operation completes on the primary. 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 Thu, Jan 23, 2014 at 12:01 PM, Marcus Haarmann <marcus.haarmann@midoco.de > wrote: > Hi all, > > we just encountered a problem with a database, where after execution of a > sql > script (which succeeded without error) > a table was locked exclusively. > Setup is a HDR paid using Linux IDS 11.70FC5GE. > We added a new table and a foreign key constraint to an existing table, > which > was not modified. > After successful execution, the referenced table was locked in all > databases > where the script > added the foreign key to (multiple, having the same structure). > There was no open transaction, sysmaster did not report any locks. > On another server, where we applied the same DDL, there was no lock. > But that one (same IDS release, same OS) does not have HDR, but is a > standalone instance, > running on another machine. > > We tried to set primary server in standard mode, but the lock was still > there. > online.log did not show any errors (despite of the messages that server was > now in standard mode). > > Only restarting the DB engine did release the lock. After setting primary > role > again, HDR came up > without any error. > > Anybody encountered this before ? > Should we interrupt HDR before applying such a table modification ? > > Thanks for your help ! > > Marcus Haarmann > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c234a6cae0bb04f0a6e335 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.