Spring4 @Scheduled @Transaction throws no transaction is in progress at flush for mutliple dataSources

Question

My service (jobExecutor which use main dataSource) works fine when called from spring MVC controller, however all the time throws "TransactionRequiredException: no transaction is in progress" when called from scheduled method. The reason looks the jdbcTransaction bound onto the thread from scheduledThreadPool has NOT_ACTIVE as localStatus. The transaction is for main dataSource and begin by default DataSourceTransactionManager.

I'm using spring-boot, spring-data and hibernate and below are those versions

spring-boot: 1.2.7.RELEASE

hibernate-core: 4.3.11.Final

hibernate-entitymanager: 4.3.11.Final

Also using java configuration

ServerConfig.java

@Configuration
@EnableAutoConfiguration
@EnableScheduling
@EnableAsync
@EnableAspectJAutoProxy
@ComponentScan("com.my.client")
@EnableTransactionManagement
@EntityScan(basePackages = {"com.my.database.model"})
@EnableJpaRepositories(
    transactionManagerRef = "transactionManager",
    basePackages = {"com.my.database.repository"})
public class ServerConfig extends SpringBootServletInitializer implements SchedulingConfigurer, AsyncConfigurer {

    static Logger log = Logger.getLogger(ServerModeConfig.class.getName());

    @Override
    public void configureTasks(ScheduledTaskRegistrar taskRegistrar) {
        taskRegistrar.setScheduler(taskExecutor());
    }

    /**
     * get executor for scheduling job
     * @return scheduled executor
     */
    @Bean(destroyMethod="shutdown")
    public Executor taskExecutor() {
        return Executors.newScheduledThreadPool(20);
    }

    /**
     * get executor for async job
     * @return executor for asynchronous job but no time limit
     */
    @Override
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(20);
        executor.setMaxPoolSize(100);
        executor.setQueueCapacity(200);
        executor.initialize();
        return executor;
    }

    @Override
    public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
        return new MyClientAsyncExceptionHandler();
    }

}

MyScheduler.java

@Component
public class MyScheduler {

    @Autowired
    protected JobExecutor jobExecutor;

    @Override
    @Scheduled(cron = "0 */1 * * * *") // every minute
    public void run() {
        log.info("Trigger job");
        jobExecutor.execute();
    }
}

Service layer

JobExecutorImpl.java

@Service("jobExecutor")
@Transactional("transactionManager")
public class JobExecutorImpl implements JobExecutor {

    static Logger log = Logger.getLogger(JobExecutorImpl.class.getName());

    @Override
    public ClientJobBehaviour execute() {
        log.info("transaction exists? ".concat( String.valueOf(TransactionSynchronizationManager.isActualTransactionActive()) )); // true
        log.info("transaction sync? ".concat( String.valueOf(TransactionSynchronizationManager.isSynchronizationActive()) ));  // true

        ClientJobBehaviour job = new ClientJobBehaviour();
        JobInstance jobInstance = new JobInstance();
        jobInstance.setStatus(JobStatus.STARTED.toString());
        jobInstance = jobInstanceRepository.save(jobInstance);
        jobInstanceRepository.flush();  // throws TransactionRequiredException
        job.setInstanceId(jobInstance.getId());
        return job;
    }
}

Spring Data JPA repository for internal dataSource

JobInstanceRepository.java

@Repository
public interface JobInstanceRepository extends JpaRepository<JobInstance, Long>{

}

Configuration for external dataSource. This use JpaTransactionManager and named adapterTransactionManager. Repository for external dataSource looks work ok

ExternalRepositoryConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "adapterEntityManagerFactory",
        transactionManagerRef = "adapterTransactionManager",
        basePackages = {"com.my.adapter.database.repository"})
public class ExternalRepositoryConfig {
    @Autowired
    JpaVendorAdapter jpaVendorAdapter;

    @Value("${adapter.datasource.url}")
    private String databaseUrl;

    @Value("${adapter.datasource.username}")
    private String username;

    @Value("${adapter.datasource.password}")
    private String password;

    @Value("${adapter.datasource.hibernate.dialect}")
    private String dialect;

    public DataSource dataSource() {
        return new DriverManagerDataSource(databaseUrl, username, password);
    }

    @Bean(name = "adapterEntityManager")
    public EntityManager entityManager() {
        return entityManagerFactory().createEntityManager();
    }

    @Bean(name = "adapterEntityManagerFactory")
    public EntityManagerFactory entityManagerFactory() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", dialect);

        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource());
        emf.setJpaVendorAdapter(jpaVendorAdapter);
        emf.setPackagesToScan("com.my.client.another.database.model");
        emf.setPersistenceUnitName("adapterPersistenceUnit");
        emf.setJpaProperties(properties);
        emf.afterPropertiesSet();
        return emf.getObject();
    }

    @Bean(name = "adapterTransactionManager")
    public PlatformTransactionManager transactionManager() {
        return new JpaTransactionManager(entityManagerFactory());
    }
}

Below is stackTrace when the service called from the scheduled method

    org.springframework.dao.InvalidDataAccessApiUsageException: no transaction is in progress; nested exception is javax.persistence.TransactionRequiredException: no transaction is in progress
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:410)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:223)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy95.flush(Unknown Source)
    at com.textura.client.job.executor.JobExecutorHelperImpl.preProcess(JobExecutorHelperImpl.java:62)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy99.preProcess(Unknown Source)
    at com.textura.client.job.executor.JobExecutorImpl.execute(JobExecutorImpl.java:30)
    at com.textura.client.scheduler.ExportInvoicesScheduler.lambda$0(ExportInvoicesScheduler.java:49)
    at com.textura.client.scheduler.ExportInvoicesScheduler$$Lambda$76/1738859546.accept(Unknown Source)
    at java.util.Optional.ifPresent(Optional.java:159)
    at com.textura.client.scheduler.ExportInvoicesScheduler.run(ExportInvoicesScheduler.java:43)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: javax.persistence.TransactionRequiredException: no transaction is in progress
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.checkTransactionNeeded(AbstractEntityManagerImpl.java:1171)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:1332)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
    at com.sun.proxy.$Proxy86.flush(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:291)
    at com.sun.proxy.$Proxy86.flush(Unknown Source)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.flush(SimpleJpaRepository.java:480)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:436)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:421)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:506)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 40 common frames omitted

When I debug, I can see localStatus of the jdbcTransaction is NOT_ACTIVE and below method from the hibernate throw the exception

AbstractEntityManagerImpl.java

private void checkTransactionNeeded() {
    if ( !isTransactionInProgress() ) {
        throw new TransactionRequiredException(
                "no transaction is in progress"
        );
    }
}

We have two dataSources, one for internal use and the other for external use

application.properties

# database configuration
spring.datasource.url=jdbc:h2:file:~/internal-db;AUTO_SERVER=TRUE;LOCK_TIMEOUT=10000
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.schema=schema.sql
spring.datasource.data=data.sql
spring.datasource.initialize=false
#spring.datasource.initialize=true only for first time to create table, after that switch to false

# JPA. Hibernate
spring.jpa.database=H2
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=validate
#spring.jpa.hibernate.ddl-auto=choose one of [create-drop, create, update, validate, none]
spring.jpa.hibernate.naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.show-sql=false
spring.data.jpa.repositories.enabled=true

adapter.datasource.url=jdbc:sqlserver://some.host.com:1433;databaseName=MyDBname
adapter.datasource.username=sa
adapter.datasource.password=
adapter.datasource.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

Lodi

Do you have specific hibernate configuration in your application.properties? Or maybe a rogue hibernate.properties or hibernate.cfg.xml? I would also suggest to use the framework as you are now doing a lot of things Spring Boot already does for you. Your stack trace shows a transactional interceptor and also some helper classes. What really worries me is the fact that you have something called a ApplicationContextProvider which I would argue is an anti-pattern and you should use dependency injection instead. – Delegate

I updated with my application.properties. For the ApplicationContextProvider, we had a special case to pass context to other project. But that doesn't cause the issue, so I remove that to avoid any confusion. – Lodi

Actually I'm suspecting that java.util.concurrent.Executors is preventing @ Transactional from beginning transaction, and hope to hear solution if that is the case. – Lodi

No it isn't because the transactional interceptor is there multiple times. Hence I suspect there is something gin your code or configuration messing up proper tx integration. – Delegate

I didn't mention but found this is happening only when I use multiple dataSources. I use default dataSource for internal audit purpose and setup another dataSource for syncing – Lodi

If you have multiple transaction managed, this is actually crucial to your issue!, you have to specify in the @Transactional method which transaction managed to use else there will be no transaction or commit for that specific resource. So instead of @Transactional you ned @Transactional("adapterDataSource"). This also applies for every other part of the system that needs to use this transaction manager. – Delegate

actually each @Transactional, I'm specifying which transaction manager to use by @Transactional("transactionManager") for the service related one source and @Transactional("adapterTransactionManager") for the service related to the adapter dataSource. and I checked from log too – Lodi

But what you have in your @Transactional and for the spring-data configuration is conflicting... They use different transactions. – Delegate

I see default transactionManager for default dataSource ( spring.datasource property prefix) is setup by @EnableAutoConfiguration and @EnableTransactionManagement, for adapter dataSource and transaction management, I'm setting up manually like above. I checked data read and write working to each dataSource when trigger job from UI ( those dataSources have different tables ), however that is not happening when trigger by scheduler – Lodi

Answer 1

I have resolved this issue by changing transactionManager configuration for the internal dataSource. It looks default transactionManager configured by @EnableTransactionManagement is DataSourceTransactionManager and somehow begin() method on hibernate AbstractTransactionImpl isn't called, if job comes from any scheduler. So I change the DataSourceTransactionManager for internal dataSource to JpaTransactionManager like below. now all transactions for both dataSource go successfully whether the job comes from scheduler or UI. So I think my issue addressed on this post has been fixed

@Configuration
@EnableAutoConfiguration
@EnableScheduling
@EnableAsync
@EnableAspectJAutoProxy
@ComponentScan("com.my.client")
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "entityManagerFactory",
    transactionManagerRef = "transactionManager",
    basePackages = {"com.my.database.repository"})
public class ServerConfig extends SpringBootServletInitializer implements SchedulingConfigurer, AsyncConfigurer {

    static Logger log = Logger.getLogger(ServerModeConfig.class.getName());

    @Autowired
    JpaVendorAdapter jpaVendorAdapter;

    @Autowired
    DataSource dataSource;

    @Bean(name = "entityManager")
    public EntityManager entityManager() {
        return entityManagerFactory().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactory")
    EntityManagerFactory entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource);
        emf.setJpaVendorAdapter(jpaVendorAdapter);
        emf.setPackagesToScan("com.my.client.database.model");
        emf.setPersistenceUnitName("default");
        emf.afterPropertiesSet();
        return emf.getObject();
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(entityManagerFactory());
        return tm;
    }

    @Override
    public void configureTasks(ScheduledTaskRegistrar taskRegistrar) {
        taskRegistrar.setScheduler(taskExecutor());
    }

    /**
     * get executor for scheduling job
     * @return scheduled executor
     */
    @Bean(destroyMethod="shutdown")
    public Executor taskExecutor() {
        return Executors.newScheduledThreadPool(20);
    }

    /**
     * get executor for async job
     * @return executor for asynchronous job but no time limit
     */
    @Override
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(20);
        executor.setMaxPoolSize(100);
        executor.setQueueCapacity(200);
        executor.initialize();
        return executor;
    }

    @Override
    public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
        return new MyClientAsyncExceptionHandler();
    }

}

Lodi

Answer 2

I had same probem with a @Transactional @Scheduled method. Making method public solved the problem. I don't no why!

Duenas

It looks like the problem there is because of how Spring proxies work - baeldung.com/…. Only public methods in a proxied instance can make use of the @Transactional annotation. – Ferrara

Answer 3

Just adding @Transactional to the method or class did not work for me, i had to add the bean name of the transaction manager using @Transactional(value="entityTransactionManager") after this it worked fine.

Carlita

Answer 4

I suggest to try make your, repo transactional, or disable your transactional properties from your project and try it out. When you try to access non transactional context from transactional context, this could happen.

@Repository
@Transactional
public interface JobInstanceRepository extends JpaRepository<JobInstance, Long>{

}

Gravamen

I got no difference from adding @Transactional to repository – Lodi

Answer 5

Steve, there is a reported by Spring that might be related? https://jira.spring.io/browse/SPR-5082.

Try to remove @Service annotation from JobExecutorImpl and add it to your ServerConfig class with @Bean.

Also, I would remove @EnableAspectJAutoProxy temporary to see if there is any conflict with aspect scanning. At least one thing not to worry about while finding the root issue.

Sycophant

I followed what you suggested (using @Bean and removing @EnableAspectJAutoProxy), but unfortunately don't see any difference – Lodi