Blog › ICP guides

Oracle PL/SQL developer on retainer: SELECT FOR UPDATE TX lock held after RETURN on validation failure, Oracle Database developer, PL/SQL stored procedure developer on monthly retainer

October 10, 2026 · ~15 min read

An Oracle PL/SQL developer was maintaining the stored procedure VALIDATE_AND_RESERVE_CLAIM in the CLAIMS_PROC schema of a health insurance company’s Oracle Database 19c claims processing system (Oracle Database 19.17.0.0.0, single-instance on Oracle Linux 8.6). Forty-five concurrent claims adjusters accessed the system through a Spring Boot 3.2 web application deployed on Apache Tomcat 10.1, using Oracle JDBC driver ojdbc11 23.3.0.23.09 and HikariCP 5.1.0 for connection pooling against an Oracle connection pool of 30 sessions. The stored procedure was the central validation gate for the claims reservation workflow: before a claims adjuster could begin working a claim, the procedure verified that the claim was in PENDING status, below the auto-approval dollar threshold, and in a covered benefit category, then atomically set the claim status to RESERVED and recorded the adjuster’s ID. The procedure used SELECT ... FOR UPDATE NOWAIT at line 14 to lock the claim row before reading its attributes, preventing a second adjuster from concurrently reserving the same claim:

SELECT claim_amount, status, policy_id, coverage_type INTO v_claim_amount, v_status, v_policy_id, v_coverage_type FROM CLAIMS WHERE claim_id = p_claim_id FOR UPDATE NOWAIT; — this statement, when it executes successfully, places an Oracle TX exclusive lock (LMODE=6) on the CLAIMS row for claim_id = p_claim_id. The lock is owned by the Oracle session executing the procedure and is held until the session issues COMMIT or ROLLBACK. On a Tuesday morning with a queue of 180 claims opened in the preceding hour, the adjuster on workstation ADJSV-WS12 submitted claim CLM-2024-09847 (a pharmacy benefits claim, $4,200, policy PLY-2024-017843). The procedure executed the FOR UPDATE NOWAIT at line 14 successfully — the CLAIMS row was not locked by anyone else — and acquired the Oracle TX exclusive lock. At line 22, the status check evaluated: IF v_status != 'PENDING' THEN p_status_code := -1; p_message := 'Claim ' || p_claim_id || ' not in PENDING status: ' || v_status; RETURN; END IF; — the claim was in UNDER_REVIEW status (a prior adjuster had opened it in the UI but not yet submitted), so the condition was TRUE and the procedure set p_status_code := -1 and executed RETURN. The RETURN exited the procedure and returned control to the Java JDBC caller. It did not issue a ROLLBACK. The SELECT ... FOR UPDATE NOWAIT at line 14 had already placed the TX lock on the CLAIMS row. That lock was still held by the Oracle session.

The Java caller in ClaimService.validateAndReserveClaim() used a try-with-resources block: try (Connection conn = dataSource.getConnection(); CallableStatement stmt = conn.prepareCall(“{ call CLAIMS_PROC.VALIDATE_AND_RESERVE_CLAIM(?,?,?,?) }”)) { ... stmt.execute(); int statusCode = stmt.getInt(3); if (statusCode != 0) { logger.warn(“Claim {} validation failed: {}”, claimId, message); return ClaimValidationResult.failed(statusCode, message); } }. When statusCode = -1, the return ClaimValidationResult.failed() exited the try-with-resources block, triggering the auto-close of the Connection object. The Oracle JDBC driver’s Connection.close() implementation, when the connection is managed by HikariCP, does not issue an automatic ROLLBACK — it returns the logical connection to the HikariCP pool. HikariCP’s pool return path does not issue a ROLLBACK either (HikariCP would only rollback if it detected the connection was in an error state, but a non-zero stored procedure output parameter is not a JDBC error). The Oracle session behind the HikariCP pooled connection (SID 312, SERIAL# 4891) was now INACTIVE in V$SESSION, holding a TX LMODE=6 exclusive lock on the CLAIMS row for CLM-2024-09847. LAST_CALL_ET in V$SESSION counted upward from zero each second. When the Oracle DBA ran the diagnostic query at 10:23 AM, LAST_CALL_ET was 2847 — 47 minutes and 27 seconds since the session’s last database call. The lock had been held since 09:35 AM.

The downstream impact: five claims adjusters who subsequently tried to call VALIDATE_AND_RESERVE_CLAIM with p_claim_id = 'CLM-2024-09847' received ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired at the SELECT ... FOR UPDATE NOWAIT line, because the NOWAIT clause causes Oracle to raise ORA-00054 immediately rather than waiting when the requested row is already exclusively locked. The Spring Boot exception handler translated this to a 500 Internal Server Error in the adjuster’s browser, logged as java.sql.SQLTransientException: ORA-00054 resource busy. The claims supervisor received five separate Slack messages from adjusters reporting that claim CLM-2024-09847 was erroring out, and asked the Oracle DBA to investigate. The DBA ran: SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.status, s.last_call_et, l.type, l.lmode, l.request, l.block FROM v$lock l JOIN v$session s ON l.sid = s.sid WHERE l.type = 'TX' AND l.lmode = 6 ORDER BY s.last_call_et DESC; — which identified SID 312, SERIAL# 4891, USERNAME CLAIMS_PROC, MACHINE APPSERVER01, PROGRAM JDBC Thin Client, STATUS INACTIVE, LAST_CALL_ET 2847, TX LMODE=6 BLOCK=1 (blocking one or more sessions). The blocking session was the HikariCP pooled connection that had returned from the validateAndReserveClaim() call 47 minutes earlier without rolling back.

The resolution path for the immediate incident: the DBA issued ALTER SYSTEM KILL SESSION '312,4891' IMMEDIATE; to forcibly terminate the INACTIVE session holding the lock. Oracle immediately rolled back all uncommitted work associated with SID 312 (in this case, only the held FOR UPDATE lock — no DML had been committed), releasing the TX lock on the CLAIMS row. The five blocked adjuster sessions that had received ORA-00054 were already out of the database at this point (NOWAIT returned immediately to the application), so no blocked sessions were queued up waiting for the killed session’s lock. The adjusters simply retried their requests. Lock hold events that day before the fix was deployed: 5–8 (each one requiring a DBA to identify and kill the INACTIVE session).

Oracle PL/SQL and Oracle Database 19c in 2026: the enterprise database platform powering insurance, banking, and government systems

Oracle Database is the dominant enterprise relational database management system for North American and European insurance carriers, commercial banks, healthcare payers, federal and state government agencies, utilities, and manufacturing conglomerates. Oracle Database 11g Release 2 (11.2.0.4) reached end of Premier Support in January 2015 and end of Extended Support in December 2020; organizations still running 11g in 2026 are on sustaining engineering support only. Oracle Database 12cR1 (12.1.0.2) reached end of Premier Support in July 2018 and end of Extended Support in July 2022. Oracle Database 19c (19.x) is the current long-term support (LTS) release: Premier Support runs through April 2024, Extended Support through April 2027, Sustaining Support indefinitely — 19c is the dominant production release across the enterprise Oracle installed base as organizations migrated from 11g and 12c during the 2020–2024 window. Oracle Database 21c (21.x) is the non-LTS innovation release, reaching end of Premier Support in April 2024. Oracle Database 23ai (23.x, formerly branded Oracle Database 23c) is the current LTS release from 2024 forward, with AI Vector Search, JSON Relational Duality, and SQL Domains as headline features. Most organizations currently running 19c in production will begin 19c-to-23ai migration planning in the 2026–2028 window.

PL/SQL (Procedural Language / Structured Query Language) is Oracle Corporation’s procedural extension to SQL, first introduced in Oracle Database 6 (1988) and deeply integrated into every layer of Oracle-based enterprise systems: core transactional logic lives in PL/SQL stored procedures and packages; Oracle E-Business Suite (EBS) R12.2 ships with millions of lines of PL/SQL in the APPS schema; Oracle Identity Manager, Oracle Financial Services Analytical Applications, Oracle Health Insurance (formerly TriZetto), and many Oracle-adjacent ISV products expose PL/SQL APIs for customization. A PL/SQL developer on monthly retainer for an Oracle 19c database covering an insurance claims system, a banking core system, a healthcare payer adjudication engine, or a government benefits management system is maintaining code that is directly in the critical path of thousands of daily transactions. The retainer work is not glamorous — it is largely procedure-by-procedure correctness audits, performance regressions traced to bad execution plans after statistics collection, and schema evolution work to support application-layer feature additions — but the consequences of errors (lock hold bugs, missing ROLLBACKs in exception handlers, wrong index strategy on a high-frequency query) are immediately visible in production: ORA-00054 resource busy errors, ORA-01555 snapshot too old, ORA-04031 shared pool memory pressure, or an AWR report showing 40% of database time in enq: TX row lock contention.

Oracle PL/SQL developer scarcity has the same counterintuitive shape as Access developer scarcity: Oracle SQL is widely known, and many developers can write basic PL/SQL procedures. But PL/SQL transaction management — understanding that SELECT FOR UPDATE acquires a TX lock, that RETURN does not release that lock, that the calling application’s JDBC connection management determines whether the lock survives beyond the procedure call, and how to diagnose which session holds which lock via V$LOCK and V$SESSION — is knowledge that requires years of production experience on Oracle systems under concurrent load. A PL/SQL developer who can trace a “5–8 lock hold events per day” symptom to a VALIDATE_AND_RESERVE_CLAIM procedure that does SELECT FOR UPDATE at line 14 and RETURN at line 22 without ROLLBACK, confirm the diagnosis via V$LOCK LMODE=6 BLOCK=1 on an INACTIVE session with LAST_CALL_ET > 2800, fix both the PL/SQL layer and the Java JDBC caller, and audit every other procedure in the CLAIMS_PROC package for the same pattern — is providing expertise that is rare and genuinely difficult to find for an insurance carrier that needs this fixed before the next daily claim processing cycle.

Oracle TX lock internals: SELECT FOR UPDATE, ROLLBACK, COMMIT, and V$LOCK diagnostics

Oracle Database implements row-level locking for DML statements (INSERT, UPDATE, DELETE, MERGE) and for SELECT ... FOR UPDATE queries. A TX lock in V$LOCK represents an active transaction: when a session issues any DML or SELECT FOR UPDATE, Oracle records the transaction in the rollback/undo segment and places a TX lock entry in V$LOCK with LMODE=6 (exclusive mode, the only mode for TX locks). The TX lock is associated with the Oracle session (identified by SID and SERIAL# in V$SESSION) and is held until the session explicitly issues COMMIT (which commits all changes and releases all TX-associated row locks) or ROLLBACK (which discards all changes and releases all TX-associated row locks), or until the session is killed by a DBA via ALTER SYSTEM KILL SESSION. There is no automatic TX lock timeout on idle sessions in a standard Oracle Database configuration (Oracle’s IDLE_TIME profile parameter limits idle session lifetime, but this is almost never set in enterprise production configurations because it would kill long-running but legitimate batch connections).

The V$LOCK view exposes all lock holders and waiters. For TX lock diagnosis: SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.status, s.last_call_et, l.lmode, l.request, l.block FROM v$lock l JOIN v$session s ON l.sid = s.sid WHERE l.type = 'TX' ORDER BY l.block DESC, s.last_call_et DESC; — LMODE=6 with BLOCK=1 identifies the holder; LMODE=0 with REQUEST=6 identifies the waiters. LAST_CALL_ET measures seconds since the session’s last database call — a large value on a STATUS=INACTIVE session holding a TX lock is the classic signature of a connection pool connection that returned to the pool with an uncommitted transaction (the session made its last database call when the procedure executed the FOR UPDATE NOWAIT, then went INACTIVE when the Java code returned the connection to HikariCP, and LAST_CALL_ET has been counting upward ever since). The V$SESSION.PROGRAM column typically shows JDBC Thin Client or the application name for connection-pool managed sessions, which identifies the calling application tier.

The fix for the VALIDATE_AND_RESERVE_CLAIM procedure had two layers. PL/SQL layer: every non-success RETURN path in the procedure was preceded by a ROLLBACK; statement. Line 22 became: p_status_code := -1; p_message := 'Claim ' || p_claim_id || ' not in PENDING status: ' || v_status; ROLLBACK; RETURN;. Lines 29 and 36 (the dollar threshold and coverage exclusion checks) received the same treatment. The EXCEPTION WHEN NO_DATA_FOUND THEN and WHEN OTHERS THEN handlers, which previously had no ROLLBACK, also received ROLLBACK; before the output parameter assignment. A PL/SQL ROLLBACK in a stored procedure releases all locks held by the current transaction in the calling session — including the TX lock acquired by the FOR UPDATE NOWAIT. After ROLLBACK, the Oracle session has no open transaction and holds no row locks. Java JDBC layer: the validateAndReserveClaim() method was updated to call conn.rollback() before the non-success return path: if (statusCode != 0) { conn.rollback(); logger.warn(“Claim {} validation failed: {}”, claimId, message); return ClaimValidationResult.failed(statusCode, message); }. The conn.rollback() is defense in depth: the ROLLBACK in the PL/SQL procedure already releases the lock on the Oracle server side, but the Java-side rollback ensures correctness for any future path where the procedure might not issue its own ROLLBACK (for example, if the PL/SQL is refactored and a new exit path is added without a ROLLBACK). The full CLAIMS_PROC package was audited: all 11 procedures that issued SELECT FOR UPDATE or DML were reviewed for missing ROLLBACK on non-success exit paths. Three additional procedures had the same pattern; all were fixed in the same deployment.

Typical Oracle PL/SQL developer retainer work and what it looks like in a work log

SELECT FOR UPDATE TX lock held after non-success RETURN without ROLLBACK is the canonical Oracle PL/SQL invisible production lock bug for any Oracle application that uses stored procedures with input-validation patterns. It appears in the work logs of Oracle PL/SQL retainer engagements for insurance claims processing systems (validation gates that lock a claim row before checking its eligibility), banking core systems (balance reservation procedures that lock an account row before checking credit limits), healthcare adjudication engines (benefit check procedures that lock a patient encounter row before validating coverage), and government benefits management systems (allocation procedures that lock a benefit record before checking eligibility criteria). The work log entry: “Oracle Database 19.17.0.0.0; schema CLAIMS_PROC; procedure VALIDATE_AND_RESERVE_CLAIM; SELECT claim_amount, status, policy_id, coverage_type FROM CLAIMS WHERE claim_id = p_claim_id FOR UPDATE NOWAIT at line 14; TX LMODE=6 exclusive lock acquired on CLAIMS row claim_id CLM-2024-09847; status validation at line 22: IF v_status != 'PENDING' THEN p_status_code := -1; RETURN — RETURN without ROLLBACK; Oracle session (SID 312, SERIAL# 4891, STATUS INACTIVE, LAST_CALL_ET 2847) held TX LMODE=6 BLOCK=1 on CLAIMS row CLM-2024-09847; Java caller: HikariCP 5.1.0 conn.close() returned connection to pool without conn.rollback(); 5 concurrent adjusters received ORA-00054 on same claim_id; lock hold frequency per day before fix: 5–8; fix: ROLLBACK added before all non-success RETURN paths (lines 22, 29, 36) and EXCEPTION handlers; conn.rollback() added in Java caller before non-success return; full CLAIMS_PROC package audited — 3 additional procedures fixed; lock hold frequency after fix: 0; hours: 4h.”

Oracle AWR (Automatic Workload Repository) top wait event analysis is the most common Oracle PL/SQL retainer work where the lock bug is discovered indirectly: the application team notices elevated response times, asks the DBA for an AWR report, and the DBA finds enq: TX row lock contention as the top wait event by total wait time in the 8 AM–11 AM window. The AWR report identifies the SQL statement (the SELECT FOR UPDATE) as the top waited SQL, and the blocking session is traced to a JDBC pool connection holding a TX lock from a procedure that returned early without ROLLBACK. Work log: “Oracle 19c; AWR report 08:00–11:00 snapshot 3481–3484; top wait event: enq: TX row lock contention, 847 occurrences, 14,832 seconds total wait time, 17.5 seconds average; top waited SQL_ID 4xy8qp9r1kmvj: SELECT claim_amount, status, policy_id, coverage_type FROM CLAIMS WHERE claim_id = :p_claim_id FOR UPDATE NOWAIT — PL/SQL procedure CLAIMS_PROC.VALIDATE_AND_RESERVE_CLAIM line 14; diagnosis: V$LOCK TX LMODE=6 BLOCK=1 on INACTIVE session SID 312 LAST_CALL_ET 2847; cause: VALIDATE_AND_RESERVE_CLAIM RETURN at line 22 without ROLLBACK, HikariCP pool return without conn.rollback(); fix: ROLLBACK on all non-success exit paths in procedure + conn.rollback() in Java caller; AWR enq: TX row lock contention wait event count next day: 0; hours: 6h diagnosis + 4h fix + 2h AWR validation.”

Oracle PL/SQL package-level transaction state audit is planned retainer work that goes beyond fixing a single incident: reviewing all procedures and functions in a PL/SQL package (or schema) that perform DML or SELECT FOR UPDATE to verify that every exit path — success, validation failure, exception handler — terminates with either COMMIT, ROLLBACK, or a clearly documented agreement that the calling application will issue the COMMIT or ROLLBACK. This work is common in Oracle retainer engagements for insurance and banking systems where the PL/SQL codebase was built over 10–15 years by multiple developers, the original author documentation is lost, and no one has a complete map of which procedures expect the caller to manage the transaction and which are expected to manage it internally. Work log: “Oracle 19c; schema CLAIMS_PROC; package PKG_CLAIMS_WORKFLOW audit (11 procedures, 7 functions); objective: verify every DML/SELECT FOR UPDATE exit path has explicit COMMIT or ROLLBACK; findings: VALIDATE_AND_RESERVE_CLAIM (3 missing ROLLBACKs), REOPEN_DENIED_CLAIM (2 missing ROLLBACKs), TRANSFER_CLAIM_TO_UNIT (1 missing ROLLBACK in EXCEPTION handler), CALCULATE_BENEFIT_AMOUNT (SELECT only, no lock exposure, clean), 7 other procedures clean; fix: 6 ROLLBACKs added across 3 procedures; all procedures re-tested in UAT with concurrent load simulation (10 concurrent sessions, 50 claims per session); ORA-00054 occurrence in load test: 0; hours: 12h.”

How HourTab tracks Oracle PL/SQL developer retainer hours

Oracle PL/SQL SELECT FOR UPDATE TX lock hold bugs after non-success RETURN are invisible in single-session development testing by the same mechanism that makes all database concurrency bugs invisible when only one session accesses the database at a time. The developer who wrote VALIDATE_AND_RESERVE_CLAIM tested it via a single SQL*Plus or SQLcl session: they called the procedure, checked the output parameters, confirmed the CLAIMS row was updated to RESERVED when the claim was in PENDING status, and confirmed the error message was returned correctly when the claim was in a non-PENDING status. The lock behavior only becomes visible when two Oracle sessions call the procedure concurrently for the same claim_id and the first session returns early without ROLLBACK before the second session’s FOR UPDATE NOWAIT executes. A single-developer SQL*Plus test never exercises this scenario.

The work log entry that makes Oracle PL/SQL lock work auditable must name every element of the causal chain: the Oracle Database version (19c vs. 12c vs. 11g — the TX lock mechanism is identical, but the diagnostic views and AWR structure differ between versions), the schema and procedure name with the exact FOR UPDATE line number and the exact non-success RETURN line number, the V$SESSION SID and SERIAL# with LAST_CALL_ET and STATUS at diagnosis time, the V$LOCK LMODE and BLOCK values, the Java JDBC stack (JDBC driver version, connection pool implementation, and whether conn.rollback() was missing from the caller), and the lock hold frequency before and after the fix. A log that says “fixed PL/SQL lock issue, 4h” is not auditable to an insurance carrier’s operations director who is trying to explain why five claims adjusters received 500 errors on the same claim for 47 minutes. A log that names the procedure, the FOR UPDATE line, the missing ROLLBACK, the specific V$SESSION row that was holding the lock, the HikariCP pooled connection return without rollback as the propagation mechanism, and the zero-occurrence outcome after the fix is auditable and demonstrates the value of a long-term Oracle PL/SQL retainer relationship for a health insurance carrier whose claims adjudication throughput depends on an Oracle Database stored procedure package running without lock contention. HourTab gives Oracle PL/SQL developers a public retainer-hours URL they send to clients — insurance carriers, banks, healthcare payers, government agencies, and manufacturing companies that run core transactional workflows on Oracle Database PL/SQL packages maintained by an Oracle specialist on monthly retainer.

The broader context for Oracle PL/SQL retainer billing is that the SELECT FOR UPDATE TX lock held after RETURN without ROLLBACK is the Oracle PL/SQL instantiation of the universal principle that any system allowing long-lived exclusive locks held by application code demands explicit lock release on every exit path. Oracle Forms developer retainers cover the equivalent at the Oracle Forms 4GL layer: LOCKING_MODE AUTOMATIC fires SELECT ... FOR UPDATE NOWAIT on the Oracle ORDERS table row when the production scheduler presses the first key in a field, holding the TX lock for 2+ hours when the operator is interrupted — same Oracle TX lock mechanism, different application layer. Oracle Reports developer retainers cover the equivalent in Oracle Reports Services: an SRW.DO_SQL UPDATE in a Before Report trigger acquires a TX lock on a staging table row and holds it for the entire 6–8 minute report execution duration because no SRW.DO_SQL(‘COMMIT’) is issued after the update — serializing all concurrent month-end report runs behind the first instance. Microsoft Access developer retainers cover the equivalent on a different database platform: the ACE engine’s Edited Record pessimistic locking mode holds a record-level lock for the Access session lifetime when the estimator minimizes the form without saving, blocking concurrent estimators from editing the same quote.

FAQ: Oracle PL/SQL developer retainers