The Hibernate MultipleBagFetchException

Introduction

In the previous article on the N+1 query problem, we saw that one of the ways to resolve the problem was to change the fetch type of the relation to lazy and then use a join fetch directive to get our query do a join instead of having to do multiple round trips to the database. It happens that as soon as you have more than one join for ToMany relations, hibernate will throw the MultipleBagFetchException.

We will have a look at exactly how to produce the exception and then discuss how to solve it without falling back to N+1 queries.

The Model

Below is the same model we used in the N+1 query problem article.

The productVariant is mapped as shown below.

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", fetch = FetchType.LAZY)
    private List<ProductVariant> productVariants;

The review is mapped as shown below

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", fetch = FetchType.LAZY)
    private List<Review> reviews;

Both relations have a fetch type of lazy so that they are loaded only on demand.

Demo

Below is code that will produce the MultipleBagFetchException.

We configure the repository with 2 join directives

public interface ProductRepo extends JpaRepository<Product, Long> {
    @Override
    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"productVariants", "reviews"})
    List<Product> findAllById(Iterable<Long> ids);
}

We override the “findAllById” method. In line 3 we use the EntityGraph annotation to indicate that joins should be made to 2 relations namely "productVariants" and "reviews".

We access the method from our service.

    public List<Product> fetchProducts(List<Long> ids) {
        return productRepo.findAllById(ids);
    }

We invoke the service method from our test

    @Test
    void when2JoinsOnListRelations_expectMultiBagException() {
        final Product product = createProduct();
        final Product savedProduct = productService.saveProduct(product);
        final Product savedProduct12 = productService.saveProduct(createProduct());

        final List<Long> ids = List.of(savedProduct.getId(), savedProduct12.getId());
        final ThrowingCallable fetchProducts = () -> productService.fetchProducts(ids);
        assertThatThrownBy(fetchProducts).hasRootCauseInstanceOf(MultipleBagFetchException.class);
    }

The exception thrown is shown below

Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.codemajor.jpademo.product.entity.Product.productVariants, com.codemajor.jpademo.product.entity.Product.reviews]

Resolving the MultipleBagFetchException

When we have 2 joins as shown, the results will be the cartesian product. The reason hibernate throws the exception is because of the duplicates that are formed. So 2 problems are introduced, namely the inefficiency of the cartesian product and the duplicates which hibernate does not want to deal with.

If we model the relations as sets instead of lists, hibernate will no more throw the exception. However, the underlying inefficiency of the cartesian product still persists.

In order to resolve both, a middle ground has to be found. We will create 2 queries: each will fetch a different part of the required graph of entities.

public interface ProductRepo extends JpaRepository<Product, Long> {
    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"productVariants"})
    List<Product> findAllByIdIn(Iterable<Long> ids);

    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"reviews"})
    List<Product> findByIdIn(Iterable<Long> ids);
}

Then we invoke both within the same transaction boundary so that hibernate merges the results for us.

    public List<Product> fetchProductsWithVariantsAndReviews(List<Long> ids) {
        productRepo.findByIdIn(ids);
        return productRepo.findAllByIdIn(ids);
    }

We can then test as below.

    @Test
    void when2RelationsWithListSequentiallyFetched_expect2SelectsAndNoXPlus1Selects() {
        List<Product> products = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            products.add(createProduct());
        }
        productService.saveProducts(products);

        final Statement statement = SqlStatementHolder.initStatement();

        final List<Product> fetchedProds = productService.fetchProductsWithVariantsAndReviews(products.stream()
                                                                                                   .map(product -> product.getId())
                                                                                                   .toList());

        statement.assertThatSelect().hasCount(2);
        statement.assertThatSelect().executedOnJoinTables(CountStrategyFactory.times(1), "product", "product_variant");
        statement.assertThatSelect().executedOnJoinTables(CountStrategyFactory.times(1), "product", "review");
        final Product fetchedProduct = fetchedProds.get(0);
        assertThat(fetchedProduct.getProductVariants()).isNotEmpty();
        assertThat(fetchedProduct.getReviews()).isNotEmpty();
    }

The result is 2 queries that have better efficiency than the cartesian product and that avoid the MultipleBagFetchException.

Conclusion

We have seen what causes the MultipleBagFetchException and not only how to solve it but also how to resolve the underlying performance issues.

The accompanying code can be found here in the multibag branch

Code Major