Potential concurrency issue while checking and updating a record in table

Question

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

1. TOKEN_BALANCE must be > 0 when the user tries to unlock an item, and
2. The user hasn't unlocked the same item before.

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?

Answer 1

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.