Informix Error -107

Reasons / Common Causes

-107 is the record-level counterpart to -106's table-level exclusivity conflict: another session's lock on the specific row (or, under lock escalation, the whole table) is blocking your session's request. It is, by a wide margin, the single most common lock-conflict error in normal OLTP operation — understanding why it's so common is more useful than treating any one occurrence as a bug to root-cause.

1. The default lock mode is NOT WAIT. Unless a session explicitly sets otherwise, Informix does not wait for a locked row — it returns -107 immediately. Many applications are written as though the engine will wait, and are surprised the first time real concurrent traffic exposes that assumption.
2. Two sessions touching the same row at close to the same time — the ordinary, expected shape of concurrent OLTP traffic. Under NOT WAIT, whichever session gets there second gets -107, not a queued wait.
3. A long-running transaction holding row locks well past when the work that justified them finished — a transaction that does its writes early but doesn't commit until much later (waiting on user input, an external API call, or unrelated application logic) holds those locks the entire time, turning what should be a brief window of contention into a long one.
4. Lock escalation from row-level to page- or table-level locking, usually from a large batch operation touching many rows in one transaction, or from the engine's lock table running out of room (see #6 below) — once escalated, unrelated rows on the same table start colliding too, making -107 appear far more widely than the actual conflicting operation would suggest.
5. A batch/ETL job holding locks across a large multi-row update while OLTP traffic tries to touch overlapping rows — common when a maintenance job isn't scoped to run in smaller, frequently-committed chunks.
6. Lock-table exhaustion (the LOCKS configuration parameter set too low for actual peak concurrent usage) forcing the engine toward table-level locking as a fallback, which is a much coarser and more contention-prone mode than ordinary row locking.
7. Application error handling that doesn't implement the documented retry contract — code that either doesn't catch -107 at all (surfacing a hard failure to the end user for an ordinary, expected condition) or catches it without actually retrying.

Solutions / Resolution

1. Retry with a short backoff. This is expected, ordinary behavior in a concurrent system — treat -107 as retryable, not as a failure to propagate to the end user on the first occurrence.
2. Consider SET LOCK MODE TO WAIT [seconds] instead of relying on the default NOT WAIT — the official guidance calls this the simplest recovery approach for SQL programs, and it's often the right default for OLTP workloads where a bounded wait beats an immediate failure.
3. For C-ISAM programs, if the operation genuinely requires exclusive access rather than tolerating ordinary row-level contention, request it explicitly with ISEXCLLOCK rather than repeatedly colliding with incidental locks.
4. Keep transactions short — commit as soon as the work that needs the lock is done; don't hold a transaction open across user think-time, external API calls, or unrelated application logic that doesn't need the same lock.
5. Scope batch/ETL updates into smaller, frequently-committed transactions instead of one large multi-row update, so locks are held briefly rather than for the job's entire duration — or use LOCK TABLE ... IN EXCLUSIVE MODE / the DATABASE statement's EXCLUSIVE clause for genuinely single-user maintenance windows, scheduled around active traffic.
6. Check and tune the LOCKS configuration parameter if lock-table exhaustion is implicated — see Diagnostic Checks for confirming this before changing it.
7. Make sure application error handling actually implements the retry contract — rollback the current transaction, wait briefly, retry — rather than swallowing -107 into a generic failure or surfacing it to the user as though it were a real error condition.

Examples

The ordinary concurrent-update collision

-- Session A                              -- Session B
UPDATE inventory SET qty = qty - 1
  WHERE sku = 'WIDGET-1';
-- succeeds, transaction not yet committed

                                           UPDATE inventory SET qty = qty - 1
                                             WHERE sku = 'WIDGET-1';
                                           -- -107: row locked by session A

-- Session A commits
COMMIT;
                                           -- Session B retries, now succeeds

Nothing was wrong with either session — this is the expected shape of two concurrent updates to the same row. Retrying B is the correct response, not investigating a bug.

Choosing to wait instead of failing fast

SET LOCK MODE TO WAIT 5;   -- wait up to 5 seconds for a lock instead of failing immediately

UPDATE inventory SET qty = qty - 1 WHERE sku = 'WIDGET-1';
-- if session A's transaction commits within 5 seconds, this succeeds
-- without the application needing its own retry loop

This shifts the "wait and retry" logic from the application into the engine — appropriate when the typical hold time is short and predictable.

Batch job causing widespread, unexpected -107s

-- A nightly job updates 500,000 rows in one transaction, holding locks
-- (or escalating to table-level) for the job's entire multi-minute run
UPDATE orders SET archived = 1 WHERE order_date < TODAY - 365;

If this runs during business hours, or without committing in smaller batches, OLTP sessions touching completely unrelated rows in orders can start seeing -107 for the job's whole duration — the fix is restructuring the batch job (smaller committed chunks, or an off-hours window), not chasing individual -107s from the OLTP side.

Diagnostic Checks

1. Check current locks and their holders:

   onstat -k
   

   This shows which session holds a lock on the row/table in question — the fastest way to confirm whether this is an ordinary transient collision or a session that's held a lock far longer than expected.
2. Check lock-table usage if -107 is unusually widespread rather than isolated to a specific hot row:

   onstat -p
   

   Look for lock-table overflow or a usage count near the configured LOCKS limit — that points at escalation (Reasons #4/#6) rather than ordinary row-level contention.
3. Confirm the session's lock mode — check whether the application sets SET LOCK MODE TO WAIT or relies on the (colliding-prone) default NOT WAIT.
4. Correlate spikes in -107 with batch/ETL job schedules — a widespread, time-bounded spike that lines up with a known nightly job points at the job's transaction scoping, not application code.
5. Review the LOCKS onconfig parameter against actual peak concurrent lock usage from onstat -p if escalation is suspected, before assuming any single query is at fault.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The other foundational ISAM-level error in this family.
• -106 — "ISAM error: non-exclusive access." The table-level sibling of this same lock- conflict family — -106 is about needing exclusive access to a whole file/table (typically for DDL); -107 is about a specific row already locked by someone else (typically ordinary concurrent DML). Both are usually transient and best handled with retry logic rather than treated as bugs.

If -107 is isolated to specific rows during normal traffic, it's almost certainly not a bug — build retry logic and move on. If it's widespread and correlates with a batch job or a lock-table usage spike, look there instead of at the individual queries reporting it.