Announcement Announcement Module
Collapse
No announcement yet.
Transaction with Spring JDBC template Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transaction with Spring JDBC template

    Hi,

    I want to implement Transaction Management with Spring JDBC Template.
    I used transaction advice, Annotation Driven transaction approach but didn't get any results.

    I have two insert statements, i want them both to be completed.
    It performs insertion on one table, when goes to perform insertion on second table, an exception is thrown. I want the transaction to rollback on this exception.

    My transaction advice is as follows:
    Code:
     <tx:advice id="addTxAdvice" transaction-manager="transactionManager">
    		<tx:attributes>
    			<tx:method name="addTest" propagation="REQUIRED" rollback-for="java.lang.Exception"/>
    		</tx:attributes>
    	</tx:advice>
    
    	<aop:config>
    		<aop:pointcut id="addAOP"
    			expression="execution(* com.test.DaoImplementation.*(..))" />
    		<aop:advisor advice-ref="addTxAdvice"
    			pointcut-ref="addAOP" />
    	</aop:config>
    But didn't get any results.

    Tried using the annotation approach:

    Code:
    @Transactional(propagation=Propagation.REQUIRED,rollbackForClassName="Exception",isolation=Isolation.READ_COMMITTED,rollbackFor=Exception.class)
    public int addMethod()throws Exception
    {
    insert1();
    insert2();
    return 1;
    }
    
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        	<property name="dataSource" ref="dataSource"/>
      	</bean> 
      	
      	<tx:annotation-driven transaction-manager="transactionManager"/>
    Got the following in the debug logs:
    Code:
    DEBUG 2012-07-18 17:09:20,255 [http-8080-1] (AbstractBeanFactory.java:242) (doGetBean) - Returning cached instance of singleton bean 'transactionManager'
    DEBUG 2012-07-18 17:09:20,261 [http-8080-1] (AbstractPlatformTransactionManager.java:365) (getTransaction) - Creating new transaction with name [com.test.dao.Dao.addMethod]: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED; '',-java.lang.Exception,-Exception
    DEBUG 2012-07-18 17:09:20,261 [http-8080-1] (DriverManagerDataSource.java:162) (getConnectionFromDriver) - Creating new JDBC DriverManager Connection to [jdbc:mysql://url:3306/local_db]
    DEBUG 2012-07-18 17:09:20,275 [http-8080-1] (DataSourceTransactionManager.java:204) (doBegin) - Acquired Connection [com.mysql.jdbc.JDBC4Connection@125ee49] for JDBC transaction
    DEBUG 2012-07-18 17:09:20,281 [http-8080-1] (DataSourceUtils.java:187) (prepareConnectionForTransaction) - Changing isolation level of JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] to 2
    DEBUG 2012-07-18 17:09:20,284 [http-8080-1] (DataSourceTransactionManager.java:221) (doBegin) - Switching JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] to manual commit
     INFO 2012-07-18 17:09:20,286 [http-8080-1] (?:?) (add) - In DaoImplementation / addMethod()
    DEBUG 2012-07-18 17:09:20,287 [http-8080-1] (JdbcTemplate.java:880) (batchUpdate) - Executing SQL batch update [INSERT INTO mapping(NAME, DOC_NO,ALLOCATE_FLAG, CREATED_BY) VALUES(?, ? ,?, ?)]
    DEBUG 2012-07-18 17:09:20,288 [http-8080-1] (JdbcTemplate.java:569) (execute) - Executing prepared SQL statement [INSERT INTO mapping(NAME, DOC_NO,ALLOCATE_FLAG, CREATED_BY) VALUES(?, ? ,?, ?)]
    DEBUG 2012-07-18 17:09:20,289 [http-8080-1] (JdbcUtils.java:362) (supportsBatchUpdates) - JDBC driver supports batch updates
     INFO 2012-07-18 17:09:20,297 [http-8080-1] (?:?) (add) - In DaoImplementation / insertQuery 
    DEBUG 2012-07-18 17:09:20,298 [http-8080-1] (AbstractPlatformTransactionManager.java:843) (processRollback) - Initiating transaction rollback
    DEBUG 2012-07-18 17:09:20,299 [http-8080-1] (DataSourceTransactionManager.java:279) (doRollback) - Rolling back JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@125ee49]
    DEBUG 2012-07-18 17:09:20,300 [http-8080-1] (DataSourceUtils.java:213) (resetConnectionAfterTransaction) - Resetting isolation level of JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] to 4
    DEBUG 2012-07-18 17:09:20,300 [http-8080-1] (DataSourceTransactionManager.java:322) (doCleanupAfterCompletion) - Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] after transaction
    DEBUG 2012-07-18 17:09:20,301 [http-8080-1] (DataSourceUtils.java:332) (doReleaseConnection) - Returning JDBC Connection to DataSource
    It is showing initiating rollback, but it is not rolling back.

    What may be the issue?
    Please help.

    Thanks in advance.
    Regards,
    Annuk
    Last edited by annuk; Jul 18th, 2012, 07:47 AM.

  • #2
    You are using MySQL make sure you use table types that support transactions, if your tables are MyISAM tables those aren't transactional.

    Comment


    • #3
      Hi Marten Deinum,

      Originally posted by Marten Deinum View Post
      You are using MySQL make sure you use table types that support transactions, if your tables are MyISAM tables those aren't transactional.
      The tables were MyISAM.Changed it to INNODB and it worked.
      Thanks.
      Regards,
      Annuk.

      Comment

      Working...
      X