How to handle Concurrent create if not exists with spring data JPA

user_11259999 0
Viewed 785

I am using spring data JPA in my java-springboot project. I have one method which processes a lot of inter related entities(some of which are lazy loaded) and this method is run by concurrently by multiple threads. My issue is - when say Thread T1 comes and tries to create a new Book for the author, it checks if the book info already exists in the DB or not and starts creating a new record, in the meanwhile thread T2 also tries the same and starts creating the same resource, however when persisting, it gets DataIntegrtyViolation exception.

@Entity
@Table(name = "BookInfo", uniqueConstraints = @UniqueConstraint(columnNames = { 
"publisherName",
    "bookTitle", "authorName" }), indexes = {
            @Index(name = "BookInfoProviderIndex", columnList = "publisherName") 
})
public class BookInfo  {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY/* , generator="table_gen" */)
  private Long id;

  @Column(name = "publisherName", nullable = false)
  private String publisherName;

  @Column(name = "bookTitle")
  private String bookTitle;

  @Column(name = "authorName")
  private String authorName;

the repository -

@Repository
public interface BookInfoRepository extends PagingAndSortingRepository<BookInfo, Long> {

public BookInfo findFirstByPublisherNameAndBookTitleAndAuthorName(String publisherName,
        String bookTitle, String authorName);

}

and the method which is causing the issue -

@Service
public class AuthorService{
  @Autowired
  BookInfoRepository bookRepo;

  @Transactional
  public createAutoBiography(AuthorDTO dto){
      BookInfo book = null;
      book = bookRepo.findFirstByPublisherNameAndBookTitleAndAuthorName(dto.getPubName(), dto.getTitle(), dto.author());
      if(book != null){
         book = buildBookInfo(dto);
         book = bookRepo.save(book);
      }

      ...//other methods to create dependent resources for Author class and save the entities using the respective repositories 
  }
}

I tried the following -

  1. Added @Lock(PESSIMISTIC_WRITE) on the findFirstByPublisherNameAndBookTitleAndAuthorName() repo method - this works, but collisions will not be very frequent and I am concerned about the performance impact it will have on the overall functionality, to handle occasional collisions like mentioned above.
  2. made the book creation part as a separate method with @Transactional(isolation = Isolation.SERIALIZABLE, propagation = Propagation.REQUIRES_NEW) - but I am getting no session error
  3. Made the BookInfo repo implement JpaRepository instead of PagingAndSortingRepository to use saveAndFlush() instead of save(), but it looks like even saveAndFlush will not commit to DB, the commit will implicitly happen only at the end of the outer method's Transaction

Also, noticed randomly there is a clash in resource creation for Author object itself, due to concurreny, where instead of adding the newly encountered BookInfo to an existing author, it creates a new entry for the author with the new book info

In general what is the best way to make JPA transactions thread safe?

spring-data-jpa spring-boot concurrency jpa java
2 Answers

Found the solution for this using the @Transactional(propagation = Propagation.REQUIRES_NEW) (you need the @Transactional provided by spring framework and not the javax.transaction.Transactional). The reason it wasn't working for me is due to a very non intuitive caveat of how spring data JPA manages the transaction proxy. Consider this -

@Service
public class AuthorService {

@Transactional
public void createAutoBiography() {
    createBook();
    // process other entities
}

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void createBook() {
    // ...
}

}

here you would be expecting two transactions to be created, one by createAutoBiography() which is suspended by another transaction created in the createBook() method - and this is where my assumption was wrong.

Spring creates a transactional AuthorService proxy, but once we are inside the AuthorService class and call other inner methods, there is no more proxy involved. This means, no new transaction is created. This meant any exception in the second method was closing the transaction for the entire service and I was getting No session error when the next jpa operation was attempted.

So solved this by putting the createBook() method in a separate service class and adding a class level @Transactional(propagation = Propagation.REQUIRES_NEW) annotation.

Now the createBook() execution takes place in a separate transaction and it is written to DB as soon as this method returns(transaction concludes) while the original transaction in createAutoBiography() is suspended. So, now if we catch the exception(as mentioned by @slauth above ) or use some kind of database locking we can handle/avoid the DataInegrityViolation Exception respectively. Also, even if the exception is not handled, only the second transaction should roll back, while the main transaction can still execute(need to test this though) .

reference - https://www.marcobehler.com/guides/spring-transaction-management-transactional-in-depth#transactional-pitfalls

user_112599990

You could simply catch the DataIntegrityViolation and call findFirstByPublisherNameAndBookTitleAndAuthorName (or createAutoBiography recursively) again.

user_67560900
Related