Blog › ICP guides

Ingres developer on retainer: RETURN without ROLLBACK in database procedure, Actian Ingres developer, CA Ingres developer on monthly retainer

October 9, 2026 · ~15 min read

An Ingres developer was maintaining a database procedure in Ingres r10s (Ingres 10.1) for a UK local government benefits system at a county council. The benefitdb database served 60 concurrent benefits processors who assessed and processed housing assistance and employment support claims. A database procedure named ProcessBenefitClaim handled the workflow for placing a benefit claim under active review: it issued an UPDATE BenefitClaims SET status = 'Under Review' WHERE claim_id = :p_claim_id statement, which acquired an Ingres exclusive row lock on the BenefitClaims record for the specified claim ID within an open Ingres transaction, and then performed a series of documentation completeness validations — confirming that the required supporting documents were on file, that the claimant’s income documentation met the current threshold for the program, that residency proof was current, and that no prior claims for the same period were already active. When the documentation completeness check at line 28 of ProcessBenefitClaim failed — indicating that the required document count on file was below the required threshold — the procedure executed RETURN -2 to return a documentation failure code to the calling application. The developer who wrote the documentation failure branch understood that the calling application would display a documentation failure message to the processor and that the claim would not be placed under review. What was not accounted for was that RETURN in an Ingres Procedure Language (IPL) database procedure exits the procedure and returns the specified value to the caller — but it does not commit or roll back any open Ingres transaction that was active when the procedure was executing. The UPDATE BenefitClaims at line 12 of ProcessBenefitClaim had acquired an Ingres exclusive row lock on BenefitClaims row claim_id = 23841. After RETURN -2 at line 28, the Ingres connection used by the caller’s session continued to hold that exclusive row lock for the duration of the open transaction on that connection. Other benefits processors attempting to update or review the same claim received Ingres error E_DM004D “Timeout on lock request” after the default 60-second lock timeout expired. Lock timeout errors per morning: 5–8, each requiring an Ingres DBA to kill the blocking session via ipm; 0 after ROLLBACK was added before every RETURN on validation failure paths in ProcessBenefitClaim.

The root cause was the relationship between Ingres Procedure Language’s RETURN statement and the lifetime of Ingres transaction locks on the calling connection. Ingres database procedures execute within the transaction context of the calling connection: when a client application calls ProcessBenefitClaim, the procedure executes SQL statements against the same Ingres connection and the same Ingres transaction that the calling application is using. The UPDATE BenefitClaims statement at line 12 of ProcessBenefitClaim acquires a row-level exclusive lock on the BenefitClaims record for claim_id = 23841 within the calling connection’s active transaction. This lock is held by Ingres for the duration of that transaction — until the calling connection issues either a COMMIT (committing changes and releasing all locks) or a ROLLBACK (discarding changes and releasing all locks). The RETURN statement in Ingres Procedure Language exits the procedure and returns the specified integer return code to the calling application. It has no effect on the calling connection’s transaction state: it does not commit or roll back the calling transaction, and it does not release any row-level locks that were acquired by SQL statements within the procedure. After RETURN -2 exits ProcessBenefitClaim, the calling application receives the -2 return code, displays the documentation failure message to the processor, and waits for the processor’s next action. The calling application’s Ingres connection is still holding an exclusive row lock on BenefitClaims row 23841 from the procedure’s UPDATE statement — invisible to the calling application, invisible to the processor, and invisible to all other benefits processors who are working through the same claims queue.

The single-session test invisibility follows the standard concurrent database lock bug pattern. The developer who wrote ProcessBenefitClaim tested the documentation failure path on a development Ingres instance with a single test session. They called the procedure with a test claim that had an insufficient document count, confirmed that the procedure returned -2, and verified that the calling application displayed the documentation failure message correctly. The development Ingres connection held the exclusive row lock on the test BenefitClaims record after the failed documentation check, but since only one session was connected to the development Ingres database during the test, no other session attempted to UPDATE the same claim record, and no E_DM004D timeout occurred. The development Ingres instance may also have had a lock timeout setting of -1 (wait indefinitely), meaning that even if a second connection had attempted to lock the same row, it would have waited without returning an error, further concealing the lock hold. In production with 60 concurrent benefits processors sharing the Ingres database, the collision became visible during morning processing peaks when multiple processors were working through the same program’s claims queue: one processor’s session held the lock on claim 23841 after a documentation failure, and another processor attempting to review or update the same claim waited 60 seconds before receiving E_DM004D.

Ingres: from the UC Berkeley relational database pioneer to Actian’s enterprise platform

Ingres (Interactive Graphics and Retrieval System) is one of the oldest and most historically significant relational database management systems in the history of computing. The original INGRES system was developed at the University of California Berkeley starting in 1973 by Michael Stonebraker and Eugene Wong, as a research project in relational database theory. The Berkeley INGRES system was one of the first implementations of the relational model proposed by E. F. Codd at IBM, and it influenced virtually every subsequent relational database system — including, directly or indirectly, PostgreSQL, which descends from the Berkeley POSTGRES project that Stonebraker started in 1986 as a successor to INGRES. The original Berkeley INGRES used the QUEL query language rather than SQL; the commercial Ingres product adopted SQL in the early 1980s as the industry standardized on it.

Ingres was commercialized by Relational Technology Inc. (RTI), founded by the Berkeley INGRES team in 1980. RTI later renamed itself Ingres Corporation. Computer Associates (CA) acquired Ingres Corporation in 1994, and the product was maintained and developed under the CA Ingres brand through the late 1990s and 2000s. CA Ingres was open-sourced in 2004 under a dual BSD/commercial license — the open-source release created the Ingres community edition, which allowed organizations to run and maintain CA Ingres without a commercial license. Actian Corporation was formed in 2011 when the former CA Ingres product and team were spun out from CA, acquiring the commercial Ingres product line along with other analytics database technology. Actian continued Ingres development as Actian Ingres 10.x and subsequently as Actian X, adding vectorized query processing for analytics workloads alongside the traditional OLTP Ingres engine. The current active production versions are Actian Ingres 10.2 and Actian Ingres 11.x.

Ingres’s installed base in government and financial services is substantial and geographically concentrated in the UK, Australia, the Netherlands, Belgium, and Scandinavia. UK local government — county councils, district councils, London boroughs, unitary authorities — built large-scale case management, benefits administration, property management, and financial management systems on CA Ingres and CA OpenROAD through the 1990s and early 2000s. Australian state government departments — particularly in transport, land registry, and social services — built large-scale Ingres applications for population-scale data management. These applications are characterized by high concurrency, long operational lifetimes, and complex transaction logic with regulatory compliance requirements. The developer pool for Ingres SQL and Ingres Procedure Language (IPL) is small and concentrated among developers who learned Ingres in the 1990s and have maintained their expertise through ongoing retainer engagements. Ingres retainer developers with deep IPL, Ingres lock management, and Ingres-to-PostgreSQL or Ingres-to-Actian migration expertise are rare and command premium rates.

Ingres transaction management in database procedures: BEGIN TRANSACTION, COMMIT, ROLLBACK, and E_DM004D lock timeout

Ingres Procedure Language (IPL) is the stored procedure language for Ingres database procedures. IPL provides conditional logic (IF/THEN/ELSEIF/ELSE/ENDIF), loops (WHILE, FOR SELECT), variable declaration, exception handling (ON ERROR), and SQL statement execution within a procedure body. Transaction management in Ingres database procedures is explicit: the procedure does not automatically commit or roll back changes on exit. The key transaction management concepts in Ingres IPL database procedures are: Transaction context — Ingres database procedures execute within the transaction context of the calling connection. If the calling application has begun a transaction (explicitly via BEGIN TRANSACTION or implicitly via autocommit-off), SQL statements within the procedure participate in that transaction and acquire locks according to Ingres’s locking rules. COMMIT ends the current transaction on the calling connection, commits all pending changes, and releases all row-level and page-level locks. ROLLBACK ends the current transaction, discards all pending changes, and releases all locks. RETURN in an Ingres IPL procedure exits the procedure and returns the specified integer value to the caller. It has no effect on the calling connection’s transaction state or lock holdings. After RETURN, the calling connection continues its transaction with whatever lock state existed when the procedure exited.

The Ingres error E_DM004D “Timeout on lock request” is the error code returned by the Ingres lock manager when a lock request waits longer than the configured lock timeout period without acquiring the requested lock. The default lock timeout for Ingres connections is 60 seconds: if a connection waits 60 seconds for an exclusive row lock that is held by another connection, the lock request fails and the waiting connection receives E_DM004D. The lock timeout is configurable per session using SET LOCKMODE SESSION WHERE timeout = n (where n is seconds, or 0 for no timeout, or session to inherit the system default). E_DM004D is therefore the visible symptom of a lock hold bug: if a benefits processor’s session receives E_DM004D when attempting to UPDATE a claim, it means that another session has held an exclusive lock on that claim row for at least 60 seconds without committing or rolling back. The lock hold bug is on the other session — the one that held the lock and never released it — not on the session that received E_DM004D.

The ProcessBenefitClaim procedure had four RETURN statements within the transaction scope that lacked ROLLBACK, not one. The documentation completeness check at line 28 was the first to be identified because it was the most frequently triggered in production. A thorough audit of the full procedure revealed three additional RETURN paths that also lacked ROLLBACK: the income threshold check at line 35 (RETURN -3 when the claimant’s reported income exceeded the program maximum), the residency proof check at line 41 (RETURN -4 when the residency documentation was older than the required recency threshold), and the prior claim audit at line 48 (RETURN -5 when a prior claim for the same program period was found active). All four failure paths were logically correct in their intent — each correctly identified a reason to reject placing the claim under review — but each left the BenefitClaims row lock held after exiting the procedure via RETURN without ROLLBACK. The correct fix: ROLLBACK added immediately before every RETURN on every failure path, or (the more structured approach) an explicit ROLLBACK block at the start of a centralized cleanup section that all failure paths flow through before returning. The procedure was refactored to use a flag variable (v_error_code INTEGER4 = 0) set by each validation check, with a single exit block at the end of the procedure that issues ROLLBACK if v_error_code != 0 or COMMIT if v_error_code = 0, before a single RETURN :v_error_code — eliminating the multiple scattered exit points and guaranteeing that every procedure exit issues either COMMIT or ROLLBACK.

Diagnosing an Ingres transaction lock hold requires using Ingres’s lock diagnostic tools to identify the holding session and cross-reference it with the procedure or application code that left the transaction open. ipm (Interactive Performance Monitor) is the primary Ingres diagnostic tool: running ipm on the Ingres server shows all active database sessions with their current transaction state, active SQL, and lock holdings. The Lock Detail screen in ipm lists all active locks with the session ID, database name, table name, lock granularity (row, page, or table), lock mode (X for exclusive, S for shared, IX for intent exclusive), and lock status (GRANTED or WAITING). In the ProcessBenefitClaim incident, ipm showed: session ingres_app_07, database benefitdb, lock mode X on BenefitClaims page containing claim_id = 23841, duration 14 minutes, status GRANTED; sessions ingres_app_12 and ingres_app_19 waiting on the same page, lock mode X, status WAITING. iimonitor provides an alternative diagnostic: connecting to the Ingres server monitor and running show sessions ingres_app_07 displays the session’s transaction status, the last SQL statement executed, and the session’s connection parameters (user, host, application). The show sessions output confirmed that session ingres_app_07 had an active transaction with last SQL UPDATE BenefitClaims SET status = 'Under Review' WHERE claim_id = 23841 — confirming the ProcessBenefitClaim UPDATE as the lock source. Resolution at the time: Ingres DBA killed session ingres_app_07 via ipm remove session ingres_app_07, releasing all locks and allowing sessions ingres_app_12 and ingres_app_19 to proceed. Permanent fix: ROLLBACK added to all four failure paths in ProcessBenefitClaim, procedure refactored to centralized exit block.

Typical Ingres developer retainer work and what it looks like in a work log

Ingres transaction lock not rolled back on RETURN validation failure is the canonical Ingres invisible production lock bug for database procedures with multi-path validation logic that perform UPDATE operations within an open transaction. It appears in the work logs of Ingres retainer engagements for government benefits administration, case management, claims processing, financial management, and property management systems built on CA Ingres r3 through Ingres 10.x and maintained by Actian Ingres 10.2. The work log entry that makes this diagnosable: “Ingres r10s / Ingres 10.1; database benefitdb; procedure ProcessBenefitClaim; Ingres table BenefitClaims, key claim_id = 23841; UPDATE BenefitClaims SET status = 'Under Review' WHERE claim_id = :p_claim_id at line 12 (Ingres exclusive row lock acquired on BenefitClaims row claim_id 23841); documentation completeness check failure at line 28: IF :v_doc_count < :v_required_docs THEN RETURN -2 ENDIF — RETURN -2 without ROLLBACK; Ingres ipm diagnostic: session ingres_app_07, database benefitdb, lock mode X on BenefitClaims page containing claim_id 23841, duration 14 min, status GRANTED; sessions ingres_app_12 and ingres_app_19 waiting, status WAITING; iimonitor show sessions ingres_app_07: last SQL UPDATE BenefitClaims SET status = 'Under Review' WHERE claim_id = 23841, transaction active; E_DM004D timeout errors per morning before fix: 5–8 (each requiring Ingres DBA to kill blocking session via ipm); fix: ROLLBACK added before RETURN -2 at line 28; full procedure audited — 3 additional RETURN paths without ROLLBACK found: income threshold at line 35, residency proof at line 41, prior claim audit at line 48 — all fixed; procedure refactored to centralized exit block with flag variable; E_DM004D timeout errors per morning after fix: 0; 4h.”

Ingres database procedure development and SQL maintenance is the routine day-to-day Ingres retainer task for government and financial services clients who have large bodies of Ingres database procedures supporting their core business workflows and need those procedures updated as program rules change, as data model changes are made, or as new reporting requirements are added. A common engagement for a UK county council: the housing benefits program has added a new documentation category for digital identity verification, which must be included in the documentation completeness check in ProcessBenefitClaim alongside the existing document types, and the new category must be tracked in a new DigitalId column in the BenefitDocuments table. The retainer developer alters the BenefitDocuments table to add the DigitalId column, updates the documentation completeness check in ProcessBenefitClaim to include the new document category in the count query, confirms that the existing four ROLLBACK fix points still cover the updated validation logic, tests the modified procedure against the production data model, and confirms that no E_DM004D regressions are introduced. Work log: “Ingres 10.1; database benefitdb; ALTER TABLE BenefitDocuments ADD COLUMN DigitalId CHAR(50) WITH NULL — schema change applied, Ingres system catalog updated; ProcessBenefitClaim line 26: updated documentation count query (SELECT COUNT(*) INTO :v_doc_count FROM BenefitDocuments WHERE claim_id = :p_claim_id AND (doc_type IN ('IncomeProof', 'ResidencyProof', 'BenefitHistory') OR DigitalId IS NOT NULL)) to include digital identity records; rollback coverage confirmed: all 4 ROLLBACK points in centralized exit block unaffected by query change; tested with 3 test claims (complete set, missing traditional docs, digital-only) — correct return codes (-2 for insufficient, 0 for complete); no E_DM004D in concurrent test with 2 simultaneous test sessions; 3h.”

Ingres version migration and Actian upgrade is the highest-investment Ingres retainer engagement for government clients running CA Ingres r3 or Ingres 6.x applications who need to migrate to Actian Ingres 10.2 for continued OS compatibility and commercial support. The migration assessment covers: deprecated IPL syntax audit (identifying database procedures using Ingres r3 or 6.x IPL syntax that Ingres 10.x does not accept, including deprecated locking syntax and obsolete QUEL-derived constructs); transaction rollback audit (the migration is the correct moment to audit all database procedures for ROLLBACK on every RETURN failure path, fixing any that are found before they become E_DM004D incidents on the new version); Ingres configuration migration (iiconfig parameters for lock timeout, buffer pool size, log file configuration, and security settings must be reviewed and updated for Actian Ingres 10.2); and application layer compatibility (if the database procedures are called from an OpenROAD application, ESQL/C programs, or JDBC/ODBC applications, those callers must be tested against the Ingres 10.2 backend for any API changes in the Ingres client libraries). Work log: “CA Ingres r3 → Actian Ingres 10.2 migration; database benefitdb, claimsdb, financedb; procedure inventory: 187 database procedures across 3 databases; deprecated IPL syntax audit: 12 procedures using deprecated r3 locking hint syntax updated to Ingres 10.x standard (EXECUTE PROCEDURE with SHARE clause removed — locking hints moved to session lock mode configuration); transaction rollback audit: 187 procedures reviewed for ROLLBACK on every RETURN failure path; 23 procedures found with RETURN paths lacking ROLLBACK — all fixed (including ProcessBenefitClaim from prior E_DM004D production incident); iiconfig migration: lock_timeout updated from -1 (development default) to 120 seconds (production appropriate), log file size increased, buffer pool adjusted for 64-bit addressing; OpenROAD 6.2 compatibility test against Actian Ingres 10.2 driver: all 94 frames exercised against production data snapshot — no connection or transaction errors; ESQL/C programs recompiled against Ingres 10.2 ESQL/C header files — 3 API deprecation warnings resolved; migration completed; 48h across 8 sessions.”

How HourTab tracks Ingres developer retainer hours

Ingres transaction lock hold bugs caused by ROLLBACK missing on RETURN failure paths in database procedures are invisible in single-session development testing by the same mechanism that makes all concurrent database lock bugs invisible when only one connection exercises the code at a time. The developer who writes ProcessBenefitClaim tests the documentation failure path on a development Ingres instance with a single test session: they call the procedure with a test claim that has insufficient documents, confirm that the procedure returns -2, and verify that the calling application displays the documentation failure message correctly. The Ingres connection used by the development session holds the exclusive row lock on the test BenefitClaims record after the failed documentation check, but since only one session is connected to the development Ingres database during the test, no other session attempts to UPDATE the same claim record, and no E_DM004D timeout occurs. The development Ingres instance may have a lock timeout of -1 (wait indefinitely), meaning that even if a second test connection had attempted the same lock, it would have waited forever without returning an error. The ipm lock diagnostic would show the exclusive row lock if queried, but the developer never queries ipm during the development test — there is no visible symptom to prompt a diagnostic session. In production with 60 concurrent benefits processors sharing the Ingres database, the collision is visible only when two processors work on related claims within the lock hold window.

The work log entry that makes Ingres lock work auditable must name every element of the causal chain: the Ingres version, the database name, the procedure name, the Ingres table and row key, the UPDATE statement, the RETURN path that lacked ROLLBACK, the ipm and iimonitor lock diagnostic evidence (session ID, database name, table and page, lock mode, duration, blocked sessions), the E_DM004D timeout frequency before and after the fix, and the audit result for additional unprotected RETURN paths in the same procedure. A log that says “fixed Ingres locking issue, 4h” is not auditable to a county council IT manager, a central government digital transformation director, or an Ingres service desk reviewing a change control record for a benefits administration system. A log that names the procedure, the ipm and iimonitor evidence, the specific RETURN statement that lacked ROLLBACK, and the before/after E_DM004D rate is auditable, defensible under FOI and change control review, and builds the trust that sustains a long-term Ingres retainer relationship in public sector environments where system reliability and auditability are statutory requirements. HourTab gives Ingres developers a public retainer-hours URL they send to clients — UK local government, Australian state government, Dutch and Belgian public sector agencies, US government departments, utilities, insurance, and financial services organizations that run large-scale case management, benefits, claims, and financial management systems on CA Ingres and Actian Ingres and maintain them with an Ingres specialist on monthly retainer.

The broader context for Ingres retainer billing is that the RETURN without ROLLBACK pattern in Ingres IPL is the Ingres instantiation of the universal principle that every platform requiring explicit transaction management demands that the developer pair every transaction open with a transaction close on every exit path. OpenROAD developer retainers cover the equivalent at the OpenROAD 4GL frame method layer: a frame method that calls BYREF record access or issues an UPDATE and exits via RETURN on validation failure without ROLLBACK — same Ingres E_DM004D timeout symptom, same invisible single-session test, but originating in the OpenROAD application layer rather than in an Ingres stored procedure. Progress OpenEdge ABL developer retainers cover the equivalent in Progress: a stored procedure or 4GL block that acquires an exclusive record lock with FIND FIRST EXCLUSIVE-LOCK and exits without RELEASE or transaction end — same class of invisible explicit transaction management bug, different platform. Natural developer retainers cover the equivalent in Software AG Natural with Adabas: a Natural subroutine that holds an Adabas record in a hold queue without issuing END TRANSACTION or BACKOUT TRANSACTION on the failure path — same pattern of a developer who tests in single-user mode and never observes the multi-user hold queue collision that their missing rollback creates in production.

FAQ: Ingres developer retainers