Baan developer on retainer: bwisfc1999m000 mass cost recalculation holds single transaction across 387,000 production order updates — Baan lock table overflow terminates run and blocks concurrent LN users for four hours — Infor LN developer on monthly retainer

Infor LN 10.4.1 • SQL Server 2019 • Baan 4GL • ttisfc001000 • selectdo db.update() • single db.commitx() • lock table overflow • retainer billing

At Midland Heavy Equipment, the monthly production order cost recalculation had been growing progressively slower for six months. The custom Infor LN session bwisfc1999m000 recalculated planned-versus-actual cost variances across all production orders and wrote updated variance fields back to the production order header table. Run times: January 47 minutes, February 1h 22m, March 2h 48m, April 4h 17m — and in April the session did not complete. At hour four, Infor LN terminated it with the error: Lock table overflow. Session aborted. The partial update left cost variance data in a mixed state — some production orders with April figures, others still at March — requiring a manual rollback to backup and a second run attempt before the data was usable.

During those four-plus hours, any shop floor supervisor or planner who tried to update a production order in standard LN sessions bdsfc2100m000 (Maintain Production Orders) or bdsfc0520m000 (Approve Production Orders) received a lock timeout error on 30–40% of their update attempts. The Baan 4GL developer retainer was engaged after April’s failed run to diagnose why a session that had worked fine for a year was now failing — and why it was getting worse each month.

Infor LN: Baan 4GL transaction scope and the lock table

Infor LN is the current product name for Baan ERP (Baan IV and Baan V), now owned by Infor. All application code is written in a proprietary 4GL called Baan Tools, which provides a Pascal-like scripting language. The language has a built-in data access layer: selectdo opens a cursor over a table, endselect closes it, db.update() writes the current record back to the database within the active transaction, and db.commitx() commits the transaction and releases all pending locks.

The critical point: in Baan 4GL, every db.update() call operates within the currently open transaction. The transaction remains open — accumulating pending database row locks — until db.commitx() is called. Infor LN maintains an internal lock table that tracks all pending row locks for the session. SQL Server (the underlying database in this installation) also holds row-level locks for the open transaction. Both the LN lock table and the SQL Server lock infrastructure have capacity limits. When the session holds an open transaction across hundreds of thousands of update operations, both limits are approached.

The bwisfc1999m000 session design: one transaction, all records

The session bwisfc1999m000 was written 18 months earlier, when the ttisfc001000 production order header table contained approximately 89,000 records. At that size, the single-transaction design completed in 47 minutes with no issues — the peak lock count was manageable and SQL Server lock memory was within normal range. The session was never designed to scale; it was written for the table size that existed at the time.

The Baan 4GL structure of the session (simplified for readability):

function b_wrtc.bwisfc1999m000.recalculate()
  selectdo
    select all from ttisfc001000
    where ttisfc001000.t_cwar = p_cwar
    order by ttisfc001000.t_mitm
      | Calculate planned vs actual cost variance
      ttisfc001000.t_plca = calculate.planned_cost(ttisfc001000.t_mitm)
      ttisfc001000.t_acca = calculate.actual_cost(ttisfc001000.t_mitm)
      ttisfc001000.t_cvar = ttisfc001000.t_acca - ttisfc001000.t_plca
      db.update()
      | BUG: no db.commitx() here
      | Each db.update() adds one lock to the LN lock table
      | After 387,000 iterations: 387,000 pending locks in one transaction
  endselect
  db.commitx()   | BUG: single commit for entire table — 387,000 rows
endfunction

By April, ttisfc001000 had grown from 89,000 records to 387,000. The monthly recalculation now attempted to hold a single transaction across 387,000 db.update() calls. Lock table overflow terminated the session at approximately record 350,000, after four hours and seventeen minutes of accumulation.

Why concurrent users were blocked

While bwisfc1999m000 held its open transaction, SQL Server held row-level locks on every ttisfc001000 record that had been updated so far in the current transaction scope. A shop floor supervisor opening a production order in bdsfc2100m000 triggered an LN data access call to read and potentially update that record. If the record was among those already updated by bwisfc1999m000 (still locked in the open transaction), SQL Server blocked the supervisor’s read-for-update until the lock was released. The lock timeout in this SQL Server environment was 30 seconds — after 30 seconds without release, SQL Server returned a lock timeout error to Infor LN, which displayed a lock error to the user.

By month three, when the session was holding 200,000+ locks over nearly three hours, roughly half the production order table was locked at any given moment during the run. The 30–40% lock error rate for interactive users corresponded to the fraction of their attempted updates that touched a record within the growing locked set.

Diagnosis: SQL Server lock monitoring and Baan session source review

The Baan 4GL developer retainer began with SQL Server monitoring. Running a query against sys.dm_tran_locks while the session was active confirmed a single SQL Server transaction holding over 180,000 row-level lock entries on ttisfc001000 at the point of inspection — with the count growing by roughly 1,500 per minute. The transaction had been open for approximately 2 hours at the time of inspection.

Source review of bwisfc1999m000 confirmed the single-transaction design: one selectdo loop, one db.update() per iteration, one db.commitx() at loop end. The session had no row counter, no intermediate commit, and no restart mechanism. The table row count growth from 89,000 (at session creation) to 387,000 (April) explained the progression: January’s 47-minute run at 89,000 rows; April’s 4h17m run at 387,000 rows with lock overflow at ~350,000 rows. The growth rate was approximately 25,000 rows per month — consistent with steady order volume at the manufacturing plant.

The fix: batch commit every 500 records

The corrected session added a bigunit counter variable and called db.commitx() at every 500 records. The batch size of 500 was chosen to keep the maximum pending lock count well below the LN lock table limit while committing frequently enough to minimize the lock window for interactive users:

function b_wrtc.bwisfc1999m000.recalculate()
  local domain bigunit l.cnt
  l.cnt = 0
  selectdo
    select all from ttisfc001000
    where ttisfc001000.t_cwar = p_cwar
    order by ttisfc001000.t_mitm
      ttisfc001000.t_plca = calculate.planned_cost(ttisfc001000.t_mitm)
      ttisfc001000.t_acca = calculate.actual_cost(ttisfc001000.t_mitm)
      ttisfc001000.t_cvar = ttisfc001000.t_acca - ttisfc001000.t_plca
      db.update()
      l.cnt = l.cnt + 1
      if l.cnt >= 500 then
        db.commitx()    | Release locks every 500 rows
        l.cnt = 0
      end
  endselect
  if l.cnt > 0 then
    db.commitx()        | Final commit for remaining rows
  end
endfunction

With a 500-record batch commit, the maximum number of pending locks at any moment is 500. LN lock table overhead: negligible. SQL Server lock memory: negligible. The lock window for any single record is at most the time to process 499 other records — roughly 2–3 seconds at the session’s processing rate — instead of the full multi-hour run.

Test results on the production-size 387,000-row data set: session run time 24 minutes (faster than the original January 47-minute run at 89,000 rows, because SQL Server lock management overhead during the run dropped to near zero). Peak pending locks during the run: observed maximum of 498 rows. Concurrent user lock timeouts during the test run: 0. The improvement in run time was not anticipated — it is a side effect of SQL Server performing significantly less lock management work when transactions are short.

Why table growth exposed the design flaw

The session was correct and functional when written against 89,000 rows. The single-transaction design is not inherently wrong for small tables — it provides full atomicity (either all records are updated or none are), which has value in some contexts. The problem is that atomicity across 387,000 records is not achievable within the LN lock table capacity. The batch commit design sacrifices full-table atomicity for practical operability: each 500-record batch is atomic, but a failure after batch 400 of 774 leaves 400 batches committed and the remainder at prior values, requiring re-run from batch 401 (or from the beginning with idempotent update logic).

For a variance recalculation session, partial atomicity is acceptable — recalculating from any starting point produces the same result because the calculation is deterministic from current cost data. The session was enhanced with a restart table that records the last committed ttisfc001000.t_mitm (item code) after each db.commitx(), so that if the session is interrupted, the next run can skip already-committed batches rather than re-processing the full table from record 1.

How HourTab helps Baan / Infor LN developer retainers

The Midland Heavy Equipment engagement took 4.0 hours: error analysis (0.5h, reviewing the April lock overflow error log and the month-by-month run time progression), session source review (1.0h, reading bwisfc1999m000 source in the Baan Tools editor, confirming the single-transaction structure and identifying the db.commitx() placement), fix implementation (0.5h, adding the counter variable, the per-500-rows commit, and the final post-loop commit), and testing (1.5h, a full 387,000-row test run against a restored backup of ttisfc001000 with SQL Server lock monitoring active to confirm peak lock count and run time, plus a concurrent-user simulation confirming 0 lock timeouts during the test run). The restart mechanism was added separately (0.5h) as a defensive enhancement for large-table sessions.

In a HourTab retainer log, the session reads: “bwisfc1999m000 lock table overflow: selectdo over ttisfc001000 (387,000 rows), db.update() per row, single db.commitx() at loop end; April run: started 02:17, lock table overflow at 04:34 (~350,000 rows); partial update: 350,000 rows at April values, 37,000 rows at March values; SQL Server dm_tran_locks at inspection: 180,000 row locks in one transaction; concurrent user impact: 30–40% lock timeout rate on bdsfc2100m000 and bdsfc0520m000 during run; fix: batch db.commitx() every 500 rows (counter variable l.cnt, commitx + reset at 500, final commitx after endselect); peak locks after fix: 498 observed; run time after fix: 24m (vs 4h17m before); concurrent lock timeouts after fix: 0; restart table added for partial-run recovery; 4.0h.” Midland Heavy Equipment’s operations manager can see the root cause (one transaction, 387,000 locks), the concurrent user impact, and the zero-lockout result of the fix.

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: Baan / Infor LN developer retainers

See also: JD Edwards developer retainer · SAP ABAP developer retainer · Progress 4GL developer retainer