-
-
Save rayden-alex/6ad7eccdabc8555a9925aaadabff6114 to your computer and use it in GitHub Desktop.
JPA and Hibernate: Simple and Smart way of using PostgreSQL Advisory Locks with JPQL to prevent Lost Update anomaly
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package br.com.stackspot.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.stackspot.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); | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package br.com.stackspot.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 AccountNotFoundOrLockNotAcquiredException} | |
| */ | |
| 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); | |
| } | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment