Last active
February 7, 2024 02:36
-
-
Save rponte/1a31395f58de1cd189daae0a358cec20 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 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 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
The previous code acquired a lock with the
pg_try_advisory_xact_lock()
function using the account ID as a key (just a number), which is brittle since this ID number could be anything. Ideally, we should give some context for the key, something like "account:42
" or "product:1234
" for example.Since the
pg_try_advisory_xact_lock()
function does not support text as argument, we must generate aint/bigint
hash for any text informed as key, like "account:42
". We could generate a hash for text using the Postgres built-in functionpg_catalog.hashtextextended(text, seed)
as below:Here are other examples of using this function:
And the query result on Postgres v14.5:
hash_key_with_random_seed
may not be useful for this scenario, since it changes the key value every time it runs 😉