Here is the case,
A member has to redeem a token to access (unlock) a given item. The relevant database tables are:
Table 1
Table MEMBER_BALANCE: MEMBER_ID, TOKEN_BALANCE
Table 2
Table UNLOCKED_ITEM: MEMBER_ID, DATE_UNLOCKED, ITEM_ID
Checks, or constraints, that I need to enforce are
My gut inclination is to write a simple method in MemberService.java:
@Transactional
public void unlockItem(Member member, Item item){
memberBalanceDAO.decrementBalance(member);
itemDAO.unlockItem(member, item);
}
I've dealt with the second requirement by adding a unique
constraint on MEMBER_ID
/ ITEM_ID
pair on the UNLOCKED_ITEM
table.
I think, the only thing I need to take care of would be, users trying to unlock many items at the same time, with TOKEN_BALANCE
requirement not met. For example, TOKEN_BALANCE
is 1, but the user clicks to unlock two items, virtually, at the same time.
Below is my MemberBalanceDAO.decrementBalance
method:
@Transactional
public void decrementBalance(Member member) {
MemberBalance memberBalance = this.findMemberBalance(member);
if (memberBalance.getTokens() >= 1) {
memberBalance.setTokens(memberBalance.getTokens() - 1);
this.save(memberBalance);
} else {
throw new SomeCustomRTException("No balance");
}
}
I don't think this protects me from the TOKEN_BALANCE
= 1 usecase. I'm worried about with multiple unlock requests at the same time. If the balance is 1, I could get two calls to decrementBalance()
at the same time both committing the balance to 0, but then also two successful calls to itemDAO.unlockItem(...)
as well, right?
How should I implement this? Should I set the service level method's transaction to isolation = Isolation.SERIALIZABLE
? Or is there a cleaner/better way to approach this?
I would rather suggest you to introduce version
column in member_balance
table. Refer to the docs, Optimistic Locking.
As you mentioned that you couldn't modify the schema; you can go with versionless optimistic locks, explained here.
Or you might like to go for pessimistic locking, explained here. Then, you can modify your method, decrementBalance()
, to fetch the member balance there, don't use findMemberBalance()
. For example,
@Transactional
public void decrementBalance(Member member) {
MemberBalance memberBalance = entityManager.find(
MemberBalance.class, member.id, LockModeType.PESSIMISTIC_WRITE,
Collections.singletonMap( "javax.persistence.lock.timeout", 200 ) //If not supported, the Hibernate dialect ignores this query hint.
);
if (memberBalance.getTokens() >= 1) {
memberBalance.setTokens(memberBalance.getTokens() - 1);
this.save(memberBalance);
} else {
throw new SomeCustomRTException("No balance");
}
}
NB: It might not work as it is; it's just to provide you some hints.