Locked tables: how to avoid them

Abstract

You may experience problems with some applications such as Sage Line 500 running on Informix Dynamic Server with High Availability Data Replication (HDR). When running period end or maintenance programs that recreate tables after purging some of the contents, the table may not immediately be available afterwards, creating locked tables and causing the program to crash. This article provides a work-around.

Content

The chain of events is as follows:

A new table is created.
Rows to be retained are inserted.
An index is created.
The next operation fails because the table is locked.

With the default settings, once an index has been created on a primary server, it is sent directly (not via logical logs) afterwards to the HDR secondary server with text appended to the Informix message logs as follows:

Primary

Copy to Clipboard
Syntax HighlighterHH:MM:SS  DR: Sending index database:"owner".table#index : Started
HH:MM:SS  DR: Sending index database:"owner".table#index : Completed.

Secondary

Copy to Clipboard
Syntax HighlighterHH:MM:SS  DR: Receiving index database:"owner".table#index : Started 
HH:MM:SS  DR: Receiving index database:"owner".table#index : Completed.

If the index is large, this can take some time. The table is meanwhile exclusively locked on both primary and secondary, even though control has already been returned to the calling application. Unless the table is empty, if it tries to use the table right away, even to create a second index, it will most likely fail with:

Copy to Clipboard
Syntax Highlighter243: Count not position within a table (owner.table) 
113: ISAM error: the file is locked.

A solution is available from IDS 11.10 onwards. The following command will activate and set a configuration parameter in the $ONCONFIG file so that new pages are instead passed to the secondary via the logical logs when indexes are created on the primary:

Copy to Clipboard
Syntax Highlighteronmode -wf LOG_INDEX_BUILDS=1

The message log then shows:

Copy to Clipboard
Syntax HighlighterHH:MM:SS  Value of LOG_INDEX_BUILDS has been changed to 1.

With the new setting in force, a table is no longer locked and is immediately available for use after an index has been built. You can check whether it is currently enabled as in the example below:

Copy to Clipboard
Syntax Highlighter$ onstat -g ipl

IBM Informix Dynamic Server Version ...
Index page logging status: Enabled
Index page logging was enabled at: 2014/09/29 16:52:22

There are some caveats:

The documentation advises: “If LOG_INDEX_BUILDS is enabled, logical log file space consumption will increase, depending on the size of the indexes”.
It will take longer to create an index due to the logging overhead.
Most importantly, a large index build might fail with “Long transaction aborted” if the database engine decides it has consuming too big a proportion of the total log space available.

Experienced DBAs therefore temporarily disable this parameter during outages for major schema changes with replication stopped.

Note that index page logging is mandatory for the alternative Informix Remote Secondary Server (RSS) replication method.

Conclusion

Provided you have sufficient log space, enabling index page logging can prevent unexpected failures and locked tables during normal running when applications use newly indexed tables with HDR in operation.

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.