I have a Spring Boot application deployed on two instances, using MySQL as the database. When I send two simultaneous requests with the same message, the following issue occurs:

1. Instance 1 tries to retrieve the corresponding item from the database :

   Item item = itemRepository.findByMessage(message);
   if (item != null) { 
     item.setDate(date);
   } else {
      Item newItem = new Item();
      newItem.setMessage(message); // message is the unique identifier
      newItem.setDate(date);
      try {
        itemRepository.save(newItem);
      } catch (DataIntegrityViolationException e) {
          if (isDuplicateEntryException(e)) {
          itemUpdateService.updateItemIfFailure(message, date);
          }
      }
     }
   

2. Instance 2 executes the same request simultaneously. If it has not yet seen the item inserted by Instance 1, it also tries to insert it, leading to a unique key constraint violation (Duplicate Entry exception).

3. To handle this case, I implemented an update operation in a separate transactional method with @Transactional(propagation = Propagation.REQUIRES_NEW) :

   @Transactional(propagation = Propagation.REQUIRES_NEW)
   public long updateItemIfFailure(Date date, String message) {
     Item item = itemRepository.findByMessage(message);
     item.setDate(date);
     itemRepository.save(item);
   }
   

However, this results in a lock wait timeout error:

Lock wait timeout exceeded; try restarting transaction

Question

How can I avoid this concurrency issue between the two instances and handle either inserting or updating the item properly without running into unique key violations or lock wait timeouts?

Thanks for your help!