JPA and Hibernate: Simple and Smart way of using PostgreSQL Advisory Locks with JPQL to prevent Lost Update anomaly

AccountRepository.java

package br.com.rponte.nullbank.withdrawal;

import org.hibernate.LockOptions;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;

import javax.persistence.LockModeType;
import javax.persistence.QueryHint;
import javax.transaction.Transactional;
import java.util.Optional;

@Repository
public interface AccountRepository extends JpaRepository<Account, Long> {

    /**
     * Loads the entity even when a lock is not acquired
     */ 
    @Transactional
    @Query(value = """
                   select new br.com.rponte.nullbank.withdrawal.LockableAccount(
                          c
                         ,pg_try_advisory_xact_lock(c.id)
                         )
                     from Account c
                    where c.id = :accountId
                      and pg_try_advisory_xact_lock(c.id) is not null
                   """
    )
    public Optional<LockableAccount> findByIdWithPessimisticAdvisoryLocking(Long accountId);

}

PessimisticLockingWithAdvisoryLockInQueryATMService.java

package br.com.rponte.nullbank.withdrawal;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PessimisticLockingWithAdvisoryLockInQueryATMService {

    @Autowired
    private AccountRepository repository;
    @Autowired
    private TransactionRepository transactionRepository;

    @Retryable(
            value = FailedToAcquireLockForAccountException.class,
            maxAttempts = 3,
            backoff = @Backoff(delay = 100, random = true, multiplier = 2.0)
    )
    @Transactional
    public void withdraw(Long accountId, double amount) {

        // We load the entity even if a lock is not acquired
        LockableAccount lockedAccount = repository.findByIdWithPessimisticAdvisoryLocking(accountId).orElseThrow(() -> {
            throw new IllegalStateException("account does not exist: " + accountId);
        });

        // But the business logic is executed only if the lock was acquired for the account
        Account account = lockedAccount
                .getAccountIfLockedOrElseThrow();

        double newBalance = (account.getBalance() - amount);
        if (newBalance < 0) {
            throw new IllegalStateException("there's not enough balance");
        }

        account.setBalance(newBalance);
        repository.save(account);

        transactionRepository
                .save(new Transaction(account, amount, "withdraw"));
    }

}

/**
 * Represents an account that may be locked or not
 */
class LockableAccount {
    private Account account;
    private boolean locked;

    public LockableAccount(Account account, boolean locked) {
        this.account = account;
        this.locked = locked;
    }

    /**
     * Returns the actual account if it was locked or else throws an {@code FailedToAcquireLockForAccountException}
     */
    public Account getAccountIfLockedOrElseThrow() {
        if (!locked) {
            throw new FailedToAcquireLockForAccountException("Account already locked by another user");
        }
        return account;
    }
    
    public boolean isLocked() {
        return locked;
    }
}

class FailedToAcquireLockForAccountException extends RuntimeException {

    public FailedToAcquireLockForAccountException(String message) {
        super(message);
    }
}

Discussion with Francky Pachot on Twitter about using the pg_try_advisory_xact_lock () function on both the WHERE and SELECT clauses and their possible corner cases.

Francky brought up an issue when using the function on the WHERE clause without an index (or when it's not used):

Ok. There's always a risk when the behavior depends on when the function is evaluated because an execution plan can change.
Maybe it is safer within a CASE statement.
Example:
https://dbfiddle.uk/QUP_t0T7
(that's for WHERE - not sure if the same can happen with SELECT)

Below, we can see the DB Fiddle:

According to Francky, maybe we should use a CASE statement to prevent this issue:

SELECT id, value, pg_try_advisory_xact_lock(id)
  FROM demo
 WHERE id=2 
   AND CASE WHEN id=2 THEN pg_try_advisory_xact_lock(id) END is not null ;