Transaction not rolling back in Spring & Mybatis & MySql

[navdeep]

Hi,

FYI: I am using Spring 3.1, MyBatis 3.2, MySql 5.5.I have tested the transaction rollback on mysql without MyBatis and it is working fine.

Problem: My transaction is not rolling back. when i call saveUser to insert user with user name "abc" it should have to rollback based on the simulated error But it does not.

Spring Configuration file:

      <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/nav" />
		<property name="username" value="user1" />
		<property name="password" value="user1" />
		<property name="defaultAutoCommit" value="false"/>
	</bean>
	
	<tx:advice id="txAdvice"  transaction-manager="transactionManager">
           <tx:attributes>
                <tx:method name="saveUser" propagation="REQUIRED" rollback-for="Exception" />
           </tx:attributes>
        </tx:advice>
	
       <aop:config>
            <aop:pointcut id="createOperation" expression="execution(* com.mapperDao.UserMapperDaoImpl.*(..))"/>
            <aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>
       </aop:config>

       <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
               <property name="dataSource"  ref="dataSource" />    
       </bean>
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	        <property name="dataSource" ref="dataSource" />
	        <property name="configLocation" value="mybatis-config.xml" />
	</bean>
	<!-- sql Session available at your door steps -->
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
	<bean id="userMapperDao" class="com.mapperDao.UserMapperDaoImpl">
		<property name="sqlSession" ref="sqlSession" />
	</bean>
	<bean id="user" class="com.model.User">
		<property name="id" value="2"/>
		<property name="name" value="Anil"/>
		<property name="sex" value="Male"/>
	</bean>

mybatis-config.xml

.........
	<mappers>
		<mapper resource="com/mapperXml/UserMapper.xml"/>
	</mappers>
.......

UserMapper.xml

....
<mapper namespace="com.mapperDao.UserMapperDao" />
.....

UserMapperDaoImpl.java

        public class UserMapperDaoImpl  implements UserMapperDao{

	private SqlSession sqlSession;
           //gettter and setter for sqlSession
        @Override
	public void saveUser(User user) {
		// TODO Auto-generated method stub
		
		try {	
			UserMapperDao userMapper=sqlSession.getMapper(UserMapperDao.class);
			userMapper.saveUser(user);
			if(user.getName().equalsIgnoreCase("abc")){
				throw new Exception("Simulate Error...");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
     }

MainClass

   ApplicationContext context=new ClassPathXmlApplicationContext("spring-configuration.xml");
			UserMapperDao userDao=(UserMapperDao) context.getBean("userMapperDao");
			
			List list=userDao.getAllUser();
			System.out.println(list);
			
			User user=(User)context.getBean("user");
			userDao.saveUser(user);
			user.setId("1");
			user.setName("Akash");
			userDao.saveUser(user);
			user.setName("abc");
			userDao.saveUser(user);

when i call saveUser with user name "abc" it should have to rollback based on the simulated error But it does not.

Thanks
Navdeep

[m_auro1]

Hi Navdeep,
the transaction is defined if Exception is thrown from saveUser method, but in your code the catch only do a e.printStackTrace() and no exception is thrown by that method.

Adjust your code by re-throw the exception after e.printStackTrace() or remove the catch block.

Bye
m_auro1

[navdeep]

This should not be the case like you said because i am already throwing a exception from saveUser method, However i have tried your way and Transactions still not rolling back.

[Marten Deinum]

Please use [ code][/code ] tags when posting code, that way it remains readable.

As m_auro1 already mentioned your code breaks proper transaction management.

public class UserMapperDaoImpl implements UserMapperDao{

	private SqlSession sqlSession;
	//gettter and setter for sqlSession
	@Override
	public void saveUser(User user) {
		// TODO Auto-generated method stub
		try {	
			UserMapperDao userMapper=sqlSession.getMapper(UserMapperDao.clas s);
			userMapper.saveUser(user);
			if(user.getName().equalsIgnoreCase("abc")){
				throw new Exception("Simulate Error...");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}

The part in red catches the exception you throw and as such the exception isn't seen by the transaction management for spring and as such a rollback wont happen.

Next your pointcut is also flawed

<aop:config>
  <aop:pointcut id="createOperation" expression="execution(* com.mapperDao.UserMapperDaoImpl.*(..))"/>
  <aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>
</aop:config>

The pointcut should be on the interface instead of the impl

<aop:config>
  <aop:pointcut id="createOperation" expression="execution(* com.mapperDao.UserMapperDao+.*(..))"/>
  <aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>
</aop:config>

Also why are you reconfiguring to rollback for Exception, I suggest using a RuntimeException instead (this is rolled back by default). So I would remove the rollback-for attribute from your tx-advice and instead of an Exception throw a RuntimeException.

Also what do you expect to be rolled back?! User 1 is still going to be in the database as that transaction is already committed so if you expect to have that user removed from the database I suggest chancing your expectations an read-up on transactions.

[navdeep]

Thanks Marten,

i tried your way But still not happening and getting serious for me. However I have another sample without MyBatis, which is working fine. Please find code below:

   <bean id="dataSource" 
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost:3306/nav"/>
      <property name="username" value="user1"/>
      <property name="password" value="user1"/>
    </bean>
  
   <tx:advice id="txAdvice"  transaction-manager="transactionManager">
      <tx:attributes>
          <tx:method name="create" propagation="REQUIRED" rollback-for="Exception"/>
      </tx:attributes>
   </tx:advice>
	
   <aop:config>
      <aop:pointcut id="createOperation" expression="execution(* com.tutorialspoint.StudentJDBCTemplate.*(..))"/>
      <aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>
   </aop:config>
	
   <!-- Initialization for TransactionManager -->
   <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
      <property name="dataSource"  ref="dataSource" />    
   </bean>
   
   <!-- Definition for studentJDBCTemplate bean -->
   <bean id="studentJDBCTemplate" class="com.tutorialspoint.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />  
   </bean>

    
    public class StudentJDBCTemplate implements StudentDAO{
            private JdbcTemplate jdbcTemplateObject;
            public void setDataSource(DataSource dataSource) {
                        this.jdbcTemplateObject = new JdbcTemplate(dataSource);
                }

           public void create(String name, Integer age, Integer marks, Integer year){

               try {
                   String SQL1 = "insert into Student (name, age) values (?, ?)";
                  jdbcTemplateObject.update( SQL1, name, age);

                // Get the latest student id to be used in Marks table
                String SQL2 = "select max(id) from Student";
                int sid = jdbcTemplateObject.queryForInt( SQL2 );

                String SQL3 = "insert into Marks(sid, marks, year) " + "values (?, ?, ?)";
                jdbcTemplateObject.update( SQL3, sid, marks, year);

                System.out.println("Created Name = " + name + ", Age = " + age);
                // to simulate the exception.
                 throw new RuntimeException("simulate Error condition") ;
              } catch (DataAccessException e) {
                     System.out.println("Error in creating record, rolling back");
                     throw e;
                }
         }

Here you can see the only change is JdbcTemplate replacing MyBatis . Source is: tutorialspoint website.