Spring boot transaction hanging if two transactions are updating the same row

Question

I'm trying to update the same row in two separate transactions that I'm managing programmatically.

Here is the test:

    @Sql("classpath:sql/balance/account_with_balance_EE.sql")
    @Test
    void changeNoLock() {
        long balanceId = 10000L;
        DefaultTransactionDefinition firstTransactionDefinition = new DefaultTransactionDefinition();
        DefaultTransactionDefinition secondTransactionDefinition = new DefaultTransactionDefinition();
        firstTransactionDefinition.setName("firstTransaction");
        secondTransactionDefinition.setName("secondTransaction");
        firstTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
        secondTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);

        TransactionStatus firstTransactionStatus = txManager.getTransaction(firstTransactionDefinition);
        balanceService.change(balanceId, BigDecimal.ONE);

        TransactionStatus secondTransactionStatus = txManager.getTransaction(secondTransactionDefinition);
        balanceService.change(balanceId, BigDecimal.ONE);

        txManager.commit(firstTransactionStatus);
        txManager.commit(secondTransactionStatus);


        BalanceDto balanceDto = balanceService.find(balanceId);

        assertEquals(new BigDecimal("2.00"), balanceDto.getBalanceAmount());
    }

balanceService#change method is transactional with default propagation and default isolation.

When I run this test it's just hanging, the only time it doesn't hang is when I commit first transaction before starting the second why. Why is this happening?

I'm using Java 17 with Spring boot 3 and a Postgres database.

Answer 1

Looks like you have deadlocks, you could create a retry mechanism to handle deadlock situations, and catch deadlock exceptions and retry the transactions after a delay

import org.springframework.dao.DeadlockLoserDataAccessException;

@Test
void changeWithRetry() {
    long balanceId = 10000L;
    DefaultTransactionDefinition firstTransactionDefinition = new DefaultTransactionDefinition();
    DefaultTransactionDefinition secondTransactionDefinition = new DefaultTransactionDefinition();
    firstTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    secondTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);

    int maxAttempts = 3;

    BiConsumer<DefaultTransactionDefinition, String> retryLogic = (transactionDefinition, name) -> {
        int attempt = 0;
        while (attempt < maxAttempts) {
            TransactionStatus transactionStatus = txManager.getTransaction(transactionDefinition);
            try {
                balanceService.change(balanceId, BigDecimal.ONE);
                txManager.commit(transactionStatus);
                break;
            } catch (DeadlockLoserDataAccessException e) {
                txManager.rollback(transactionStatus);
                attempt++;
                if (attempt >= maxAttempts) {
                    throw e;
                } else {
                    try {
                        Thread.sleep(1000);
                    } catch (InterruptedException ex) {
                        Thread.currentThread().interrupt();
                    }
                }
            }
        }
    };

    retryLogic.accept(firstTransactionDefinition, "firstTransaction");
    retryLogic.accept(secondTransactionDefinition, "secondTransaction");

    BalanceDto balanceDto = balanceService.find(balanceId);
    assertEquals(new BigDecimal("2.00"), balanceDto.getBalanceAmount());
}

Or a simplified and with best performance version

@Test
void changeWithoutRetry() {
    long balanceId = 10000L;
    DefaultTransactionDefinition firstTransactionDefinition = new DefaultTransactionDefinition();
    DefaultTransactionDefinition secondTransactionDefinition = new DefaultTransactionDefinition();
    firstTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    secondTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);

    TransactionStatus firstTransactionStatus = txManager.getTransaction(firstTransactionDefinition);
    balanceService.change(balanceId, BigDecimal.ONE);
    txManager.commit(firstTransactionStatus);

    TransactionStatus secondTransactionStatus = txManager.getTransaction(secondTransactionDefinition);
    balanceService.change(balanceId, BigDecimal.ONE);
    txManager.commit(secondTransactionStatus);

    BalanceDto balanceDto = balanceService.find(balanceId);
    assertEquals(new BigDecimal("2.00"), balanceDto.getBalanceAmount());
}