How can I continue a transaction in Spring Boot with PostgreSQL after an Exception occured?

Question

I created a service method that creates user accounts. If creation fails because the given e-mail-address is already in our database, I want to send the user an e-mail saying they are already registered:

@Transactional(noRollbackFor=DuplicateEmailException.class)
void registerUser(User user) {
   try {
      userRepository.create(user);
   catch(DuplicateEmailException e) {
      User registeredUser = userRepository.findByEmail(user.getEmail());
      mailService.sendAlreadyRegisteredEmail(registeredUser);
   }
}

This does not work. Although I marked the DuplicateEmailExcepetion as "no rollback", the second SQL query (findByEmail) still fails because the transaction was aborted.

What am I doing wrong?

There is no @Transactional annotation on the repository.

Answer 1

That's not a problem with Spring / JDBC or your code, the problem is with the underlying database. For example, when you are using the Postgres if any statement fails in a transaction all the subsequent statements will fail with current transaction is aborted.

For example executing the following statements on your Postgres:

> start a transaction
> DROP SEQUENCE BLA_BLA_BLA;
> Error while executing the query; ERROR: sequence "BLA_BLA_BLA" does not exist"
> SELECT * FROM USERS;
> ERROR: current transaction is aborted, commands ignored until end of transaction block

Still the SELECT and subsequent statements are expected to succeed against MySQL, Oracle and SQL Server