Lazy Loading, Query Storms, and Lost Updates in a JPA Portal

Why it happens. With a transaction-scoped persistence context (the default for @Stateless + EntityManager injected with @PersistenceContext), the persistence context is closed when findOrders returns and the JTA transaction commits. For a lazy @OneToMany, Hibernate returns a proxy/PersistentBag holding only the owning side's key; the first getLines() outside the transaction needs a live persistence context and connection, and throws.

Fix 1 - fetch join. SELECT DISTINCT o FROM Order o LEFT JOIN FETCH o.lines WHERE o.customer.id = :id. One query, no N+1. Trade-off: DISTINCT is needed to de-duplicate the parent rows, and combining a fetch join with setMaxResults makes the database limit joined rows, so pagination becomes wrong (Hibernate falls back to in-memory paging and warns). Fetching two List collections at once also throws MultipleBagFetchException.

Fix 2 - @EntityGraph. em.find(Order.class, id, hints) or a repository method annotated @EntityGraph(attributePaths = "lines"). Same effect as fix 1, declarative, composable, and pagination-safe if you fetch one collection at a time. This is the usual production answer.

Fix 3 - keep the persistence context open into the view. A stateful session bean with an extended persistence context, or a servlet filter that binds an EntityManager to the request, lets the JSF layer traverse lazies. Trade-off: the persistence context accumulates entities for the whole request/session, entities become stale (no refresh), memory grows, and you can accidentally flush changes from the view. Use it deliberately, not as a global default.

DTO projection is the cleanest alternative for a read-only screen: a JPQL constructor expression returning exactly the columns the page prints, with no managed entities at all. The real defect is mixing a transaction-scoped persistence context with view-layer traversal.

Diagnosis. This is the classic N+1 select: one query for the 40 orders, then one query per order to initialize its lazy lines collection as the page iterates. Forty orders is small; the same page at 500 orders issues 501 statements, each with a network round trip, and the 6-second render is mostly latency, not database work. Note the correlation: statement count tracks row count, not data volume.

Fixes.

1. LEFT JOIN FETCH o.lines in the query that loads the orders - reduces 41 to 1. Add DISTINCT because the join multiplies parent rows.
2. @EntityGraph(attributePaths = {"lines"}) on the finder - same SQL shape, easier to keep per use case.
3. @BatchSize(size = 25) (Hibernate) or the global hibernate.default_batch_fetch_size - turns N queries into N/25 IN (...) queries. Useful when you cannot change every query, and it is pagination-safe.
4. DTO projection with a constructor expression - no collection initialization at all, and the smallest result set.

The pagination trap. If the page uses setFirstResult/setMaxResults, a collection fetch join applies the limit to joined rows. With 3 lines per order and maxResults = 20, you get 6 or 7 orders, and Hibernate 4/5 emits a warning and may materialize the whole result set in memory to page correctly. Options: page on order ids first (SELECT o.id ... ORDER BY o.id, then a second query with WHERE o.id IN (:ids) and the fetch join), use @BatchSize instead of a join for the collection, or use @EntityGraph with only to-one associations on the paged query.

Verification. Enable SQL logging or a statement-count assertion in a test; the acceptance criterion is a constant statement count as order count grows.

Why nothing was detected. Without a version column, em.merge(detachedOrder) issues an unconditional UPDATE ... WHERE id = ? that writes every column from the detached snapshot. The second agent's merge overwrites the first agent's committed row; there is no predicate that could fail, so the database has no way to know the write was based on stale data. This is a lost update, and it is invisible afterwards - the row simply reflects the last writer.

What to add. A surrogate version field on the aggregate root:

@Version
private long version;

Hibernate then emits UPDATE orders SET ..., version = version + 1 WHERE id = ? AND version = ? and checks the affected row count. If another transaction already incremented the version, the row count is 0 and the flush throws OptimisticLockException (wrapping StaleObjectStateException). Notes that matter:

• The version increments on flush, not on setVersion. Do not set it yourself.
• With merge() on a detached entity, the version in the detached instance is carried into the WHERE clause; never rebuild the detached entity from client input without carrying the original version, or you are back to last-write-wins.
• Optimistic locking detects the conflict at commit time, so a user may lose work. Detect early: load the entity in a @ViewScoped bean or send the version to the browser and check it on submit.
• The entity must not use @OptimisticLock(excluded = true) on mutable business fields.

What to do with the exception. Catch OptimisticLockException at the action-method boundary (it marks the transaction rollback-only, so you cannot simply retry in the same transaction). Re-read the current row, show both versions to the agent, and let them merge. Also consider LockModeType.PESSIMISTIC_WRITE for the short critical section where a human is not involved, remembering that it holds a database row lock until commit.