Insert after delete same transaction in Spring Data JPA

Question

Using Spring Data JPA I have the next flow inside the same transaction (REQUIRES_NEW) :

1. Remove a set of user's predictions with this Spring Data JPA repository method.

   @Query(value = "DELETE FROM TRespuestaUsuarioPrediccion resp WHERE resp.idEvento.id = :eventId AND resp.idUsuario.id = :userId")
   @Modifying
   void deleteUserPredictions(@Param("userId") int userId, @Param("eventId") int eventId);
   

2. Insert the new user's predictions and save the master object (event).

   eventRepository.save(event);
   

When this service finishes, the commit is made by AOP but only works in first attemp...not in the next ones...

How can I manage this situation without iterating over event's predictions entries and updating each one inside?

UPDATE

I tried with that and it doesn't work (the adapter inserts the objects I remove before):

@Transactional(propagation=Propagation.REQUIRES_NEW, rollbackFor=PlayTheGuruException.class)
private void updateUserPredictions(final TUsuario user, final TEvento event, final SubmitParticipationRequestDTO eventParticipationRequestDTO) 
{
    eventRepository.deleteUserPredictions(user.getId(), event.getId());
    EventAdapter.predictionParticipationDto2Model(user, event, eventParticipationRequestDTO);
    eventRepository.save(event);
}

Answer 1

Hibernate changed order of the commands. It works in below order : Execute all SQL and second-level cache updates, in a special order so that foreign-key constraints cannot be violated:

    1. Inserts, in the order they were performed
    2. Updates
    3. Deletion of collection elements
    4. Insertion of collection elements
    5. Deletes, in the order they were performed 

And that is exactly the case. When flushing, Hibernate executes all inserts before delete statements. The possible option are :
1. To call entityManager.flush() explicitly just after the delete. OR 2. Wherever possible update existing rows and from rest create ToBeDeleted List. This will ensure that existing records are updated with new values and completely new records are saved.

Answer 2

PostgreSQL (and maybe other databases as well) have the possibility to defer the constraint until the commit. Meaning that it accepts duplicates in the transaction, but enforces the unique constraint when committing.

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> UNIQUE(<column1>, <column2>, ...) DEFERRABLE INITIALLY DEFERRED;