Informix Error -143

Reasons / Common Causes

-143 means the database server detected an impending circular wait: two or more concurrent requests each waiting for a resource held by another, such that if all were allowed to proceed, none ever could. The official guidance explicitly links this to -107's treatment — same general remedy shape — but the deeper fix for recurring deadlocks is different: eliminating the circular-wait possibility through consistent design, not just retrying past it.

1. A genuine circular wait between two sessions. Session A holds a lock on resource X and wants Y; Session B holds Y and wants X. Neither can proceed, and the engine detects this and aborts one side rather than letting both wait forever.
2. Inconsistent lock-acquisition ordering across the application. Different code paths (or different parts of the same logical operation) acquiring locks on the same set of resources in different orders creates the structural possibility of a circular wait whenever those paths run concurrently — this is the single most common root cause of recurring deadlocks.
3. Updating related tables in application-dependent order — one code path updating a parent row then a child row, another updating child-then-parent, within transactions that can run concurrently. A very common real-world pattern behind deadlocks in systems with several related tables updated together.
4. Long transactions touching many rows or tables, increasing the surface area (and therefore probability) for a circular wait to form against another similarly long transaction.
5. Index page locking adding lock-ordering complexity beyond what's visible from the application's own row-level operations — two transactions can deadlock over index pages neither was consciously trying to coordinate around.
6. High-contention "hot" rows (a shared counter or sequence-like row updated by many transactions) increasing deadlock likelihood from contention volume alone, even without an inherent ordering bug.
7. In an Informix STAR (distributed) environment, deadlock detection is probabilistic, not deterministic — based on a configurable resource-wait-duration delay across systems, rather than an instantaneous global wait-for graph. A genuine deadlock across systems may take that configured delay to be detected.

Solutions / Resolution

1. Roll back the current transaction and re-execute it after a delay — the official guidance's own recommended remedy, matching -107's shape. The engine has already resolved the deadlock by aborting one participant; the application's job is to retry cleanly.
2. Review and redesign applications that share tables concurrently to minimize deadlock probability. The single most effective structural fix: enforce a consistent lock/resource-acquisition order across every code path touching the same set of tables or rows, so a circular wait becomes structurally impossible rather than merely unlikely.
3. Keep transactions short and scoped to as few rows/tables as necessary — reducing the window and surface area during which a circular wait could form.
4. For STAR/distributed environments, review and tune the resource-wait-duration delay configuration deliberately — too short risks aborting merely slow (not actually deadlocked) transactions; too long delays real deadlock detection and recovery.
5. Build retry-with-backoff logic into applications for deadlock aborts, exactly as for -107 — some level of occasional deadlock, retried cleanly, is often an acceptable trade-off against the engineering cost of eliminating every possible ordering inconsistency.
6. For known hot-row contention patterns, consider alternative designs that reduce lock contention directly — a SERIAL/sequence-based approach instead of a manually incremented shared counter row, for example — rather than only reducing deadlock probability around the existing design.

Examples

The classic circular wait

-- Session A                                  -- Session B
UPDATE accounts SET balance = balance - 100
  WHERE id = 1;                                UPDATE accounts SET balance = balance - 50
                                                  WHERE id = 2;
UPDATE accounts SET balance = balance + 100
  WHERE id = 2;    -- waits: B holds id=2      UPDATE accounts SET balance = balance + 50
                                                  WHERE id = 1;    -- waits: A holds id=1
-- -143: neither can proceed; the engine aborts one side

Both sessions update the same two rows in opposite orders — the structural fix is enforcing a consistent order (e.g., always lock the lower id first) across every code path that updates these accounts together.

Inconsistent update ordering across code paths

Code path 1: UPDATE parent_table ...; UPDATE child_table ...;
Code path 2: UPDATE child_table ...; UPDATE parent_table ...;

Run concurrently often enough, these two orderings eventually deadlock — standardizing on one order (always parent-then-child, everywhere) removes the possibility entirely, rather than just reducing its frequency.

STAR's probabilistic detection

In a distributed Informix STAR setup, a deadlock spanning two systems isn't caught by an instantaneous cross-system wait-for graph — it's detected once a transaction's wait exceeds the configured resource-wait-duration delay. A very short configured delay catches deadlocks faster but risks false positives against transactions that are merely slow; a long delay avoids that but leaves genuine deadlocks unresolved longer.

Diagnostic Checks

1. Review the message log for deadlock detail — which sessions and resources were involved, if the version in use logs that level of detail.
2. Review application code paths that update the same set of tables for consistent lock/resource acquisition order — this is the single highest-value check for a recurring deadlock pattern.
3. For STAR environments, check the configured resource-wait-duration delay setting.
4. Check for known hot rows or tables with high contention correlating with deadlock frequency, if the pattern doesn't point at an obvious ordering inconsistency.

Related Errors / Related Topics

• -107 — "ISAM error: record is locked." The official guidance explicitly ties -143's remedy to -107's — both call for a retry after rollback, though -143 additionally warrants a design review if it recurs, since a true deadlock (unlike ordinary lock contention) usually indicates a fixable ordering inconsistency.
• -134 — "ISAM error: no more locks." A different lock-management condition (capacity exhaustion rather than a circular wait), but part of the same general troubleshooting family — review actual lock usage patterns rather than treating either as a one-off.

If -143 recurs regularly against the same tables, treat it as a design signal — standardize resource-acquisition order across every code path touching those tables — rather than relying on retry logic indefinitely.