A job streams a multi-million-row HQL query using a cursor and a correctly configured JDBC fetch size, yet it still runs out of heap. What is filling memory, and how do you fix it?
answer
- Cursor fixes the wire, not the session
- Persistence context is unbounded
- readOnly halves cost, clear() bounds it
- flush() before clear() when writing
- Heap dump → StatefulPersistenceContext
basics
~20 sThe persistence context. Every entity pulled from the cursor is registered with a state snapshot and stays strongly referenced. Fix: load read-only and call session.clear() every N rows — or select a projection instead of entities.
solid answer
~50 sStreaming fixes the *transport*, not the *session*. Each row you pull is hydrated into a managed entity, registered in the persistence context with a loaded-state snapshot, and held by a strong reference until the session closes. Over millions of rows that is a guaranteed heap exhaustion, cursor or no cursor. The fix has three parts: 1. **Load read-only** (`org.hibernate.readOnly` hint or `setDefaultReadOnly`) — drops the snapshot, roughly halving per-entity cost, and removes the flush-time scan. 2. **Detach periodically** — `session.clear()` every few hundred/thousand rows (flush first if you are also writing). This is what actually bounds memory. 3. **Don't fetch entities if you don't need them** — a scalar/DTO projection never enters the persistence context at all. Also rule out: the driver silently buffering the whole result (fetch-size conditions not met), a collection `join fetch` inflating rows, and the second-level or query cache retaining what you read.
code
java · 17 linessession.beginTransaction();
int i = 0;
try (ScrollableResults<Order> rows = session
.createQuery("from Order o where o.year = 2025", Order.class)
.setReadOnly(true)
.setFetchSize(500)
.setCacheMode(CacheMode.IGNORE)
.scroll(ScrollMode.FORWARD_ONLY)) {
while (rows.next()) {
writer.write(toDto(rows.get()));
if (++i % 1000 == 0) {
session.clear();
}
}
}
session.getTransaction().commit();go deeper
Know the headline: entities accumulate in the persistence context, so periodically clear the session.
Give the three-part recipe (read-only, periodic clear, prefer projections) and explain why the cursor alone does not help.
Diagnose rather than guess — heap dump, dominator tree, time-to-first-row — and enumerate the other suspects: unmet driver streaming conditions, collection fetch joins, L2 cache pollution, a non-streaming sink.
Question the design: whether the work belongs in the database, whether a chunked restartable read beats one long transaction, and what the pinned connection and long-lived snapshot cost the rest of the system.
## Streaming solves one problem, not the problem When a large-read job OOMs after being "fixed" with a cursor, the mental model to correct is that there are **two** places rows can pile up: 1. **Between the database and the driver** — solved by a server-side cursor plus a sane JDBC fetch size. 2. **Inside the Hibernate session** — not solved by any of that. Every entity you pull from a `ScrollableResults` or `Stream` is a fully managed entity. Hibernate registers it in the persistence context (first-level cache) keyed by type and id, stores a loaded-state snapshot for dirty checking, and holds a strong reference to both for the lifetime of the session. Nothing evicts entries automatically; the persistence context is unbounded by design, because its job is identity and change tracking, not caching with a policy. Ten million entities, each with a snapshot, is simply ten million-plus objects retained. ## The remedy, in order ### 1. Make the read read-only `query.setHint("org.hibernate.readOnly", true)` or `session.setDefaultReadOnly(true)` stops Hibernate keeping the snapshot. That is roughly a 40–50% cut in retained bytes per entity and removes dirty checking cost at flush. It is a strict improvement for a read-only job — but it does **not** bound memory, because the entities themselves are still registered. ### 2. Clear the persistence context periodically This is the actual fix. Every N rows (a few hundred to a few thousand — tune it), call `session.clear()`. That detaches everything and lets the garbage collector reclaim it, so retained memory becomes O(batch) instead of O(result set). Two cautions. If the job also writes, `flush()` before `clear()` or you discard pending changes silently. And `clear()` detaches objects you already handed downstream: if your consumer holds references and later touches an uninitialized lazy association, it fails. Consume each row fully — map it to a DTO, write it out — before the next clear. ### 3. Prefer not fetching entities Ask whether the job needs managed objects at all. `select new com.acme.Row(o.id, o.total) from Order o` or a scalar projection produces plain objects that never enter the persistence context, so the whole problem disappears and hydration is cheaper too. Streaming *entities* is what you do when you genuinely need mapped behaviour or associations. A stateless API is the other route: no first-level cache at all, everything detached by construction. ## Rule out the other suspects **The driver never streamed.** If time-to-first-row is long and heap spikes before your first callback, the driver buffered the whole result. Recheck the driver's conditions (autocommit off, forward-only, positive fetch size on PostgreSQL; the streaming or cursor-fetch mode on MySQL). Cursor code with an unmet precondition looks correct and behaves like `getResultList()`. **A collection join fetch.** `from Order o join fetch o.lines` multiplies rows and forces Hibernate to buffer to assemble each parent. It is also documented as unsafe to scroll. Remove the fetch join; if you need children, process them per parent with a separate bounded query, or drive the query from the child side and group. **Caches.** If the entity is second-level cached, a full-table walk pours the whole table into the L2 region, evicting useful entries and consuming memory outside the session. Set `CacheMode.IGNORE`/`GET` for the job. The query cache should never be used for a huge result. **Consumer-side accumulation.** Sometimes Hibernate is innocent: the code collects results into a list, builds one giant JSON document, or uses a `Stream.collect(...)` that materializes everything anyway. Streaming end-to-end means the sink is streaming too. ## How to prove it rather than guess Take a heap dump at the point of failure and look at the dominator tree. A `StatefulPersistenceContext` (or its entity/collection maps) retaining hundreds of megabytes is a diagnosis, not a hypothesis. If instead the driver's result-set object is the dominator, your fetch-size configuration is the culprit. If neither, look at your own consumer. ## The canonical shape Open session and transaction; issue the query read-only with an explicit fetch size and no collection fetch joins; scroll; per row, map and emit; every N rows, `clear()`; close everything in try-with-resources. If the job also writes, `flush()` then `clear()` on the same cadence, matched to the JDBC batch size.
- How do you choose the clear() interval?It is a memory-versus-overhead trade, and a few hundred to a few thousand rows covers most cases. Too small adds churn and, in a write job, defeats JDBC batching; too large lets the persistence context grow again. If the job writes, align the interval with the JDBC batch size so a flush maps cleanly onto full batches, and confirm the choice by watching heap on a realistic data volume.
- What breaks if you call clear() while downstream code still holds the entities you emitted?They become detached. Reading already-initialized scalar fields still works, but touching an uninitialized lazy association fails because the proxy has no session, and the objects no longer have identity in that persistence context — a later load of the same row returns a different instance. The rule is to fully consume or map each row to a detached DTO before the next clear.
- When would you not stream at all, even for a large result?When the work can be pushed into the database as a set-based statement, when a chunked read with short transactions is preferable operationally, or when the result only needs a few columns and a projection with ordinary paging is simpler. Streaming pins a connection and holds a transaction open for the whole job, which is a real cost you should only pay when you need row-by-row processing in the application.
You widened the pipe from the reservoir but kept every bucket you filled stacked in the room. The flow is fine; the room is the problem.
saying these in an interview costs you the question
- Believing a cursor or getResultStream() gives constant memory on its own.
- Calling clear() without flush() in a job that also writes, silently dropping changes.
- Adding a collection join fetch to a streamed query.
- Blaming the driver without taking a heap dump to see what actually dominates.
- Assuming the second-level cache is harmless during a full-table walk.