[FIXED] Calling @Transactional Method from original @Transactional method causing rollback issue in same class

Issue

I currently have a use case, where I where if my user manually inserts a data file to be read into the database I need to check if the data exists in the DB. If it does, I want to delete it and then process and save the new file. The problem with this is my methods are marked @Transactional so even though the delete methods are ran, they aren't committed before the save method is called which violates a unique constraint casuing the rollback.

I have tried every level of propagation and also tried splitting them up into two separate transactions where my controller calls them one by one and they don't call each other.

ERROR: org.springframework.transaction.UnexpectedRollbackException: Transaction silently rolled back because it has been marked as rollback-only

CODE:

 @Transactional
  public void saveAllPositionData(InputStream is) throws IOException {

    log.info("Parsing position data...");
    ParsingResult parsingResult = positionParser.parse(is);
    if (!parsingResult.getPositions().isEmpty()) {
      LocalDate businessDate = parsingResult.getPositions().get(0).getBusinessDate();
      overwriteData(businessDate);
    }

    try {
      positionRepo.saveAll(bpsParsingResult.getPositions()); // UNIQUE CONSTRAINT FAILS HERE CAUSING ROLLBACK
      priceRepo.saveAll(parsingResult.getPrices());

      for (PositionTable position : parsingResult.getPositions()) {
        if (position.getNumberOfMemos() > 0) memoRepo.saveAll(position.getCorrespondingMemos());
      }
    } catch (Exception e) {
      log.warn("Invalid data returned from BPS parsing job: {}", e.getMessage());
    }
  }

  @Transactional(propagation = Propagation.NESTED) // Tried Propagation.* and no Annotation
  public void overwriteData(LocalDate businessDate) {
    if (memoRepo.countByBusinessDate(businessDate) > 0) {
      log.warn(
              "Memo record(s) found by {} business date. Existing data will be overridden.",
              businessDate);
      memoRepo.deleteByBusinessDate(businessDate);
    }
    if (positionRepo.countByBusinessDate(businessDate) > 0) {
      log.warn(
          "Position record(s) found by {} business date. Existing data will be overridden.",
          businessDate);
      positionRepo.deleteByBusinessDate(businessDate);
    }
    if (priceRepo.countByBusinessDate(businessDate) > 0) {
      log.warn(
          "Price record(s) found by {} business date. Existing data will be overridden.",
          businessDate);
      priceRepo.deleteByBusinessDate(businessDate);
    }
  }

Solution

UnexpectedRollbackException usually happens when an inner @Transactional method throws exception but the outer @Transactional method catch this exception but not re-throw it.(See this for more details). Methods on the JpaRepository actually has @Transactional annotated on it. Now in saveAllPositionData() , some method calls on the JpaRepository throw exception but you catch it and not rethrow it so it causes UnexpectedRollbackException.

Also , @Transactional method does not work if you self calling it from the inner class. That means @Transactional on overwriteData() does not have effect in your codes. (See Method visibility and @Transactional section in docs for more detail)

The problem with this is my methods are marked @Transactional so even though the delete methods are ran, they aren't committed before the save method is called which violates a unique constraint casuing the rollback

You can try to call flush() on the JpaRepository after calling the delete method. It will apply all the pending SQL changes collected so far to the DB but will not commit the transaction. So only the transaction involved will see the records are deleted such that when you insert the data in the same transaction later , you should not encounter unique constraint violation .

Answered By - Ken Chan
Answer Checked By - Marie Seifert (JavaFixing Admin)