TransactionRequiredException: Executing an update/delete query

Question

I'm having a tough time finding a solution to my problem.
I have a service class, which contains a method to set a verification flag upon login.

@Service("userRolesService")
@Repository
@Transactional
public class UserRolesService {
   public void verify() {
       repository.verifyUser();
   }
}

My Repository is a SpringData CrudRepository, and verifyUser is something like

 @Modifying
 @Query("UPDATE user SET (verified = 1 WHERE verified=0)")
 public void verifyUser();

When calling the code directly in a unit test, everything works fine. When calling it from my authentication provider through the application I get the following exception:

javax.persistence.TransactionRequiredException: Executing an update/delete query

The Service class is injected into both my Unit Test and the authentication provider using the @Autowired annotation. The test itself does not have any interesting annotations itself, nor does the authentication provider.

I'm fresh out of ideas, so if anybody has a clue, I would be very thankful.

EDIT: Instead of calling the verifyUser update script I now retrieve all unverified users, set the verified flag and use save() method of the repository. That works, but is very ugly, so I'm open to better suggestions.

EDIT2:

Per request here is the persistence part of the config, I guess this is most relevant, the rest only deals with authentication. This config is used in both the Unit test and web app, only difference is that the datasources are embedded H2 DB for the unit tests and mysql for the web app.

<beans [..]>

    <bean id="entityManagerFactory"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
          depends-on="persistenceInitializer">
        <property name="dataSource" ref="dataSource"/>
        <property name="persistenceUnitName" value="jpa"/>
        <property name="packagesToScan">
            <list>
                <value>com.example.model</value>
            </list>
        </property>
        <property name="jpaVendorAdapter">
            <bean class="com.example.persistence.adapter.ConfigurationRetainingHibernateJpaVendorAdapter">
                <property name="database" value="${spring.hibernate.database}"/>
                <property name="generateDdl" value="${spring.hibernate.generateDdl}"/>
            </bean>
        </property>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.DefaultComponentSafeNamingStrategy
                </prop>
            </props>
        </property>
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>

    <jpa:repositories base-package="com.example.persistence.repository"/>

    <tx:annotation-driven/>

    <bean id="persistenceInitializer" class="com.example.persistence.init.NoOpInitializer"/>

</beans>

Additionally I have a config that is only in the web app, not the unit tests:

<beans [..]>

    <bean id="propertyConfigurer"
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:application.properties</value>
            </list>
        </property>
    </bean>

    <mvc:annotation-driven/>

    <mvc:default-servlet-handler/>

    <context:annotation-config/>

</beans>

Answer 1

I think if you move the <tx:annotation-driven/> into the context containing <context:annotation-config/>, then Spring will pick up your @Transactional. The <tx:annotation-driven/> is a post processor that only decorates beans in the application context it is defined in. See my answer here for further explanation.

Answer 2

Use this way

@Modifying
    @Transactional 
    @Query(value ="delete from admindata where user_name = :userName AND group_name = :groupName",nativeQuery = true)
    public void deleteadminUser(@Param("userName") String userName,@Param("groupName") String groupName);

Answer 3

- Your service class should not also be a repository

- Here is how your applicationContext.xml should look:

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>
<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager"/>
<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="persistenceUnitName" value="persistenceUnit"/>
    <property name="dataSource" ref="dataSource"/>
</bean>

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="driverClassName" value="${database.driverClassName}"/>
    <property name="url" value="${database.url}"/>
    <property name="username" value="${database.username}"/>
    <property name="password" value="${database.password}"/>
    <property name="testOnBorrow" value="true"/>
    <property name="testOnReturn" value="true"/>
    <property name="testWhileIdle" value="true"/>
    <property name="timeBetweenEvictionRunsMillis" value="1800000"/>
    <property name="numTestsPerEvictionRun" value="3"/>
    <property name="minEvictableIdleTimeMillis" value="1800000"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="initialSize" value="1"/>
    <property name="minIdle" value="1"/>
    <property name="maxActive" value="10"/>
    <property name="poolPreparedStatements" value="true"/>
    <property name="maxOpenPreparedStatements" value="20"/>
</bean>

- Here is how your unit tests classes should be defined

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(loader=WebContextLoader.class, locations = {"classpath:/META-INF/spring/applicationContext.xml", "classpath:/META-INF/spring/applicationContext-test-override.xml"})
public class MyTest {

- NOTE the use of applicationContext-test-override.xml This is used to override any settings in your context for testing. Doing it this way means you are testing the real application context, so if you make mistakes there, it will come up in your tests. It should be located in src/test/resources. This is all you need hopefully:

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="url" value="${database-test.url}"/>
</bean>

- (optional) to use the mode=aspectj

Add the following to the maven plugins. It incorporates aspects at compile time, instead of runtime (mode=proxy).

        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>aspectj-maven-plugin</artifactId>
            <version>1.4</version>
            <dependencies>
                <dependency>
                    <groupId>org.aspectj</groupId>
                    <artifactId>aspectjrt</artifactId>
                    <version>${aspectj.version}</version>
                </dependency>
                <dependency>
                    <groupId>org.aspectj</groupId>
                    <artifactId>aspectjtools</artifactId>
                    <version>${aspectj.version}</version>
                </dependency>
            </dependencies>
            <executions>
                <execution>
                    <goals>
                        <goal>compile</goal>
                        <goal>test-compile</goal>
                    </goals>
                    <!-- NB: force aspect compile before normal compile, required for 1.3+ 
                        see: MASPECTJ-13, MASPECTJ-92 -->
                    <phase>process-sources</phase>
                </execution>
            </executions>
            <configuration>
                <outxml>true</outxml>
                <aspectLibraries>
                    <aspectLibrary>
                        <groupId>org.springframework</groupId>
                        <artifactId>spring-aspects</artifactId>
                    </aspectLibrary>
                </aspectLibraries>
                <source>${java.version}</source>
                <target>${java.version}</target>
            </configuration>
        </plugin>

Answer 4

I had the some problem and I resolved it by just adding @Transactional annotation on the service method that perform delete or update.

Answer 5

I also faced the same Problem and Resolved By Adding the Annotations @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class, readOnly = false)

@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class, readOnly = false)
public class UserRolesService{
..........
}