Informix Error -239

Reasons / Common Causes

-239 is the SQL-level error most INSERT/UPDATE statements actually surface for a duplicate-key violation — -100 is its ISAM-level counterpart, reached the same way but visible to SQL callers as this code instead. Everything -100 covers in depth (check-then-insert races, SERIAL collisions, retried non-idempotent loads, index-vs-application value-normalization mismatches) applies here identically. This entry focuses on what -239 adds beyond that: a second, distinct cause the official text calls out specifically.

1. A genuine duplicate value in a column or columns constrained to be unique — see -100 for the full range of realistic causes and fixes; they apply here without modification.
2. A locking conflict when the table's lock mode is PAGE. The official text is explicit that this can produce -239 without an actual duplicate value being present — a page-level locking conflict during concurrent access can be reported this way.
3. Repeatable Read isolation interacting with a unique constraint specifically (as opposed to a bare unique index). The official guidance points to -268 as the more relevant, specific code for that scenario.

Solutions / Resolution

1. Roll back the current transaction and re-execute — either without any duplicate rows, or with the locking conflict resolved, per the official guidance.
2. For a genuine duplicate, follow -100's guidance in full — find the existing row, decide whether to overwrite/skip/surface the conflict, fix a SERIAL collision, or fix ETL idempotency, depending on what's actually causing the duplicate.
3. If no genuine duplicate is found, check the table's lock mode. A PAGE lock mode under concurrent access can produce this error as a locking artifact rather than a real data conflict — consider whether row-level locking resolves the false-positive-looking behavior.
4. If running under Repeatable Read isolation, check -268 specifically — a named unique constraint violation under that isolation level is the more precise diagnosis the official guidance directs you toward.

Examples

The genuine duplicate (see -100 for the full range of causes)

INSERT INTO customer (id, email) VALUES (2, 'a@example.com');
-- -239: a@example.com already exists under a UNIQUE INDEX

Diagnose and fix exactly as described under -100 — find the existing row, understand how it got there, and choose the appropriate fix.

A PAGE-lock-mode conflict without genuine duplication

-- Table lock mode is PAGE
-- Two concurrent sessions both insert into the same page,
-- neither actually violating uniqueness, but the page-level
-- lock conflict is reported as -239

Reviewing and potentially changing the table's lock mode (to row-level locking) can resolve recurring -239 reports that don't correspond to any actual duplicate value found on investigation.

Repeatable Read and a named constraint

SET ISOLATION TO REPEATABLE READ;
INSERT INTO orders (order_id, ...) VALUES (...);
-- -239, but the actual condition is more precisely described by -268
-- because a named UNIQUE constraint (not a bare index) is involved

Diagnostic Checks

1. Identify which index or constraint actually raised it, following -100's own diagnostic query against sysindexes.
2. Search for the existing row holding the conflicting value, per -100's guidance.
3. If no duplicate is found, check the table's lock mode — PAGE vs. row-level — as the distinguishing check specific to -239.
4. If under Repeatable Read isolation, review -268 for the constraint-specific framing of this same condition.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The ISAM-level counterpart reached the same way — nearly all of -239's realistic causes and fixes live there.
• -268 — "Unique constraint violated." The named-constraint-specific sibling, particularly relevant under Repeatable Read isolation per the official guidance.

For a genuine duplicate, go straight to -100's extensive guidance — this entry's real value is the PAGE-lock-mode cause and the -268 pointer, both of which -100 doesn't cover.