I use Spring Boot 2.7.18, Java 17, EntityManager, Criteria API and Executors.newFixedThreadPool(20). My database contains data with different types of payments (for example, "expense" and "replenishment").

I have a method which calculates statistics grouping payments by types. I try to execute requests to the database asynchronously. I use local EntityManager for each request to avoid problems with threads.

And sometimes I get correct data, which contains both "expense" and "replenishment" payments, and sometimes I get wrong data, which contains only "replenishment" payments. So one request in one monent returns one data, and the same request in another moment returns another data.

I tried many different approaches, but still I get unexpected result.

Asynchronous code:

CompletableFuture<List<PaymentCountDto>> paymentCountsFuture = CompletableFuture.supplyAsync(() -> {
            EntityManager entityManager = entityManagerFactory.createEntityManager();
            try {
                entityManager.getTransaction().begin();
                List<PaymentCountDto> result = paymentService.getPaymentCounts(entityManager, paymentRequest, specification);
                entityManager.getTransaction()mit();
                return result;
            } finally {
                if (entityManager.isOpen()) {
                    entityManager.close();
                }
            }
        }, executor);

Custom repository:

@PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<StatusStatisticPayment> getStatusStatisticPaymentsBySpecificationAndCurrency(EntityManager entityManager, Specification<Payment> specification, Currency currency) {
        if (entityManager == null) {
            entityManager = this.entityManager;
        }
        entityManager.clear();
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<StatusStatisticPayment> query = builder.createQuery(StatusStatisticPayment.class);
        Root<Payment> root = query.from(Payment.class);
        Predicate specificationPredicate = specification.toPredicate(root, query, builder);
        Predicate currencyPredicate = builder.equal(root.get("cardCurrency"), currency.getCurrency());
        query.where(builder.and(specificationPredicate, currencyPredicate));
        Expression<String> groupByStatus = root.get("status");
        Expression<BigDecimal> sumTransactionSum = builder.sum(root.get("transactionSum"));
        Expression<BigDecimal> sumTransactionCommission = builder.sum(root.get("transactionCommission"));
        Expression<BigDecimal> sumTransactionValue = builder.sum(root.get("transactionValue"));
        Expression<Long> count = builder.count(root);
        query.select(builder.construct(StatusStatisticPayment.class, groupByStatus, sumTransactionSum, sumTransactionCommission, sumTransactionValue, count));
        query.groupBy(groupByStatus);
        return entityManager.createQuery(query).getResultList();
    }

Please could anyone help me with this problem? I spent several days on this.

Thank you in advance!

I use Spring Boot 2.7.18, Java 17, EntityManager, Criteria API and Executors.newFixedThreadPool(20). My database contains data with different types of payments (for example, "expense" and "replenishment").

I have a method which calculates statistics grouping payments by types. I try to execute requests to the database asynchronously. I use local EntityManager for each request to avoid problems with threads.

And sometimes I get correct data, which contains both "expense" and "replenishment" payments, and sometimes I get wrong data, which contains only "replenishment" payments. So one request in one monent returns one data, and the same request in another moment returns another data.

I tried many different approaches, but still I get unexpected result.

Asynchronous code:

CompletableFuture<List<PaymentCountDto>> paymentCountsFuture = CompletableFuture.supplyAsync(() -> {
            EntityManager entityManager = entityManagerFactory.createEntityManager();
            try {
                entityManager.getTransaction().begin();
                List<PaymentCountDto> result = paymentService.getPaymentCounts(entityManager, paymentRequest, specification);
                entityManager.getTransaction()mit();
                return result;
            } finally {
                if (entityManager.isOpen()) {
                    entityManager.close();
                }
            }
        }, executor);

Custom repository:

@PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<StatusStatisticPayment> getStatusStatisticPaymentsBySpecificationAndCurrency(EntityManager entityManager, Specification<Payment> specification, Currency currency) {
        if (entityManager == null) {
            entityManager = this.entityManager;
        }
        entityManager.clear();
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<StatusStatisticPayment> query = builder.createQuery(StatusStatisticPayment.class);
        Root<Payment> root = query.from(Payment.class);
        Predicate specificationPredicate = specification.toPredicate(root, query, builder);
        Predicate currencyPredicate = builder.equal(root.get("cardCurrency"), currency.getCurrency());
        query.where(builder.and(specificationPredicate, currencyPredicate));
        Expression<String> groupByStatus = root.get("status");
        Expression<BigDecimal> sumTransactionSum = builder.sum(root.get("transactionSum"));
        Expression<BigDecimal> sumTransactionCommission = builder.sum(root.get("transactionCommission"));
        Expression<BigDecimal> sumTransactionValue = builder.sum(root.get("transactionValue"));
        Expression<Long> count = builder.count(root);
        query.select(builder.construct(StatusStatisticPayment.class, groupByStatus, sumTransactionSum, sumTransactionCommission, sumTransactionValue, count));
        query.groupBy(groupByStatus);
        return entityManager.createQuery(query).getResultList();
    }

Please could anyone help me with this problem? I spent several days on this.

Thank you in advance!

• java
• postgresql
• spring-boot
• multithreading
• entitymanager

Share Improve this question Follow edited Mar 21 at 13:15 Maksim Banit 18533 bronze badges asked Mar 21 at 12:31 Dmitry TishDmitry Tish 111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Instead of calling the repository asynchronously and manually managing the EntityManager, try using @Async in the service layer like this:

@Async
@Transactional(readOnly = true)
public CompletableFuture<List<StatusStatisticPayment>> getStats(...) {
    return CompletableFuturepletedFuture(repository.getStatusStatistic(...));
}

This approach is safer and much simpler.