JPA N+1 and the Four JOIN FETCH Traps — MultipleBagFetchException, Pagination OOM, OneToOne LAZY

Table of contents

Why this article

“How do you solve N+1?” is the most common JPA interview question — which is exactly why depth shows up in the follow-up:

• What if you JOIN FETCH two collections?
• What about JOIN FETCH with setMaxResults?
• When does @OneToOne(LAZY) actually behave as LAZY, and when does it always fetch?

These three questions decide senior depth. This article measures all of them in one 4-depth domain.

1. The 4-depth domain

MerchantOwner (1)
  └─ OneToMany → Merchant (5/owner)
                     └─ OneToMany → AutoReplyRuleN1 (3/merchant)
                                         └─ OneToMany → ReplyHistory (4/rule)
                                                            └─ OneToOne mappedBy → ReplyHistoryMetadata

20 owners × 5 × 3 × 4 = 1,200 histories. Big enough to expose every trap.

2. S1 baseline — N+1

@Transactional(readOnly = true)
public void s1NPlusOne() {
    List<MerchantOwner> owners = ownerRepo.findAll();   // 1 SQL
    for (MerchantOwner o : owners) {
        for (Merchant m : o.getMerchants()) {            // LAZY — 1 SQL/owner
            for (AutoReplyRuleN1 r : m.getRules()) {     // LAZY — 1 SQL/merchant
                ...
            }
        }
    }
}

Expected: 1 + 20 + 100 = 121 SQL.

[Measured — Java/Spring Stage 2 / 2026-05-09] Identical code, run twice — only application.yml’s hibernate.default_batch_fetch_size differs:

Run A — default_batch_fetch_size OFF (default):

Run B — default_batch_fetch_size: 10:

S1’s 121 prep decomposition: 1 main + 20 (owners→merchants) + 100 (merchants→rules) = 121 — the measurement is a 3-depth traversal of the 4-depth domain (history is not iterated). Multiplicative growth per depth. ★ Critical finding: S4’s 1201 prep is unaffected by default_batch_fetch_size — the answer “just enable batch_fetch_size” to N+1 is half-true. ToOne LAZY’s proxy limitation lives outside the collection batch mechanism (see §6).

3. S2 — JOIN FETCH one level

@Query("SELECT DISTINCT o FROM MerchantOwner o LEFT JOIN FETCH o.merchants")
List<MerchantOwner> findAllJoinFetchMerchants();

One SQL with cartesian product, deduplicated by DISTINCT (Hibernate 6 also performs in-memory dedup automatically).

4. S3 — MultipleBagFetchException

@Query("SELECT DISTINCT m FROM Merchant m "
        + "LEFT JOIN FETCH m.rules r "
        + "LEFT JOIN FETCH m.owner.merchants")
List<Merchant> findAllTwoBags();

org.hibernate.loader.MultipleBagFetchException:
cannot simultaneously fetch multiple bags: [m.rules, owner.merchants]

Why

List is treated as a Bag — unordered. Two Bags joined together produce a cartesian whose row-to-element mapping is undefined for unordered collections.

Fixes

• Switch one to Set. Hibernate dedups automatically.
• Fetch one collection per query; let the second use @BatchSize.
• Or, fetch via two separate JPQL queries and let the persistence context stitch them.

Vlad Mihalcea — MultipleBagFetchException compares all three.

5. S5 — JOIN FETCH + Pagination → in-memory OOM

@Query("SELECT DISTINCT o FROM MerchantOwner o LEFT JOIN FETCH o.merchants")
List<MerchantOwner> findAllJoinFetchPaging(Pageable pageable);

Logs:

WARN  HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

5.1 Why in-memory paging — code trace

Assume 20 owners × 5 merchants and Pageable.of(0, 5) (we want 5 owners).

❌ If Hibernate naively appended LIMIT (hypothetical)

SELECT o.*, m.* FROM merchant_owner o
LEFT JOIN merchant m ON m.owner_id = o.id
ORDER BY o.id LIMIT 5;

The 5 rows returned:

→ only 1 owner returned (not the 5 requested). Worse case: if owner 1 had 3 merchants, row 4 would jump to owner 2’s merchants, leaving owner 2 with only 2 of its 5 — truncated child collection. Data integrity broken.

✅ Hibernate’s actual fallback

-- Hibernate's actual SQL — no LIMIT
SELECT o.*, m.* FROM merchant_owner o
LEFT JOIN merchant m ON m.owner_id = o.id
ORDER BY o.id;

// Hibernate internal flow (pseudocode):
List<Object[]> rawRows = jdbc.executeQuery(sqlWithoutLimit);  // all 100 rows hydrated
List<MerchantOwner> allOwners = dedupByOwnerId(rawRows);      // memory dedup → 20 owners
return allOwners.subList(0, 5);                               // ★ in-memory paging

→ correct 5 owners, but 100 rows traversed memory. Correctness paid in heap. The HHH90003004 WARN signals this fallback.

5.2 Production OOM scale — domain size matters

→ even with page size of just 20, the entire cartesian materialises in memory. A silent OOM — fine until your advertiser/store list grows, then sudden 5xx.

Fix

• Page parents only, then fetch children with an IN query.
• Or use @BatchSize so each lazy collection load batches K parents at a time.

Vlad Mihalcea — HHH000104 walks both patterns.

6. S4 — @OneToOne LAZY proxy limitation

@Entity class ReplyHistory {
    @OneToOne(mappedBy = "history", fetch = LAZY)
    private ReplyHistoryMetadata metadata;
}

Even though metadata is LAZY, every findAll() of ReplyHistory issues a SELECT for metadata.

Why

The owning side (@JoinColumn) can decide null-ness from the FK column — proxy works. The non-owning side (mappedBy) has no FK in the entity itself, so Hibernate must SELECT to know whether metadata is null. The intent of LAZY is impossible to honour without enhancement.

★ Why default_batch_fetch_size does NOT fix this

S4 stays at 1201 prep in Run B (with default_batch_fetch_size: 10). Two different LAZY mechanisms:

• Collection LAZY (OneToMany / ManyToMany): Hibernate wraps the collection in a PersistentBag/PersistentSet. On first access, default_batch_fetch_size rounds up other parents at the same depth and fetches them in one IN clause — batched.
• ToOne LAZY (OneToOne / ManyToOne): Hibernate inserts a proxy. The proxy must know “is this null?” — but for the non-owning side the FK is in the opposite table, so Hibernate fires a SELECT per row at materialisation time. No window to batch.

→ batch_fetch_size batches collection LAZY triggers via IN-clause grouping; it has no hook into the per-row ToOne SELECTs. This is the article’s core point: “N+1” hides two distinct mechanisms — the global config fixes the first, only @MapsId (or bytecode enhancement) fixes the second.

Fixes

• @MapsId — collapse 1:1 into PK = FK on the owning side, drop the mappedBy mapping. Single direction. Vlad’s standard recommendation.
• Bytecode Enhancement with @LazyToOne(LazyToOneOption.NO_PROXY) — needs the gradle plugin.

Vlad Mihalcea — OneToOne LAZY.

7. S6 — default_batch_fetch_size and N/K+1

S6 in this experiment runs the exact same code as S1. The only difference is one line in application.yml:

spring.jpa.properties.hibernate:
  default_batch_fetch_size: 10   # Hibernate default is -1 (off) — must be enabled explicitly

3-depth traversal SQL emitted:

-- Run A (config OFF) — baseline N+1
SELECT * FROM merchant_owner;                              -- 1
SELECT * FROM merchant WHERE owner_id = ?;                 -- ×20
SELECT * FROM auto_reply_rule_n1 WHERE merchant_id = ?;    -- ×100
-- = 121 SQL

-- Run B (default_batch_fetch_size: 10)
SELECT * FROM merchant_owner;                              -- 1
SELECT * FROM merchant WHERE owner_id IN (?,?,...,?);      -- ×2 (20/10)
SELECT * FROM auto_reply_rule_n1 WHERE merchant_id IN (?,?,...,?);  -- ×10 (100/10)
-- = 13 SQL

121 → 13 (9.3×) without changing a single line of application code. The value of default_batch_fetch_size as a global safety net. But — as measured in §6 — it does not fix @OneToOne non-owning LAZY. The full prescription is the config plus @MapsId.

7.1 Step-by-step — how Hibernate’s batch fetch actually works

How does paging 20 owners with default_batch_fetch_size: 10 enabled end up at 13 SQL? Let’s trace it through the persistence context.

1:N row layout

merchant_owner (20 rows)              merchant (100 rows, 5/owner)
┌────┬──────────┐                     ┌────┬──────────┬──────────┐
│ id │ name     │                     │ id │ name     │ owner_id │
├────┼──────────┤                     ├────┼──────────┼──────────┤
│  1 │ owner-1  │ ─┐                  │  1 │ m-1-0    │    1     │ ◄┐
│  2 │ owner-2  │  │                  │  2 │ m-1-1    │    1     │ ◄┤  owner=1's
│ ...│ ...      │  │   1:N            │ ...│ ...      │   ...    │ ◄┤  5 merchants
│ 20 │ owner-20 │ ─┘                  │  5 │ m-1-4    │    1     │ ◄┘
└────┴──────────┘                     │ ...│ ...      │   ...    │
                                      │100 │ m-20-4   │   20     │
                                      └────┴──────────┴──────────┘

Step 1: parent paging — initial persistence context

-- SQL #1
SELECT * FROM merchant_owner ORDER BY id LIMIT 20;

PersistenceContext (1st-level cache):
  owner#1  → merchants: ⏳ PersistentBag (NOT initialized)
  owner#2  → merchants: ⏳ PersistentBag (NOT initialized)
  ...
  owner#20 → merchants: ⏳ PersistentBag (NOT initialized)

→ 20 owners hydrated; each merchants slot holds an uninitialized PersistentBag. No merchant SQL fired yet.

Step 2: forEach first iteration → owner#1 LAZY trigger

owner#1.getMerchants().size() is called. Hibernate’s batch decision:

1. owner#1's merchants are needed
2. scan persistence context for *other uninitialized PersistentBags* → owner#2..#20 (19)
3. batch_size = 10 → group owner#1 + 9 others → owner_id ∈ {1, 2, ..., 10}
4. fire one IN-clause SQL, hydrate all 10 owners' merchants

-- SQL #2
SELECT * FROM merchant WHERE owner_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

→ only owner#1 was needed, but 10 owners’ merchants were fetched in one SQL. This is the heart of batch fetching — current need + likely upcoming needs combined as a look-ahead.

PersistenceContext after Step 2:
  owner#1..#10 → merchants: ✅ hydrated (1..10 batch)
  owner#11..#20 → merchants: ⏳ NOT initialized

Steps 3~10: owner#2..#10 — all cache hits

owner#2.getMerchants().size();   // ✅ already hydrated → no SQL
...
owner#10.getMerchants().size();  // ✅ same

Step 11: owner#11 → second batch

-- SQL #3
SELECT * FROM merchant WHERE owner_id IN (11, 12, ..., 20);

Steps 12~20: all cache hits

Result

Formula: 1 + ⌈N/K⌉. For the 3-depth traversal in this EXP (down to rules), it’s 1 + 2 + 10 = 13 SQL — the §7 measurement.

Key takeaways:

• zero changes to application code — o.getMerchants() calls remain the same
• after the first trigger, every subsequent call is a cache hit
• iteration order doesn’t matter — whichever owner triggers first kicks off the batch
• no cartesian risk — IN clauses can’t produce row multiplication

7.2 What batch fetch does NOT fix

But (§6.2) @OneToOne non-owning LAZY (S4) stays at 1201 prep — only @MapsId fixes it. The one-line config is half the answer; that’s this article’s core message.

7.5 Easy to confuse — DTO projection vs @Transactional(readOnly = true)

These are completely different dimensions — meant to be used together, but each fixes a different problem.

Direct evidence from this EXP — S1 already uses @Transactional(readOnly = true) and still emits 121 prep:

@Transactional(readOnly = true)         // ← already readOnly!
public Stats s1NPlusOne() {
    List<MerchantOwner> owners = ownerRepo.findAllNoFetch();
    for (MerchantOwner o : owners) {
        for (Merchant m : o.getMerchants()) {       // ← LAZY trigger
            sumRules += m.getRules().size();
        }
    }
}

→ readOnly does nothing for the fetch traps.

7.5.1 What @Transactional(readOnly = true) actually does

1. Sets FlushMode.MANUAL → no auto-flush → no INSERT/UPDATE/DELETE
2. ★ no snapshot taken — entity hydration skips the original-state copy. Dirty-check cost = 0 (W4 P2 EXP-13 measured 132×)
3. Passes a read-only hint to the JDBC driver

What it does NOT fix:

• ✅ entities still hydrate, proxies still alive
• ✅ LAZY triggers still fire → N+1 unchanged
• ✅ JOIN FETCH / paging / MultipleBag / OneToOne LAZY traps unchanged

7.5.2 What DTO projection actually does

1. Maps SQL results directly to a POJO constructor → no proxy is created
2. Not registered in the persistence context — first-level cache cost = 0
3. No snapshot taken (same effect as readOnly)
4. ★ since there is no proxy, LAZY triggers are physically impossible → N+1 traps fail to start

7.5.3 Side-by-side

7.5.4 In practice — use both together

@Transactional(readOnly = true)              // ← snapshot savings (W4 P2 132×)
public List<OwnerSummaryDto> summaries() {
    return ownerRepo.findOwnerSummaries();   // ← DTO projection (zero N+1)
}

→ The two tools act on different layers. readOnly fixes the write side (snapshot/flush) cost; DTO projection fixes the read side (proxy/LAZY/cache) traps. “readOnly solves N+1” is wrong — S1’s 121 prep is the direct counter-evidence.

7.6 Deep hierarchy — dedicated single-shot query (QueryDSL / DTO projection)

The @BatchSize chain (§7.1) is a safety net for generic entity traversal, not the answer for every deep hierarchy. If a specific screen / API endpoint demands a deep hierarchy, a dedicated single-shot query is the right move. §8.2’s industry pattern A (Naver / Kakao style — write JPA + read QueryDSL/jOOQ) institutionalises exactly this principle.

7.6.1 Four options for a 4-depth single-shot query

(A) JPQL JOIN FETCH (entity) — depth-limited

@Query("""
    SELECT DISTINCT o FROM MerchantOwner o
    LEFT JOIN FETCH o.merchants m
    LEFT JOIN FETCH m.rules
""")
List<MerchantOwner> findOwnersWithMerchantsAndRules();

❌ m.rules is also a List → MultipleBagFetchException (Hibernate 6 startup HQL validation rejects it). Switching to Set avoids the exception, but the cartesian still grows: 20 × 5 × 3 = 300 rows, plus history = 1,200 rows → result hydration cost explodes; pagination impossible. → entity JOIN FETCH tops out at depth 1, maybe 2.

(B) JPQL DTO projection (★) — unlimited depth, all traps avoided

public record OwnerHierarchyRow(
    Long ownerId, String ownerName,
    Long merchantId, String merchantName,
    Long ruleId, String keyword,
    Long historyId, String matchedText
) {}

@Query("""
    SELECT new com.example.OwnerHierarchyRow(
        o.id, o.name,
        m.id, m.name,
        r.id, r.keyword,
        h.id, h.matchedText
    )
    FROM MerchantOwner o
    LEFT JOIN o.merchants m
    LEFT JOIN m.rules r
    LEFT JOIN r.histories h
    WHERE o.id IN :ownerIds
    ORDER BY o.id, m.id, r.id, h.id
""")
List<OwnerHierarchyRow> findHierarchy(@Param("ownerIds") List<Long> ownerIds);

The SQL emitted — 1 SQL:

SELECT o.id, o.name, m.id, m.name, r.id, r.keyword, h.id, h.matched_text
FROM merchant_owner o
LEFT JOIN merchant m ON m.owner_id = o.id
LEFT JOIN auto_reply_rule_n1 r ON r.merchant_id = m.id
LEFT JOIN reply_history h ON h.rule_id = r.id
WHERE o.id IN (?, ?, ...)
ORDER BY o.id, m.id, r.id, h.id;

→ 1,200 rows arrive flat. No proxy is created, no entity hydrated:

• ✅ MultipleBagFetchException avoided (no entity collection mapping)
• ✅ Zero N+1 (no proxy)
• ✅ Pagination possible (paginate parents only, then IN)
• ✅ Zero snapshot / first-level-cache cost

(C) QueryDSL DTO projection (★★) — type-safe + dynamic

public List<OwnerHierarchyRow> findHierarchy(List<Long> ownerIds, String keywordPrefix) {
    return queryFactory
        .select(Projections.constructor(OwnerHierarchyRow.class,
            owner.id, owner.name,
            merchant.id, merchant.name,
            rule.id, rule.keyword,
            history.id, history.matchedText))
        .from(owner)
        .leftJoin(owner.merchants, merchant)
        .leftJoin(merchant.rules, rule)
        .leftJoin(rule.histories, history)
        .where(
            owner.id.in(ownerIds),
            keywordPrefix == null ? null : rule.keyword.startsWith(keywordPrefix)  // ← dynamic
        )
        .orderBy(owner.id.asc(), merchant.id.asc(), rule.id.asc(), history.id.asc())
        .fetch();
}

Advantages over JPQL: ✅ type-safe (compile-time column refs) ✅ dynamic where ✅ refactor-safe. Why Korean big-tech adopts QueryDSL on the read side — read paths usually have high condition variability per screen, and QueryDSL fits naturally.

(D) Native SQL — special cases (window functions / CTE / complex aggregations)

When you need RDBMS-native features like GROUP BY with rollups, window functions, or CTEs.

7.6.2 SQL count comparison — 4-depth, 1,200 rows

→ The DTO multi-JOIN single-shot dominates round-trips. The 1× vs 43× gap means tens of times latency difference if the DB is far away (e.g. cross-region).

7.6.3 Two-axis decision — entity/DTO × JPQL/QueryDSL

DTO projection itself is doable in either JPQL or QueryDSL. The two axes are independent.

Axis 1 (return type) trade-offs:

• entity → BatchSize works, dirty checking available, but the traps (MultipleBag / paging) remain
• DTO → no proxy, all traps avoided, but cascade / dirty checking unavailable

Axis 2 (authoring tool) trade-offs:

• JPQL → concise, static — fine for simple screens
• QueryDSL → type-safe, strong dynamic where, great IDE support — the standard for complex / dynamic screens

7.6.4 Three-track production default

A real codebase typically runs all three tracks — chosen per screen / endpoint.

Decision in one line:

• “Is this deep hierarchy bound to a specific screen / API?” → YES → DTO + (QueryDSL or JPQL) single-shot
• NO, it’s a generic entity traversal → Entity + BatchSize chain

8. Operational rules — Vlad’s 5 commandments + 4 industry patterns + decision tree

8.1 Vlad Mihalcea’s 5 commandments

Vlad Mihalcea — Hibernate ORM’s most active external contributor. His blog is the de facto standard for production-grade Hibernate. Mapped to this article’s scenarios:

The 6 scenarios are precisely each commandment violated.

8.2 What real teams pick — 4 industry patterns

Korean big-tech commerce/content teams typically run pattern A (write JPA + read QueryDSL). Whichever pattern you pick, the N+1 mechanisms here still matter — pattern A’s write side still triggers LAZY through dirty checking; pattern B with EntityGraph alone walks into the pagination trap.

8.3 Decision tree

8.4 Production default this article recommends

1. Always enable default_batch_fetch_size: 10 (measured: 9.3× drop on collection LAZY)
2. One JOIN FETCH + @BatchSize for the rest (commandment 4)
3. DTO projection for read-only views (commandment 3)
4. @MapsId unidirectional for OneToOne (commandment 5; batch_fetch_size cannot save you)

9. Conclusion

JOIN FETCH alone is not the answer. The trade-off space spans List vs Set, owning vs non-owning, pagination compatibility, the boundary between N+1 that batch_fetch_size can fix and N+1 it cannot, and how Hibernate processes cartesian products. Without that map, the failure modes (OOM, surprise SELECTs) reproduce only in production.

Three-line summary:

1. default_batch_fetch_size: 10 — one line, 9.3× drop on collection LAZY (S1 121 → 13). Always on.
2. But it does NOT fix @OneToOne non-owning LAZY (S4 1201 → 1201). Separate prescription: @MapsId unidirectional.
3. DTO projection for read-only views + JOIN FETCH + BatchSize for transactional views. Two trails, picked per screen.

Next: JPA saveAll IDENTITY bulk-insert trap.

References

Official

• Hibernate ORM — Fetching
• Hibernate ORM — @BatchSize

Vlad Mihalcea

• N+1 Query Problem
• MultipleBagFetchException
• HHH000104
• OneToOne LAZY

External

• Baeldung — @BatchSize

Sister posts

• JPA Dirty Checking cost
• JPA Optimistic Lock + retry stampede