Spring boot transaction rollback doesn't fire on PostgreSQL database because of idle transaction

Question

I try to execute a transactional operation and intentionally throw an exception in order to verify if a rollback is done but the rollback isn't being executed.

The PostgreSQL database version is 12.1-1 and is Docker-based.

Here is the service that contains the @Transactional annotation:

@Service
public class MyTestService {
    @Autowired
    private DocumentDataDao documentDataDao;

    @Transactional
    public void test() {
        DocumentData data = new DocumentData();
        data.setData(UUID.randomUUID().toString());
        documentDataDao.create(data);
        throw new IllegalArgumentException("Test rollback");
    }
}

The create function is using a NamedParameterJdbcTemplate to insert the data:

String statement = String.format("INSERT INTO %s (%s) VALUES (%s) RETURNING %s", tableName,
                String.join(",", insertingColumns), String.join(",", values),
                String.join(",", returningColumns));
return getNamedJdbcTemplate().queryForObject(statement, parameters, getRowMapper());

And the test function is called from another service:

@Service
public class ApplicationStartupListener {
    private Logger log = LoggerFactory.getLogger(ApplicationStartupListener.class);

    @Autowired
    private MyTestService testService;

    @PostConstruct
    public void init() {
        try {
            testService.test();
        } catch (Exception e) {
            log.error("fail to start", e);
        }
    }
}

When debugging I found out that if the rollback isn't executed it's because of the transaction being IDLE.

Here is the rollback function from PgConnection and executeTransactionCommand isn't being executed:

public void rollback() throws SQLException {
    checkClosed();

    if (autoCommit) {
      throw new PSQLException(GT.tr("Cannot rollback when autoCommit is enabled."),
          PSQLState.NO_ACTIVE_SQL_TRANSACTION);
    }

    if (queryExecutor.getTransactionState() != TransactionState.IDLE) {
      executeTransactionCommand(rollbackQuery);
    }
  }

Any hint on why the transaction is being marked as idle and stops the rollback method to be executed would be appreciated.

Edit (1)

As @M. Deinum mentioned, there is no guarantee that a transactional proxy has been created when using @PostConstruct. That's why I tested with an ApplicationRunner:

@Component
public class AppStartupRunner implements ApplicationRunner {
    @Autowired
    private MyTestService testService;

    @Override
    public void run(ApplicationArguments args) throws Exception {
           testService.test();
    }
}

But this didn't work either.

I also tried to run the test method a few moments after the application has been started by using a RestController and sending an HTTP request to it but still, the same issue.

@RestController
public class AppController {

    @Autowired
    private MyTestService testService;

    @GetMapping("/test")
    public ResponseEntity<Object> test() {
        testService.test();
        return ResponseEntity.ok().build();
    }
}

Edit (2)

I upgraded the PostgreSQL JDBC version from 42.2.2 to 42.2.18 (latest as of now) but the connection is still IDLE when trying to rollback.

Edit (3)

I reproduced the issue in a git repository: https://github.com/Martin-Hogge/spring-boot-postgresql-transactional-example/tree/master.

Answer 1

I examined the architecture that you want to use multiple schemas (data sources, jdbc templates) in a single application. @Transactional only manages application's default data source that is named HikariPool-1. When you call the rest method, new hikari pool will be created that is named HikariPool-2. Your operations are on HikariPool-2, but @Transactional manages only HikariPool-1.

@Transactional's transaction manager argument can not be changed dynamically. So, you can define a new custom annotation that manages your transactions. Or you can use TransactionTemplate instead of annotations.

I created a simple custom transaction management aspect. It is working with defined dao's data source and transaction lifecycle.

Test Service

@Service
public class MyTestService {
    @Autowired
    private DocumentDataDao documentDataDao;

    @CustomTransactional(DocumentDataDao.class)
    public void test() {
        DocumentData data = new DocumentData();
        data.setData(UUID.randomUUID().toString());
        documentDataDao.create(data);
        throw new IllegalArgumentException("Test rollback");
    }
}

Custom Transactional

package com.example.transactional;

import java.lang.annotation.*;

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface CustomTransactional {
    Class<? extends BaseDao<?>> value();
}

Custom Transactional Aspect

package com.example.transactional;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.Map;

@Aspect
@Component
public class CustomTransactionalAspect implements ApplicationContextAware {
    private ApplicationContext applicationContext;
    private Map<Class<? extends BaseDao<?>>, BaseDao<?>> classMap = new HashMap<>();

    @Around("@annotation(customTransactional)")
    public Object customTransaction(ProceedingJoinPoint joinPoint, CustomTransactional customTransactional) throws Throwable {
        BaseDao<?> baseDao = getBaseDao(customTransactional.value());

        // custom transaction management
        return baseDao.getConnectionHandler().getTransactionTemplate().execute(status -> {
            try {
                return joinPoint.proceed();
            } catch (Throwable throwable) {
                throw new RuntimeException(throwable);
            }
        });
    }

    /**
     * Search {@link BaseDao} class on spring beans
     *
     * @param clazz Target dao class type
     * @return Spring bean object
     */
    private BaseDao<?> getBaseDao(Class<? extends BaseDao<?>> clazz) {
        return classMap.computeIfAbsent(clazz, c -> applicationContext.getBean(c));
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }
}

Connection Handler

I added transactionTemplate for transaction operations

public class ConnectionHandler {
    private NamedParameterJdbcTemplate namedJdbcTemplate;
    private JdbcTemplate jdbcTemplate;
    private TransactionTemplate transactionTemplate;
    private String schema;

    public ConnectionHandler(DataSource dataSource, String schema) {
        this.namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.schema = schema;

        this.transactionTemplate = new TransactionTemplate(new DataSourceTransactionManager(dataSource));
    }

    public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
        return namedJdbcTemplate;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public String getSchema() {
        return schema;
    }

    public TransactionTemplate getTransactionTemplate() {
        return transactionTemplate;
    }
}

BaseDao

Change modifier of getConnectionHandler to public.

    public ConnectionHandler getConnectionHandler() {
        return getDataSource().getConnection(getSchemaName());
    }

pom.xml

You can remove postgresql.version, spring-jdbc.version and HikariCP.version. Problem is not related with versions. Add spring-boot-starter-aop dependency for aspect operations.

<dependencies>
...
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
</dependencies>