Informix Error -144: ISAM error: key value locked.

Reasons / Common Causes

-144 is a specific, precisely-defined race: an uncommitted transaction has deleted a row with a given primary key value, and a different transaction — running under NOT WAIT lock mode — tries to insert or update a row to use that same key value before the delete's fate (commit or rollback) is known. Under NOT WAIT, the engine can't yet tell whether the key is truly free, so it reports -144 immediately rather than blocking.

1. A "delete old value, insert replacement with the same key" pattern split across separate concurrent transactions or sessions, rather than done atomically within one transaction — the classic trigger. Between the delete and its commit, the key value is in an indeterminate state for anyone else trying to claim it.
2. Upsert-style logic implemented as delete-then-insert instead of a single UPDATE or a genuinely atomic upsert mechanism — a common pattern that's more race-prone than it needs to be for what's logically a single operation.
3. Batch/ETL reprocessing that deletes and reinserts records with the same key rapidly, racing against other concurrent activity touching the same key space.
4. NOT WAIT lock mode specifically being what surfaces this as -144 — under WAIT mode, the same underlying race would simply block the second session until the first transaction resolves, rather than failing immediately with this code.

Solutions / Resolution

1. Treat this identically to -107: roll back the current transaction and re-execute it after a delay. Per the official guidance, this is the standard remedy — and the outcome of the retry depends on what happened to the original deleting transaction:
   • If it committed, the key is now genuinely free, and the retry proceeds normally.
   • If it rolled back, the key still exists, and the retry now correctly receives a duplicate-key error (-100/-239/-268) instead of -144 — this is expected, correct behavior, not a new bug to chase.
2. Build retry logic that anticipates both outcomes as legitimate, rather than treating a subsequent duplicate-key error after a -144 retry as a surprising failure — it's the accurate reflection of the other transaction having rolled back.
3. Reconsider delete-then-insert as the implementation for what's logically an upsert. Using UPDATE (if the row should simply change while keeping its key), or a genuinely atomic upsert mechanism, narrows or eliminates the window during which this race can occur.
4. Consider SET LOCK MODE TO WAIT for workloads prone to this pattern — instead of an immediate -144, the second session waits for the first transaction's outcome and then naturally proceeds or fails with the correct final state, trading immediate failure for a bounded wait.
5. Keep delete-and-reinsert transactions short and commit promptly, minimizing the window during which this race can affect other concurrent sessions targeting the same key.

Examples

The classic delete-then-reinsert race

-- Session A                                  -- Session B
BEGIN WORK;
DELETE FROM customer WHERE email = 'a@x.com';
-- not yet committed
                                               BEGIN WORK;
                                               INSERT INTO customer (email, ...)
                                                 VALUES ('a@x.com', ...);
                                               -- -144: A's delete hasn't
                                               -- resolved yet

Session B retries after a delay:

-- If A committed in the meantime:
INSERT INTO customer (email, ...) VALUES ('a@x.com', ...);
-- succeeds — the key is genuinely free now

-- If A rolled back instead:
INSERT INTO customer (email, ...) VALUES ('a@x.com', ...);
-- -100/-239: correctly reports a duplicate — the row was never actually removed

Replacing delete+insert with UPDATE

-- Instead of:
DELETE FROM customer WHERE email = 'a@x.com';
INSERT INTO customer (email, name) VALUES ('a@x.com', 'New Name');

-- Prefer, if the key itself isn't changing:
UPDATE customer SET name = 'New Name' WHERE email = 'a@x.com';

A single UPDATE never creates the indeterminate window that a separate delete-then-insert does — there's no moment where another session could observe the key as "in the process of being freed."

Diagnostic Checks

1. Confirm the lock mode (NOT WAIT vs. WAIT) in use for the affected sessions — this determines whether the race surfaces as -144 or as an ordinary wait.
2. Review whether application logic implements upserts via delete-then-insert rather than UPDATE or an atomic upsert mechanism.
3. Check the timing of the deleting transaction's commit or rollback relative to the failing insert/update, to confirm this matches the expected race pattern rather than something else.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The outcome you'll see on retry if the original deleting transaction rolled back rather than committed.
• -107 — "ISAM error: record is locked." The official guidance explicitly ties -144's remedy to -107's — same retry-after-rollback treatment, applied to this more specific delete/insert race condition.

After retrying a -144, a subsequent duplicate-key error isn't a new problem — it's the correct outcome of the original delete having been rolled back rather than committed.