Informix Error -106

Reasons / Common Causes

-106 means the ISAM processor was asked to do something that requires exclusive access to a file or table — most commonly adding or dropping an index — and couldn't get it. The official text is explicit that this is usually transient: another session is legitimately using the object right now, and the fix is often just "wait and retry," not a code or schema problem.

For C-ISAM programs:

1. The file wasn't opened with ISEXCLLOCK. Structural operations like isaddindex()/ isdelindex() require the file to have been opened exclusively; if isopen() was called without that flag, the operation is rejected regardless of whether anything else actually has the file open.

For SQL products:

2. A concurrent session has the table open in any lock mode while your session issues ALTER TABLE, CREATE INDEX, DROP INDEX, or another operation that needs an exclusive lock. The official text's own example — a second user altering a table the first user has locked — is the ordinary case, not an edge case.
3. A long-running query, an open cursor, or an idle-in-transaction session left over from earlier work holding a lock on the table well after the application logic that needed it has finished — the lock outlives the work that justified it because a commit, rollback, or cursor close never ran.
4. A race between two DDL operations targeting the same table at close to the same time — only one session can hold exclusive access; the other gets -106.
5. A long-running reporting query (particularly under an isolation level or SET ISOLATION option that holds locks for the query's duration) colliding with a maintenance window it wasn't scheduled around.
6. Replication or HDR/RSS secondary processes holding a reference to the table during their own processing, competing with a maintenance operation on the primary.

Solutions / Resolution

For C-ISAM programs:

1. Open the file with ISEXCLLOCK before calling isaddindex()/isdelindex() or any other operation that requires exclusive access — review the program logic if this isn't already the case.

For SQL products:

2. Since this is normally transient, retry after a short delay rather than treating a single -106 as a hard failure — this applies especially to automated schema-migration tooling, which should have retry-with-backoff around DDL rather than failing a whole migration run on the first lock conflict.
3. Identify and address the actual blocking session if retries keep failing — find who or what holds the table open (see Diagnostic Checks) and either wait for it to finish naturally or, if it's a leaked idle-in-transaction session, have it committed/rolled back and closed.
4. Fix cursor/transaction leaks in application code — if the same blocking pattern recurs from the same application, the root cause is usually a code path that opens a cursor or starts a transaction without a corresponding close/commit on every exit path (including error paths).
5. Schedule DDL during low-traffic windows and coordinate with whoever else might be actively querying or updating the target table, particularly for tables under active reporting load.
6. For a long-running reporting query colliding with maintenance, consider running that report against a replica or a snapshot instead of the primary table during maintenance windows, so reporting and schema changes don't compete for the same lock.

Examples

The ordinary transient conflict

-- Session A
SELECT * FROM customer WHERE region = 'west';   -- long-running report, holds a lock

-- Session B, moments later
ALTER TABLE customer ADD status VARCHAR(20);
-- -106: customer is open (by session A) and this needs exclusive access

Nothing is wrong with either session in isolation — this is two ordinary operations that happen to need the same table at an incompatible lock level at the same moment. Retrying session B's ALTER TABLE once session A's query finishes succeeds without any code change.

The leaked cursor that outlives its purpose

/* ESQL/C: opens a cursor, but an early return on a validation
   failure skips the corresponding CLOSE */
EXEC SQL DECLARE cur1 CURSOR FOR SELECT * FROM orders WHERE id = :order_id;
EXEC SQL OPEN cur1;
if (!validate(order_id)) {
    return -1;              /* leaked: cursor never closed, lock lingers */
}
...
EXEC SQL CLOSE cur1;

Every call that takes the validation-failure branch leaves a lock in place indefinitely (or until the connection itself closes) — a DDL operation against orders run later in the day can fail with -106 for reasons that have nothing to do with current activity.

C-ISAM: missing the exclusive-lock flag

/* Wrong: opened without exclusive access, then asked to add an index */
int fd = isopen("customer", ISINPUT);
isaddindex(fd, &keydesc);
/* -106 — the open didn't request exclusive access */

/* Right */
int fd = isopen("customer", ISINPUT | ISEXCLLOCK);
isaddindex(fd, &keydesc);

Diagnostic Checks

1. Check current lock holders on the target table:

   onstat -g locks
   

   or, for the table specifically:

   SELECT * FROM sysmaster:syslocks WHERE tabname = 'customer';
   

2. Check for idle-in-transaction or long-running sessions:

   onstat -u
   onstat -g ses
   

   A session shown as idle but still holding locks is the signature of a leaked transaction or unclosed cursor, not active legitimate work.
3. For C-ISAM programs, review the isopen() call for the file involved and confirm ISEXCLLOCK is passed before any isaddindex()/isdelindex() call.
4. If the conflict is reproducible on a schedule, correlate the failing DDL's timing against known reporting jobs, batch windows, or replication activity rather than assuming it's random.
5. For migration/schema-management tooling, check whether it already has retry logic around DDL — if -106 causes a hard failure rather than a retry, that's a tooling gap worth fixing independent of whatever caused this particular instance.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The other foundational ISAM-level error in this family, though unrelated in cause — grouped here as background on how this error class is organized.

Because this condition is normally transient, don't over-invest in root-causing a single occurrence — check for a genuinely leaked lock (Diagnostic Checks #2) before assuming there's a recurring problem to fix.