CODEWITHSUNDEEP
javamysqlspringspring-bootspring-transactions
sumitb.mdi
sumitb.mdi

Reputation: 990

Handling concurrent transaction in Spring-Boot

So, it might seems like a very noob question, but after some googling, I couldn't find out the best approach.

Problem : Have a Spring-Boot application, interacting with the DataBase (MySQL). Consider a table T1, where I wan't to have only a single valid entry for each user. So if next entry appears, it soft-deletes (by setting the deleted-at column) the existing rows for that user and creates a new entry. Now the problem comes when there is are multiple request, almost at the same time.

My Transactional block, looks something like this : 

@Transactional
someMethod() {
    CheckIfAnyValidEntryForUser(U1);    
    SoftDeleteExistingEntries(U1);
    DoComputation();   // Takes around a second.
    InsertNewRow();
}

Now if there are multiple request within short span of time, so my code ends up inserting multiple valid entries for a single user.

Since the row I want to edit might not exist before, so I can't do Optimistic Locking by version number.

Was thinking of acquiring a Global-Lock on this someMethod, but this increase my latency by too much.

Another approach might be to use a composite unique key in the database for columns (including the deleted-at column). And then handle the commit failure in the code.

This seems to be a very common problem, so how is this handled usually? What is the best approach?

Upvotes: 1

Views: 4463

Answers (1)

Abdelghani Roussi
Abdelghani Roussi

Reputation: 2817

You can use the isolation property of @Transactional annotation and set this property to Isolation.SERIALIZABLE :

@Transactional(isolation= Isolation.SERIALIZABLE)

Note that :

Spring by default use DEFAULT isolation strategy which uses the default isolation level of underlying datastore. Using SERIALIZABLE isolation makes your transactions executed with locking at read, write, range; so they appear as if they were executed in a serialized way, preventing dirty reads, non-repeatable reads and phantom reads.

Also

Exclusively designed for use with Propagation.REQUIRED or Propagation.REQUIRES_NEW since it only applies to newly started transactions. Consider switching the "validateExistingTransactions" flag to "true" on your transaction manager if you'd like isolation level declarations to get rejected when participating in an existing transaction with a different isolation level.

Upvotes: 1

Related Questions