I have Spring Boot application which works with PostgreSQL database.

I have only one datasource which is using for Spring Data JPA operations and for Liquibase migrations (I guess).

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://*************/*****
    username: *******
    password: *******
    type: com.zaxxer.hikari.HikariDataSource
    connection-test-query: SELECT 1;
    idle-timeout: 30000
    maximum-pool-size: 100
    minimum-idle: 7
  jpa:
    hibernate:
      ddl-auto: validate
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    show-sql: false
    properties:
      hibernate:
        default_schema: public
        format_sql: true
        enable_lazy_load_no_trans: true
        generate_statistics: false
        jdbc.batch_size: 100
        order_inserts: true
        order_updates: true
        jdbc.batch_versioned_data: true
        query.fail_on_pagination_over_collection_fetch: true
  liquibase:
    liquibase-schema: public
    default-schema: public
    change-log: classpath:db/changelog/db.changelog-master.xml

I also have Spring Boot configuration class that specifies which one transaction manager I wanna have in my application.

@Configuration
public class TransactionManagerConfig {

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(SessionFactory sessionFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(sessionFactory);
        return transactionManager;
    }
}

When it comes to using my database, I have to different ways to communicate with my data. First one is using service method annoteted with @Transactional annotation. For example for simple reading I use next approach:

@Transactional(readOnly = true, isolation = Isolation.READ_COMMITTED)
public List<Dto> fetchAll() {...

And another way is a little bit more complicated but still usual. For example for update I am interacting with transaction manually:

@Service
public class EntityService {
    @Autowired
    private DocumentRepository documentRepository;

    @Autowired
    private PlatformTransactionManager transactionManager;

    public Long update(Long id, Entity entity) {
        DefaultTransactionDefinition transForCursor = new DefaultTransactionDefinition();
        transForCursor.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
        transForCursor.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus cursorTransactionStatus = transactionManager.getTransaction(transForCursor);
        try {
            //...
            //here I am interactiong with S3 File storage
            //...
            savedEntity = entityRepository.save(entity);
        } catch (Exception e) {
            //...
            //if I catch any exception I should delete files from S3 File storage
            //which I just uploaded and only then I should rollback database transaction
            //...
            if (!(e instanceof RuntimeException)) {
                transactionManager.rollback(cursorTransactionStatus);
            }
            throw e;
        }
        try {
            transactionManager.commit(cursorTransactionStatus);
        } catch (Exception e) {
            //...
            //if any exception happens when I was trying to commit I still should delete files
            //...
            throw new TransactionException();
        }
        return savedEntity.getId();
    }
}

And the problem is that sometimes I get this exception when I am calling read or update method:

org.springframework.transaction.IllegalTransactionStateException: Pre-bound JDBC Connection found! JpaTransactionManager does not support running within DataSourceTransactionManager if told to manage the DataSource itself. It is recommended to use a single JpaTransactionManager for all transactions on a single DataSource, no matter whether JPA or JDBC access.

But sometimes it's just working fine without any errors. And the most incredibly interestion thing here that last time when I got this problem I had this exception every tenth call of my read method.

Does anyone have any idea why it can be happening and how to fix it? Thank you very much for your time and your answers!

I tried to fix it by adding @Transactional annotation to my read method. And I added @Primay annotation to my configuration class where I define transaction manager. But unfortunately it didn't help me. I was try to find the same problem here but I didn't meet the same conditions.

CodePudding user response：

That pattern:

TransactionStatus tx = transactionManager.getTransaction(...);
try {
    ...
} catch (Exception e) {
    if (!(e instanceof RuntimeException)) {
        transactionManager.rollback(tx);
    }
    throw e;
}
try {
    transactionManager.commit(tx);
} catch (Exception e) {
    ...
    throw new TransactionException();
}
return savedEntity.getId();

is definitely incorrect, the rule of thumb is: you must finish (either commit or rollback) transaction in the same place where you have started it. However in your case that is not true, so you are encountering resource leaks and other related consequences.

If you need to cleanup some external resources upon transaction rollback you have following options:

1. spring-tx way:

TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronization() {
    @Override
    public void afterCompletion(int status) {
        if (STATUS_ROLLED_BACK== status) {
            // do something
        }
    }
});

2. hibernate way:

SessionImplementor session = entityManager.unwrap(SessionImplementor.class);
session.addEventListeners(new BaseSessionEventListener() {

    @Override
    public void transactionCompletion(boolean success) {
        if (!success) {
            // do something
        }
    }

});

3. another hibernate way:

SessionImplementor session = entityManager.unwrap(SessionImplementor.class);
session.getActionQueue().registerProcess((success, sess) -> {
    if (!success) {
        // do something
    }
});