Potential concurrency issues when checking and updating records in a table


user1827569:

this is the case

Members must redeem tokens 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

The check or constraint I need to perform is

  1. TOKEN_BALANCE must be > 0 when the user tries to unlock the item, and
  2. The user has not unlocked the same item before.

My hunch 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 handle the second requirement by adding constraints uniqueon MEMBER_ID/ ITEM_IDpairs on the table UNLOCKED_ITEM.

I think, the only thing I need to take care of is that the user is trying to unlock many items at the same time TOKEN_BALANCEand the requirements are not met. For example, TOKEN_BALANCE1, but the user clicks to virtually unlock both items at the same time.

Here is my MemberBalanceDAO.decrementBalancemethod:

@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 will save me from the TOKEN_BALANCE=1 use case. I'm worried about having multiple unlock requests at the same time. If the balance is 1, I can get two calls at the same time, decrementBalance()both submit the balance as 0, and then both are called successfully itemDAO.unlockItem(...), right?

How should I implement this? Should I set transaction for service level method to isolation = Isolation.SERIALIZABLE? Or is there a cleaner/better way to solve this problem?

Adeel Ansari:

I recommend that you introduce the columns versionin the member_balancetable . See the documentation for optimistic locking .

As mentioned, you cannot modify the schema; you can use versionless optimistic locking, explained here .

Alternatively, you might want to do pessimistic locking, as described here . Then you can modify the method decrementBalance()to get the member balance here, please don't use findMemberBalance(). E.g,

@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");
    }
}

Note: it may not work as-is; just to give you some hints.

Related


Unit tests for potential concurrency issues

Andrewney: I recently had a small competition with a colleague of mine (whom I have a lot of respect for) to discuss a theoretical possibility to test whether some code is thread-safe. Let's assume we have a "black box" class FooUnknownfrom a "third party" lib

Unit tests for potential concurrency issues

Andrewney: I recently had a small competition with a colleague of mine (whom I have a lot of respect for) to discuss a theoretical possibility to test whether some code is thread-safe. Let's assume we have a "black box" class FooUnknownfrom a "third party" lib

Unit tests for potential concurrency issues

Andrewney: I recently ran a small competition with a colleague of mine (whom I have a lot of respect for) to discuss a theoretical possibility to test whether some code is thread-safe. Let's assume we have a "black box" class FooUnknownfrom a "third party" lib

Unit tests for potential concurrency issues

Andrewney: I recently ran a small competition with a colleague of mine (whom I have a lot of respect for) to discuss a theoretical possibility to test whether some code is thread-safe. Let's assume we have a "black box" class FooUnknownfrom a "third party" lib

VB.NET concurrency violation error when updating Access table

User 3432724 Just to clear things up, I created a simple example to reproduce the problem. In an MSaccess database, I have a table called "tblTest" with an auto-increment field "ID" and a string field called "Test". ID is the primary key. Now here is the code:

Potential issues when using ProfileOptimization across versions

Scott To improve the startup performance of the program at startup, I call: ProfileOptimization.SetProfileRoot(path); ProfileOptimization.StartProfile("profile"); I have some questions: Can I use the same ProfileOptimization profile across versions of the pro

Potential issues when using ProfileOptimization across versions

Scott To improve the startup performance of the program at startup, I call: ProfileOptimization.SetProfileRoot(path); ProfileOptimization.StartProfile("profile"); I have some questions: Can I use the same ProfileOptimization profile across versions of the pro

Concurrency issues when using ConcurrentHashMap

user4759317: I've been working on a REST API as part of some tricks. The current implementation has a small concurrency issue when inserting objects into the ConcurrentHashMap. My code checks to see if the consumed JSON contains an ID. If not, create a new uni

Concurrency issues when using ConcurrentHashMap

user4759317: I've been working on a REST API as part of some tricks. The current implementation has a small concurrency issue when inserting objects into the ConcurrentHashMap. My code checks to see if the consumed JSON contains an ID. If not, create a new uni

Concurrency issues when dealing with webhooks

Sean Hudson Our application creates/updates database entries based on webhooks of external services. The webhook sends the external ID of the object so we can get more data to process. Webhook processing with round trips to get more data is 400-1200ms. Sometim

Concurrency issues when dealing with webhooks

Sean Hudson Our application creates/updates database entries based on webhooks of external services. The webhook sends the external ID of the object so we can get more data to process. Webhook processing with round trips to get more data is 400-1200ms. Sometim

Concurrency issues when using ConcurrentHashMap

user4759317: I've been working on a REST API as part of some tricks. The current implementation has a small concurrency issue when inserting objects into the ConcurrentHashMap. My code checks to see if the consumed JSON contains an ID. If not, create a new uni

Concurrency issues when dealing with webhooks

Sean Hudson Our application creates/updates database entries based on webhooks of external services. The webhook sends the external ID of the object so we can get more data to process. Webhook processing with round trips to get more data is 400-1200ms. Sometim

Concurrency issues when dealing with webhooks

Sean Hudson Our application creates/updates database entries based on webhooks of external services. The webhook sends the external ID of the object so we can get more data to process. Webhook processing with round trips to get more data is 400-1200ms. Sometim

Concurrency issues when dealing with webhooks

Sean Hudson Our application creates/updates database entries based on webhooks of external services. The webhook sends the external ID of the object so we can get more data to process. Webhook processing with round trips to get more data is 400-1200ms. Sometim