Blog › ICP guides

Informix developer on retainer: isolation levels, IBM Informix 4GL, Informix IDS, and INFORMIX-SQL on monthly retainer

October 7, 2026 · ~14 min read

An Informix developer was maintaining a manufacturing order management application written in Informix 4GL (I4GL) for a precision parts manufacturer. The application included a nightly order totals report that joined three tables — ORDER_HEADER, ORDER_LINE, and INVENTORY — to produce per-order quantity and value summaries. To avoid lock waits during the peak evening shift when order entry was still active, the developer had added SET ISOLATION TO DIRTY READ at the top of the report program. On two consecutive nights, the report produced wrong order totals for 2 orders: each showed a total quantity and value that did not match the confirmed order record in the system. The discrepancies were 15–20% lower than the correct values. The orders themselves were correct in the database — querying the same tables the next morning produced correct totals.

The root cause was the interaction between SET ISOLATION TO DIRTY READ and Informix IDS’s multi-table INSERT behavior under an active transaction. The order entry application, written in ESQL/C, used a BEGIN WORK / COMMIT WORK transaction to insert a new order: first an INSERT INTO ORDER_HEADER, then a series of INSERT INTO ORDER_LINE statements for each line item (one per SKU), then a series of UPDATE INVENTORY statements to reduce available stock. The entire order was wrapped in one BEGIN WORK / COMMIT WORK block to ensure atomicity. When the report program ran — at DIRTY READ isolation — while a concurrent order entry session was mid-transaction (ORDER_HEADER inserted, first ORDER_LINE inserted, remaining ORDER_LINE rows and INVENTORY updates not yet inserted), the report’s JOIN read the committed ORDER_HEADER row, read the committed first ORDER_LINE row, but did not see the remaining ORDER_LINE rows (they had been written to the IDS data buffer but their index entries were not yet finalized). The JOIN returned a partial view of the in-flight order: one line item instead of the seven that the completed order would contain. The report aggregated that partial row set and produced a wrong order total.

The root of the problem is what “dirty read” means at the Informix IDS page level. Informix’s DIRTY READ isolation level does not wait for any locks. It reads data pages directly, including pages that are part of in-flight, uncommitted transactions. A row that has been inserted by a concurrent session but not yet committed is visible at DIRTY READ if its data page has been written (even if its index entries are not yet consistent). A concurrent multi-row INSERT inside a BEGIN WORK block writes individual rows to data pages incrementally as each INSERT executes; those rows are individually visible at DIRTY READ before the transaction commits. For a single-table query, this produces phantom rows (rows that may be rolled back if the concurrent transaction aborts). For a multi-table JOIN, this produces partial-result rows: the JOIN sees the header row and some (but not all) of the detail rows, because the detail rows are being inserted one at a time and the report ran between two of the INSERTs.

The fix was to change the report’s isolation level from SET ISOLATION TO DIRTY READ to SET ISOLATION TO COMMITTED READ. At COMMITTED READ, the report’s JOIN waits for any row-level lock held by a concurrent transaction before reading that row. When the order entry session holds a transaction lock on the ORDER_LINE rows it is inserting, the report’s cursor waits until the transaction commits (releasing the locks), then reads the full committed ORDER_LINE result set. The wait is brief — the order entry session holds the transaction lock for only the duration of the INSERT sequence, typically a few hundred milliseconds. The 2–5 second delay that the developer had been trying to avoid with DIRTY READ was not from row-level read locks but from table-level lock escalation caused by a separate index rebuild job running concurrently; COMMITTED READ resolved the wrong-total problem without reintroducing the original lock-wait delay. Wrong order totals: 2 → 0 after changing to SET ISOLATION TO COMMITTED READ. The investigation — correlating the report run timestamps with concurrent order-entry session BEGIN WORK / COMMIT WORK windows using IDS onstat -g ses output, identifying the partial-row JOIN as the source of the wrong totals, and understanding the difference between DIRTY READ and COMMITTED READ at the IDS page level — took 3 hours.

Informix, IDS architecture, and the isolation level model

Relational Software Inc. (later renamed Informix Software Inc.) developed Informix in 1980 as a relational database for Unix systems. IBM acquired Informix in 2001 and continues to develop it as IBM Informix. The current release, IBM Informix IDS 14.10, is the enterprise-grade engine; Informix IDS 12.10 remains in widespread production deployment. The Informix platform encompasses the IDS database engine (a high-concurrency, high-availability OLTP database with optional in-memory acceleration via the IBM Informix In-Memory Database Accelerator), the Informix 4GL application language (I4GL: a procedural 4GL for interactive and batch data processing, with built-in screen forms, window management, and embedded SQL), INFORMIX-SQL (the interactive query and report tool), ESQL/C (embedded SQL in C programs, precompiled by the Informix esql preprocessor), Stored Procedure Language (SPL, for writing stored procedures and functions that execute inside the IDS engine), the IBM Informix CSDK (C client driver for ESQL/C, ODBC, and CLI access), and the IBM Informix JDBC Driver for Java access. Informix IDS uses a space-based storage architecture: a server instance manages one or more dbspaces, each consisting of one or more chunks (raw disk partitions or cooked file paths). Tables are allocated within dbspaces; large object data (TEXT, BYTE, smart blobs) lives in sbspaces. The onconfig file is the IDS server configuration parameter file, governing memory (BUFFERPOOL, SHMTOTAL), logging (LOGFILES, LOGSIZE, PHYSFILE, PHYSBUFSIZE), and concurrency (DEADLOCK_TIMEOUT, LOCK_TIMEOUT, TXTIMEOUT) parameters. The online.log file is the primary IDS server event log.

Informix IDS implements four isolation levels, set per session via SET ISOLATION TO <level> in SQL or Informix 4GL. DIRTY READ acquires no locks at all: the query reads data pages directly, including pages being written by uncommitted concurrent transactions. Dirty Read can return rows that will subsequently be rolled back, partial multi-row states from in-flight INSERTs, and field values that were written and then updated within a single transaction. There is no waiting, no lock conflict, and no error at Dirty Read — it is the fastest possible isolation but provides no consistency guarantee for data shared with concurrent write transactions. COMMITTED READ waits for row-level exclusive locks held by concurrent write transactions before reading a row, ensuring that only committed data is returned. Committed Read is the Informix IDS default isolation level. It prevents dirty reads but allows non-repeatable reads (a row read twice within the same cursor scan may return different values if a concurrent session committed an update between the two reads) and phantom rows (a range query re-executed within the same scan may return different rows if concurrent sessions inserted or deleted rows in the range). CURSOR STABILITY holds a shared lock on the current row of a cursor for the duration of the FETCH; the lock is released when the cursor advances to the next row. This prevents other sessions from updating the current row while the cursor is positioned on it, but it does not prevent updates to rows the cursor has already passed. Cursor Stability prevents non-repeatable reads for the currently positioned row only. REPEATABLE READ holds shared locks on all rows read during the transaction (not released until the transaction ends with COMMIT WORK or ROLLBACK WORK). This prevents non-repeatable reads and phantom rows for all rows accessed during the transaction, but it holds more locks for longer, increasing the risk of lock contention and deadlocks in high-concurrency environments.

Informix 4GL (I4GL) is the primary application programming language for legacy Informix applications. I4GL programs have a .4gl extension and are compiled by the fglpc compiler into .4go object files, linked by fglld into an executable. I4GL provides: DEFINE for variable declaration (DEFINE v_id INTEGER, DEFINE v_name CHAR(50)); BEGIN WORK / COMMIT WORK / ROLLBACK WORK for transaction management; DECLARE cursor CURSOR FOR SELECT ... and FOREACH cursor INTO v1, v2 ... END FOREACH for result set iteration; SELECT ... INTO v1, v2 for single-row retrieval; INSERT, UPDATE, DELETE for DML; PREPARE stmt FROM sqlstring and EXECUTE stmt USING v1, v2 for dynamic SQL; INPUT BY NAME ... AFTER FIELD ... ON KEY ... END INPUT for interactive form data entry; DISPLAY ... TO field and DISPLAY ARRAY ... TO screen_array.* for form output; and WHENEVER ERROR CALL errorHandler for error routing. I4GL programs interact with a screen form defined in a .per (Perform) file; the form defines the screen layout and field types. The ISAM error code and the SQL status variable SQLCA.SQLCODE (also accessible as STATUS in I4GL) carry error information after each SQL statement. STATUS = 0 indicates success; STATUS = NOTFOUND (the Informix constant for SQL NOT FOUND, value 100) indicates no rows matched; negative STATUS values indicate errors.

Informix lock behavior interacts with isolation level in ways that are distinct from most other relational databases. At COMMITTED READ and higher isolation levels, Informix uses row-level locking by default. However, Informix can automatically escalate row-level locks to page-level locks when a transaction holds more than a configurable number of row locks (the DEF_TABLE_LOCKMODE onconfig parameter controls the default lock mode per table; lock escalation thresholds are governed by LOCKS and the per-table LOCK MODE clause in the CREATE TABLE statement). Page-level locks in Informix lock the entire 2 KB or 4 KB data page, which may contain multiple rows from the same table. An exclusive page lock held by a concurrent INSERT can block a report query at COMMITTED READ even for rows on the same page that have not been modified — this is the lock wait the developer was trying to avoid with DIRTY READ. The correct solution for mixed OLTP + reporting workloads is not DIRTY READ but rather a combination of COMMITTED READ for the reporting query with careful table-level lock mode configuration (ALTER TABLE ORDER_LINE LOCK MODE (ROW) to force row-level locking regardless of lock count) and scheduling the report to run outside the peak order-entry window.

Informix developer retainer rates span a wide range by experience level. Entry-level Informix developers typically bill at $65–$120 per hour. Mid-level Informix programmers with IDS performance tuning, isolation level expertise, and stored procedure optimization experience typically bill at $100–$175 per hour. Senior Informix developers with deep IDS internals knowledge, Informix TimeSeries extension expertise, HDR high-availability replication administration, and Informix migration experience typically bill at $145–$260 per hour. Monthly retainer engagements range from $1,700–$3,100/mo for advisory (15–22 hours) to $2,300–$5,500/mo for active maintenance.

Typical Informix retainer work and what it looks like in a work log

DIRTY READ wrong-result bugs are the most invisible category of Informix retainer work. The opening scenario is representative: a report that runs with SET ISOLATION TO DIRTY READ against tables actively written by concurrent OLTP sessions. In development and test environments, report programs are typically run in isolation — no concurrent sessions are inserting orders, updating inventory, or modifying the tables that the report reads. The SET ISOLATION TO DIRTY READ setting produces identical results to COMMITTED READ in a non-concurrent environment because there is no concurrent transaction data to read dirty. The report produces correct totals in every test run. The wrong results only appear in production during the overlap between the nightly report run and the peak order-entry shift, when concurrent OLTP sessions are actively writing multi-row orders. Work log entry: “rpt_orders.4gl: SET ISOLATION TO DIRTY READ; report JOIN of ORDER_HEADER + ORDER_LINE + INVENTORY ran concurrent with OLTP BEGIN WORK; partial ORDER_LINE rows visible (first INSERT committed at page level, remaining INSERTs in-flight); wrong order totals: 2 → 0 via SET ISOLATION TO COMMITTED READ; confirmed via onstat -g ses correlation with OLTP session transaction windows; 3h.”

I4GL interactive transaction lock hold bugs are the second most common Informix retainer pattern. In Informix 4GL, a BEGIN WORK statement starts a database transaction that remains active until an explicit COMMIT WORK or ROLLBACK WORK. An I4GL program that issues BEGIN WORK, then displays a form for user input (via INPUT or CONSTRUCT), holds the transaction lock for the entire duration of the user’s form interaction — which may be seconds, minutes, or indefinitely if the user walks away from their terminal. During this time, any rows locked by the transaction’s DML (or by the CURSOR STABILITY cursor scan used to prefetch data into form fields) are unavailable to concurrent sessions. A batch job that needs to update the same ORDER_HEADER row that the interactive user has open (at CURSOR STABILITY, the I4GL user’s cursor holds a shared lock on the current row) will wait until the user commits or rolls back their form session. In peak-hours environments, multiple interactive users each holding 2–5 minute transaction locks on different ORDER_HEADER rows can cascade into lock waits that block the nightly batch entirely. Fix: restructure the I4GL program to perform the BEGIN WORK only when the user confirms the form (after INPUT completes), not before form display. Batch blocked: 1 nightly job → 0 after transaction scope restructuring. Work log entry: “order_entry.4gl: BEGIN WORK before INPUT BY NAME; CURSOR STABILITY lock on ORDER_HEADER held during user think time (avg 4 minutes); nightly batch blocked waiting for lock release; restructured: moved BEGIN WORK to after ON ACTION accept confirmation; batch blocked: 1 job → 0; 2.5h.”

Stored procedure SPL exception propagation bugs are the third common Informix retainer pattern. An Informix stored procedure written in SPL (Stored Procedure Language) may use ON EXCEPTION blocks to catch specific SQL errors. If the procedure catches an error but does not re-raise it (via RAISE EXCEPTION), the procedure returns normally to its caller with STATUS = 0 even though a SQL error occurred internally. The calling I4GL program checks STATUS after the procedure call, finds 0, and proceeds as if the procedure succeeded. Data modifications inside the procedure that occurred before the caught exception may or may not have been rolled back, depending on whether the exception handler included a ROLLBACK WORK. If the procedure was called from within a BEGIN WORK / COMMIT WORK block in the calling I4GL program, the procedure’s statements executed as nested transactions — the ROLLBACK WORK inside the procedure rolls back only the procedure’s own statements, not the caller’s outer transaction. The calling I4GL program then issues COMMIT WORK on an outer transaction that contains partial data (some procedure statements committed before the exception, the exception-triggered rollback inside the procedure, and subsequent caller DML). Work log entry: “update_inventory_proc.spl: ON EXCEPTION caught ISAM error -268 (constraint violation) without RAISE EXCEPTION; returned STATUS=0 to caller; caller COMMIT WORK committed partial inventory update (3 of 7 SKUs updated, 4 not updated); wrong inventory counts: 4 rows → 0 after adding RAISE EXCEPTION in ON EXCEPTION handler; 2h.”

How HourTab tracks Informix retainer hours

Informix DIRTY READ retainer work is invisible by the same mechanism that makes the isolation level bug dangerous: in development and test environments, reporting programs run without concurrent OLTP write sessions modifying the same tables. The developer verified that the report’s JOIN of ORDER_HEADER + ORDER_LINE + INVENTORY produced correct totals; that the SET ISOLATION TO DIRTY READ setting did not cause any errors or warnings; and that the report ran faster than with COMMITTED READ (because DIRTY READ avoided all lock waits in the test environment). All three behaviors tested correctly in isolation. The compound failure — DIRTY READ exposing a partial-row mid-transaction INSERT state from a concurrent OLTP session — only appeared in production when the report run time overlapped with active order-entry sessions inserting multi-row orders inside BEGIN WORK / COMMIT WORK blocks. Informix IDS does not log DIRTY READ reads of uncommitted rows, does not produce any warning when a DIRTY READ query returns in-flight data, and does not distinguish in any log or error message between a DIRTY READ result and a COMMITTED READ result. The developer must correlate the report run timestamp with IDS session activity from onstat -g ses and reconstruct the concurrent transaction timeline to identify that DIRTY READ exposed a partial-INSERT state.

The work log needs to name the mechanism: which I4GL program, the isolation level (SET ISOLATION TO DIRTY READ), the query structure (JOIN across ORDER_HEADER + ORDER_LINE + INVENTORY), the concurrent transaction scenario (order entry session in mid-INSERT of multi-row order during report run), the partial-row result (1 ORDER_LINE row visible instead of 7), and the wrong-total count before and after (2 → 0). A log entry that says “fixed report wrong totals, 3h” is not auditable. A log entry that names the I4GL program, the DIRTY READ isolation level, the concurrent BEGIN WORK INSERT scenario, the IDS onstat -g ses correlation, and the 2 wrong order totals that were eliminated is auditable and defensible. HourTab gives Informix developers a public retainer-hours URL they send to clients — manufacturing companies, distribution firms, and government agencies running legacy order management and inventory applications built in Informix 4GL and INFORMIX-SQL in the 1980s and 1990s, maintained today on IBM Informix IDS 12.10 or 14.10 on Linux. Comparative context: Informix DIRTY READ isolation level bugs have structural overlap with adjacent database concurrency issues on other legacy platforms. ColdFusion retainers cover SESSION scope race conditions where concurrent AJAX requests overwrite shared session data — both are concurrency visibility bugs where the developer deliberately bypassed a protection (cflock or COMMITTED READ) to avoid a perceived performance cost, producing data integrity failures under concurrent workloads. Progress OpenEdge retainers cover implicit transaction scope bugs where ABL’s per-statement auto-commit leaves record groups in partially committed states after batch interruptions — both involve partial-write states that are only visible when an external event (concurrent transaction or batch interruption) exposes a boundary condition not covered by sequential testing.

FAQ: Informix developer retainers