3

I'm using Spring Boot and JPA (with Hibernate).

I have a method (into my service) to:

  • Check if already there into the DB a specific entity (for example I need to check if already have an user. For this I using a custom JPQL query into my JPA repository.
  • After this, in case there isn't any result for the previous step, I will create the entry into the DB (using the the save JPA repository method) At the same time I need to create an other entity also (for example some info related to the user stored into a separated table). So, in this case I will have two create-query (one for User table and one for Info-User table).
  • In case already have some results for the step 1, I need to skip the insert step obviously.

Obviously, I need to guarantee the atomicity for this method.

I must to avoid separate transactions that risk creating duplicate entries in the DB.

An additional note: on the Users table I cant to add some unique constraints because there are some different combinations for this (for example, I can create again the same user if the status is in a certain value or something like this).

I tried to use only the @Transactional annotation for my method but I noticed this is not enough (using some stress test I'm able to create multiple rows into the DB).

So now I'm confused.

What's the best practice to have an atomic method? Have I to change the Transaction Isolation level? Have I to use some Locks?

I'm not a Spring expert but I think that this is a common problem but I'm not able to understand what is the correct way.

Basically I'm looking for an atomic GET_OR_CREATE method using the JPA (and JPA repository)

Safari
  • 11,437
  • 24
  • 91
  • 191

1 Answers1

3

In that case you will need to set the Transaction isolation level to SERIALIZABLE that only one transaction can access the database. This can be done using the isolation attribute of the Transactional annotation:

@Transactional(isolation = Isolation.SERIALIZABLE)
public void yourMethod(){
    // ...
}

Read more about that topic here: https://www.baeldung.com/spring-transactional-propagation-isolation

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • thanks for your suggestion. Into the article I read: "It prevents all mentioned concurrency side effects but can lead to the lowest concurrent access rate because it executes concurrent calls sequentially." So, probably I will go in against with this performance issue.. Is it normal for my scenario? From your point of view, Is it the only one solution? Thanks – Safari Jul 20 '20 at 09:49
  • 1
    Concurrency and data integrity is always a tradeoff you can't have both. Maybe you have to check your requirements. And duplicate entries can be prevented using a unique constraint. – Simon Martinelli Jul 20 '20 at 10:44
  • @SimonMartinelli that means that if I have an unique key, I can use the default transaction isolation level? – Daniel Pop Sep 15 '21 at 08:11
  • @SimonMartinelli Are you sure that the isolation level will guarantee atomicity? In my understanding two reads could occur at the same time. Both will see that there is no user entry yet. Then the second write could occur after the first transaction finished. I read that some DBMS use snapshots. I.e. the second transaction could even repeat the read again with the same empty result, although the first already wrote an entry, because it is read from the snapshot. But that doesn't mean it couldn't perform an INSERT after that. – fishbone Mar 02 '22 at 09:27
  • "Atomicity" is the wrong term in my comment above. It is indeed atomic, nevertheless the problem of the author remains as stated in my comment. There still could be two INSERTs (if there is no unique key) where only one is expected. – fishbone Mar 02 '22 at 10:02