Transaction Report causes OutOfMemoryError and leaves dead connections in the DB pool #6201

Summary

Running the Transaction Report (/json/getTransactionReport) exhausted the Java heap in production. After that the DB connection pool handed out dead connections, so even /auth/login failed until the server was restarted.

The evidence so far points to the report loading far more data than it needs, not to a slow memory or connection leak (see Timeline).

Reproduction

1. Location: a warehouse with about 25k transactions.
2. Go to Reports > Transaction Report.
3. Set Start date 01/01/2026 and End date 08/31/2026, then click Submit.
   • Result: the table shows no results (see "Date filter" below).
4. Click Download Data (the CSV export).
   • Result: the server runs out of heap and becomes unresponsive.

Timeline (2026-09-17, UTC)

Time	Event
17:17	Server reported down
17:38	First Sentry events (heap space, JDBC rollback failures)
~17:42	Server restarted
17:46	Java heap space again, 4 minutes after the restart
18:03, 18:18, 19:15	More OutOfMemoryErrors
19:25 to 09-18 01:48	DB timeouts / rollback failures continue

A freshly restarted JVM (about 1.8 GB max heap) ran out of memory within minutes. So this report alone can exhaust the heap; an accumulated leak isn't needed to explain it.

Earlier observations (not conclusive on their own):

• Heap was about 90% used (1612 MB of 1820 MB) at a quiet moment. That was a snapshot of used heap, not what remains after GC, so it doesn't prove a leak.
• The VM uses only about 20% of its RAM, so the heap is capped well below what's available.
• The previous restart was on Aug 11.

Sentry

• Heap: SBHF-PROD-36Z, 36Y, 36W
• DB timeouts / rollback failures: 36J, 36K, 36M, 36N, 36X
• Dead pooled connections on login: 373, 374

Root causes (code)

1. Opening/closing balances load the full transaction history. ReportService.getTransactionReport() calls productAvailabilityService.getAvailableItemsAtDateAsMap() for startDate and endDate. For past dates that calls InventoryService.getTransactionEntriesBeforeDate(), which loads every TransactionEntry before the date for every product with activity, as full Hibernate entities plus lazy loads. With an end date of 08/31/2026, that is essentially the location's entire history, loaded twice. (The 25k figure counts transactions; the entry count is several times higher.)
2. Period entries are loaded as entities. getFilteredTransactionEntries() returns full TransactionEntry objects that are then grouped and summed in Groovy. The report doesn't use transaction_fact.
3. CSV "backdated" calculation (Download Data). value.transaction.unique()...transactionEntries.flatten() loads every entry of every matching transaction, across all products, before filtering. This is the heaviest path.
4. Concurrent retries. The AJAX timeout is 120s (openboxes.ajaxRequest.timeout). The browser gives up, but the server thread keeps running, so each resubmit adds another full request.
5. Connection held for the whole request. JsonController has @Transactional on the class, so each request holds a connection and a growing Hibernate session until the JSON has been rendered.
6. Pool hands out dead connections. testOnBorrow: false in application.yml. Also, maxAge: 10 * 60000 is a plain YAML string, not a number, so it may not be taking effect.

Date filter "not working"

This is most likely a silent timeout, not a date-binding bug. An 8-month range runs longer than the 120s AJAX timeout. jQuery then reports xhr.status == 0, and the fnServerData error handler in showTransactionReport.gsp returns without showing anything. The user sees an empty table while the server keeps working. Date binding looks correct (MM/dd/yyyy is in grails.databinding.dateFormats). Confirm after the performance fix.

Proposed fix

Code

• Calculate opening and closing balances with aggregate SQL (SUM(quantity) ... GROUP BY inventory_item, bin_location), or use transaction_fact, instead of loading entities.
• Replace the period-entry and backdated calculations with projection or aggregate queries (grouped by product and transaction type).
• Make getTransactionReport read-only (@Transactional(readOnly = true)) and stop holding the connection during rendering.
• Show an error when the request times out instead of failing silently, and stop duplicate submits.
• Optional: cap the date range.

Config / server

• Pool: set testOnBorrow: true and maxAge: 600000.
• Enable GC logging and -XX:+HeapDumpOnOutOfMemoryError to confirm or rule out a leak on the next occurrence.
• Review -Xmx/-Xms against the VM's RAM (short-term relief).
• Confirm a health endpoint exists and configure Azure to use it.
• If a heap dump shows long-lived growth, check other suspects (Hibernate 2nd-level cache/Ehcache, unclosed file handles).