NonStop developer on retainer: ACCTUPD cursor C1 FOR UPDATE not closed on frozen-account validation exit — NonStop SQL/MP row lock held until timeout blocks BALUPD balance updates — HPE NonStop developer, Tandem developer on monthly retainer

HPE NonStop NB50 • Guardian OS L17.02 • COBOL85 ACCTUPD • DECLARE CURSOR FOR UPDATE • CLOSE without ROLLBACK • SQLCI SHOW LOCKS • retainer billing

At Meridian Payment Systems, the overnight balance update job BALUPD started logging SQL lock timeout errors on a small but consistent set of account records. The BALUPD program would attempt to update an account balance, wait the configured lock timeout interval, and then roll back without updating. The affected accounts were not corrupted — their balances were simply not updated that night — but each rollback required a manual correction in the morning and triggered a compliance exception log entry that had to be reviewed by the operations team.

The HPE NonStop retainer developer was engaged. The diagnosis confirmed that the lock timeouts were caused by the ACCTUPD account status update program holding open a DECLARE CURSOR C1 FOR UPDATE on the same ACCOUNT rows that BALUPD was trying to update. ACCTUPD had exited its processing loop through a validation-failure path that skipped both EXEC SQL CLOSE C1 and EXEC SQL ROLLBACK WORK. NonStop SQL/MP kept the cursor open and its row-level update lock held until the ACCTUPD Pathway server process was restarted at the next maintenance window.

NonStop SQL/MP cursor locks and the update lock lifecycle

NonStop SQL/MP (the embedded SQL engine for Guardian programs prior to SQL/MX) acquires row-level locks when a cursor declared with FOR UPDATE fetches a row. The lock is acquired at FETCH time — not at OPEN time — and it is held until one of the following:

• EXEC SQL UPDATE … WHERE CURRENT OF cursor — the positioned update executes; the lock is released when the enclosing transaction commits or rolls back.
• EXEC SQL COMMIT WORK — commits the transaction and releases all locks acquired by the current transaction, including FOR UPDATE cursor locks.
• EXEC SQL ROLLBACK WORK — rolls back the transaction and releases all locks.
• EXEC SQL CLOSE cursor — closes the cursor; in NonStop SQL/MP, closing a FOR UPDATE cursor within a transaction releases the row lock acquired by the most recent FETCH on that cursor. If the cursor is closed without a COMMIT or ROLLBACK, the lock for the last-fetched row is released, but any locks from prior fetches in the same transaction that were updated (but not yet committed) remain held until the transaction ends.
• Transaction abort or process termination — NonStop SQL/MP rolls back any uncommitted transaction when a process terminates or aborts, releasing all locks.

The critical point: a FOR UPDATE cursor that is neither closed nor has its transaction committed or rolled back holds update locks on every row it has fetched in the current transaction. If the program loops over many rows with FETCH and then exits without CLOSE or ROLLBACK, those rows stay locked for the lifetime of the transaction — which, in a long-running Pathway server process, can be indefinite.

The ACCTUPD program and the frozen-account exit path

ACCTUPD was a Pathway server program that processed account status update requests from a front-end application. For each request, it opened cursor C1 to find the relevant ACCOUNT row, fetched it, validated the account state, and either updated the status or exited:

       WORKING-STORAGE SECTION.
           01  WS-ACCT-NUM        PIC X(12).
           01  WS-ACCT-STATUS     PIC X(10).
           01  WS-NEW-STATUS      PIC X(10).
           01  WS-SQLCODE         PIC S9(9) COMP.

       PROCEDURE DIVISION.

       MAIN-PARA.
           EXEC SQL
               DECLARE C1 CURSOR FOR
               SELECT ACCT_NUM, ACCT_STATUS
               FROM   ACCOUNT
               WHERE  ACCT_NUM = :WS-ACCT-NUM
               FOR UPDATE OF ACCT_STATUS
           END-EXEC

           EXEC SQL OPEN C1 END-EXEC
           IF SQLCODE NOT = 0 GO TO EXIT-PARA

           EXEC SQL
               FETCH C1 INTO :WS-ACCT-NUM, :WS-ACCT-STATUS
           END-EXEC
           IF SQLCODE = 100 GO TO EXIT-PARA  *> no row found

           * NonStop SQL/MP now holds update lock on ACCOUNT row

           IF WS-ACCT-STATUS = 'FROZEN'
               MOVE 'FROZEN ACCOUNT' TO WS-ERR-MSG
               PERFORM EXIT-PARA      *>← BUG: no CLOSE or ROLLBACK
           END-IF

           IF WS-ACCT-STATUS = 'DORMANT'
               MOVE 'DORMANT ACCOUNT' TO WS-ERR-MSG
               PERFORM EXIT-PARA      *>← BUG: no CLOSE or ROLLBACK
           END-IF

           EXEC SQL
               UPDATE ACCOUNT
               SET    ACCT_STATUS = :WS-NEW-STATUS
               WHERE  CURRENT OF C1
           END-EXEC

           EXEC SQL COMMIT WORK END-EXEC  *> releases lock on normal path
           EXEC SQL CLOSE C1  END-EXEC

           GO TO MAIN-PARA.

       EXIT-PARA.
           *> Missing: EXEC SQL CLOSE C1 END-EXEC
           *> Missing: EXEC SQL ROLLBACK WORK END-EXEC
           STOP RUN.

When WS-ACCT-STATUS = 'FROZEN', the program performs EXIT-PARA immediately. The EXIT-PARA paragraph contained only STOP RUN — no EXEC SQL CLOSE C1 and no EXEC SQL ROLLBACK WORK. Because ACCTUPD ran as a Pathway server process (not a standalone program), STOP RUN in a Pathway server did not terminate the process — it returned control to the Pathway framework to handle the next request. The NonStop SQL/MP transaction remained open. The cursor C1 remained open. The update lock on the ACCOUNT row for account 5100200047 remained held.

The next request to ACCTUPD opened a new cursor C1 for a different account number, starting a new cursor on a new row. But the previous cursor — the one that had exited through the FROZEN path — was still technically open within the process’s SQL context, with its transaction uncommitted and its lock unreleased. Each FROZEN or DORMANT account processed by ACCTUPD left another orphaned open cursor and another unreleased row lock.

SQLCI SHOW LOCKS: finding the lock holder

The retainer developer opened a SQLCI (the NonStop SQL/MP interactive utility) session and ran the lock diagnostic command while BALUPD’s lock timeout was actively occurring:

SQLCI> SHOW LOCKS ON TABLE ACCOUNT;

Table: $DATA07.ACCTSQL.ACCOUNT
Lock Type  Row Key              Process    CPU,Pin   Held Since
---------  -------------------  ---------  --------  ----------
UPDATE     ACCT_NUM=5100200047  $APPPR4    (4,823)   14:37:22
UPDATE     ACCT_NUM=4820013991  $APPPR4    (4,823)   15:12:04
UPDATE     ACCT_NUM=7231004852  $APPPR4    (4,823)   16:58:37

Process $APPPR4 (CPU 4, PID 823) held update locks on three ACCOUNT rows. These were the Meridian Payment ACCTUPD Pathway server process on CPU 4. The lock times showed that 5100200047 had been locked since 14:37 — over four hours earlier — corresponding to when that account was processed and found to be FROZEN. The developer cross-referenced the account numbers against ACCOUNT records and confirmed all three had ACCT_STATUS = 'FROZEN' or 'DORMANT' — the two status values that triggered the EXIT-PARA path without CLOSE or ROLLBACK.

BALUPD’s lock timeout was happening because account 5100200047 was a valid account for nightly balance updates, but its row was locked by ACCTUPD’s orphaned cursor. BALUPD’s EXEC SQL UPDATE ACCOUNT ... WHERE ACCT_NUM = :WS-ACCT could not acquire the write lock it needed and timed out after the configured interval.

Fix: CLOSE and ROLLBACK on every exit path

The fix required adding EXEC SQL CLOSE C1 and EXEC SQL ROLLBACK WORK to the EXIT-PARA paragraph, and also to every specific validation-failure path that performed EXIT-PARA without first going through the normal commit path:

       EXIT-PARA.
           EXEC SQL CLOSE C1  END-EXEC        *> added
           EXEC SQL ROLLBACK WORK END-EXEC    *> added
           STOP RUN.

Adding the cleanup to EXIT-PARA covers all paths that PERFORM EXIT-PARA without first executing COMMIT WORK. The developer also reviewed the full ACCTUPD source for any other cursor declarations or GO TO EXIT-PARA / PERFORM EXIT-PARA paths that could reach the exit paragraph with an open cursor:

• Line 91 (FROZEN check): PERFORM EXIT-PARA — now covered by the EXIT-PARA fix.
• Line 97 (DORMANT check): PERFORM EXIT-PARA — covered.
• Line 104 (WRONG-CURRENCY check, added in a 2024 enhancement): PERFORM EXIT-PARA — covered.
• Line 63 (OPEN C1 SQLCODE ≠ 0): GO TO EXIT-PARA — this path executes before any FETCH, so no row lock is held. CLOSE C1 on an unopened cursor in NonStop SQL/MP is a no-op (SQLCODE 0), and ROLLBACK WORK with no active transaction is also a no-op. The added EXIT-PARA cleanup is safe here.
• Line 70 (FETCH C1 SQLCODE = 100, no row found): GO TO EXIT-PARA — similarly, no row was fetched so no update lock was acquired. The added cleanup is safe and correct.

After the fix, ACCTUPD was recompiled with the NonStop COBOL85 compiler and linked with KBINDER. The ACCTUPD Pathway server class ACCTSVC was stopped and restarted to clear the existing orphaned cursors and release the three held locks. After restart, SQLCI SHOW LOCKS ON TABLE ACCOUNT showed no update locks held by $APPPR4. A test run through the FROZEN account path confirmed that EXIT-PARA now executed CLOSE C1 and ROLLBACK WORK, and that SQLCI SHOW LOCKS showed no residual lock after the exit. The overnight BALUPD job ran successfully the following night with zero lock timeouts on the previously-affected accounts.

Why Pathway server processes amplify cursor leak risk

The NonStop Pathway framework runs server programs as persistent processes that handle many requests over their lifetime without terminating between requests. This is the source of NonStop’s extreme throughput — process creation is expensive, so Pathway reuses processes. But it means that a cursor leak in a Pathway server is far more dangerous than in a standalone batch program:

• A standalone batch program that exits without closing cursors has all its locks released when the process terminates — the damage window is bounded by the run time of one execution.
• A Pathway server program that leaks a cursor on a FROZEN account validation path accumulates one unreleased lock per FROZEN account it processes, indefinitely, until the server process is manually restarted or terminates for another reason. A server that handles thousands of requests per day can accumulate thousands of orphaned locks.

At Meridian, ACCTUPD had been deployed for eight months before the BALUPD lock timeouts became frequent enough to trigger the operations alert. During those eight months, every FROZEN or DORMANT account update request had silently left an orphaned lock. Most of those locks were on accounts that BALUPD did not need to update, so they caused no visible problem. The three accounts that triggered the alert were FROZEN accounts that also happened to receive nightly balance credits — an unusual but valid business combination that exposed the dormant cursor leak.

Post-incident: the Meridian operations team added a daily SQLCI diagnostic check to their monitoring TACL script that ran SHOW LOCKS ON TABLE ACCOUNT and alerted if any process held more than five simultaneous update locks. Normal ACCTUPD operation requires at most one update lock per active request — five was chosen as a threshold that would catch cursor accumulation before it reached the level that caused BALUPD interference.

How HourTab helps HPE NonStop retainer developers

The diagnosis took 3.25 hours: 30 minutes reading the BALUPD error logs and identifying the specific ACCOUNT rows involved, 30 minutes in SQLCI running SHOW LOCKS and correlating lock holders to ACCTUPD server processes, 45 minutes reviewing the full ACCTUPD source for all cursor exit paths, 30 minutes writing and testing the fix, 30 minutes recompiling, linking, and restarting the Pathway server class, 30 minutes running the overnight test and verifying BALUPD succeeded. The retainer developer also spent 30 minutes with the operations team walking through the TACL monitoring script addition and explaining why the daily lock count check would catch future cursor leaks early.

Each of those segments is distinct, billable retainer work. In a HourTab log, the session reads: “ACCTUPD cursor C1 FOR UPDATE leak on FROZEN/DORMANT exit paths: SQLCI SHOW LOCKS confirmed 3 orphaned update locks held by $APPPR4 since 14:37; added EXEC SQL CLOSE C1 + ROLLBACK WORK to EXIT-PARA; recompile/link/restart ACCTSVC; BALUPD overnight test clean; added TACL SHOW LOCKS monitor alert (threshold 5 locks), 3.25h.” Meridian’s operations director reads that entry and sees exactly what was wrong, what was fixed, and why the monitoring addition was part of the same engagement — not scope creep.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: HPE NonStop developer retainers

See also: CICS developer retainer · IMS DB developer retainer · Db2 developer retainer