java mysql spring spring-boot transactions

Concurrent requests transaction to prevent unwanted persistence

I am trying to get my head around how to approach what initially seems a “simple” problem.

I have UserAccounts that can have MANY Purcahses BUT business logic dictates can only have one Purchase in a PurchaseState.IDLE state (a field on the entity). A purchase is IDLE when first created.

I have a repo with a method to determine if the user has a purchase with the given states already existing:

boolean existsByPurchaseStateInAndUserAccount_Id(List<PurchaseState> purchaseState, long userAccountId);

I noticed with a bit of testing and thinking I can create more than one purchase when two requests are passed in close proximity/at the same time (i.e. a concurrency issue and/or race condition).

This leads to the user account having two purchases with both having an IDLE state.

I have drawn up a quick diagram to show what I think is happening:

Now, is there a way using @Transactional that would cause the second persistence/transaction to rollback?
I am unsure if simply wrapping the service method in @Transcational(isolation=REPEATED_READ)
would relieve the issue? I.e. is there a way SQL will handle this transactionally?

I can only guess this wouldn’t actually help as the existsBy is not tracked by the SQL transaction and therefore wont rollback?

Is the only real solution to run a second countBy query at the end of the method to rollback the transaction if there is >1 entity fitting the condition? I still don’t feel this is “perfect” and fully solve the race condition/TX issue…


So the service will see there are 2 entities being committed across the two transactions (not yet committed) but for T2 the service can throw a RuntimeException to trigger the rollback?

Sorry, I have been reading bits about Transaction isolation but it seems to only be applicable to say if I am checking a field value/column of an entity rather than using logic based on say the return of a “count(*)” query…

Thank you for any enlightenment.