How to manually start a transaction on a shared EntityManager in Spring?

Question

I have a LocalContainerEntityManagerFactoryBean as EntityManager instance.

To quickly drop a full tables' content, I want to run the following code:

@Service
public class DatabaseService {
    @Autowired
    private EntityManager em;

    @Transactional
    public void clear() {
        em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
    }
}

Result:

ERROR org.springframework.integration.handler.LoggingHandler: javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:71)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)

If I make this change:

public void clear() {
    em.getTransaction().begin();
    em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
}

Result:

ERROR org.springframework.integration.handler.LoggingHandler: java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:245)
    at com.sun.proxy.$Proxy84.getTransaction(Unknown Source)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)

I also tried spring-data-jpa, but also fails:

public interface MyRepository extends CrudRepository<MyEntity, Integer> {
    @Query(value = "TRUNCATE TABLE MyTable", nativeQuery = true)
    @Modifying
    public void clear();
}

So, how can I create a transaction and run the truncate in a shared spring context?

The Spring application is started using: SpringApplication.run(AppConfig.class, args); having:

@Bean
public JpaTransactionManager transactionManager() {
    return new JpaTransactionManager(emf);
}

Outlandish

Please post the full stacktraces including caused by. – Desiderative

Don't use @Autowired use @PersistenceContext instead. – Margret

@PersistenceContext did not change anything. Stacktrace updated above. – Outlandish

You shouldn't start a transaction yourself... The @Transactional should start it... Have you actually tried that after the change? – Margret

I tried both the @Transactional and the em.getTransaction().begin(); explicit, both lead each to the same errors as above. – Outlandish

do you have a transaction manager bean declared? – Heteropterous

Yes I have, see update – Outlandish

Can you see the transaction managager bean in the list of beans created (debug log of Spring application context initialization) ? – Carnage

You are using Spring boot then why have you configured things yourself instead of using Spring Boot (currently looks like you are trying to work around it instead of using it). – Margret

Answer 1

You should use TransactionTemplate object to manage transaction imperatively:

transactionTemplate.execute(
    status -> em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate());

To create TransactionTemplate just use injected PlatformTransactionManager:

transactionTemplate = new TransactionTemplate(platformTransactionManager);

And if you want to use new transaction just invoke

transactionTemplate.setPropagationBehavior(
    TransactionDefinition.PROPAGATION_REQUIRES_NEW);

Ciliate

Answer 2

As a workaround I now created a new EntityManager explicit using the EMF, and starting the transaction manually.

@Autowired
private EntityManagerFactory emf;

public void clearTable() {
    EntityManager em = emf.createEntityManager();
    EntityTransaction tx = em.getTransaction();
    tx.begin();
    em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
    tx.commit();
    em.close();
}

That's probably not ideal, but works for the moment.

Outlandish

There is no reason why @Transactional shouldn't work, there must be something wrong with your setup. Also both solutions are workarounds instead of actually solving the problem. One thing I wonder is have you used the correct @Transactional the one from spring and not the newer from JEE7? – Margret

And is the API in your classpath or only as a provided dependency? Also you aren't using interfaces so make sure that you enable classbased proxies... Finally could you add the full stacktrace to your post? – Margret

Instead of the javax.transaction.Transactional try using the Spring @Transactional. Depending on the available classes the the javax.transaction.Transactional is enabled or disabled. – Margret

"works for the moment" might the most used sentence in software engineering – Homeward

In my case, I needed to start a transaction inside an integration test. This saved me a couple hours. Thanks! – Lovelace

Answer 3

Spring Data JPA automatically runs CRUD method in transactions for you (without needing to set up anything except a transaction manager). If you want to use transactions for your query methods, you can simply add @Transactional to these:

interface MyRepository extends CrudRepository<MyEntity, Integer> {

  @Transactional
  @Modifying
  @Query(value = "TRUNCATE TABLE MyTable", nativeQuery = true)
  void clear();
}

On a more general note, what you have declared here is logically equivalent to CrudRepository.deleteAll(), except that it (your declaration) doesn't honor JPA-level cascades. So I wondered that's really what you intended to do. If you're using Spring Boot, the activation and transaction manager setup should be taken care of for you.

If you want to use @Transactional on the service level, you need to setup both a JpaTransactionManager and activate annotation based transaction management through either <tx:annotation-driven /> or @EnableTransactionManagement (looks like the activation was the missing piece on your attempt to create transactions on the service layer).

Plague

I think using @transactional means spring will automatically handle your commits and flushes though. – Diagram

I suspect that the key was <tx:annotation-driven/> not being set up in Spring. Basically, Spring is managing the entity manager for you (so won't let you manage your own transactions), but you didn't tell it how to manage transactions. – Battue

Answer 4

@Transactional annotation should not be applied on Dao method but on a service method. Although your code says DatabaseService is a service, but by inserting EntityManger inside a service does not make any sense.

Correct way to implement is to create a Dao like below.

@Repository
public class DatabaseDao {
    @PersistenceContext
    private EntityManager em;

    public void clear() {
        em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
    }
}

Then call the dao method from a service method with @Transactional annotation.

@Service
public class DatabaseService {
    @Autowired
    private DatabaseDao dao;

    @Transactional
    public void clear() {
        dao.clear();
    }
}

Also, add @EnableTransactionManagement in your Configuration class

Eolithic