I would try to be as concise as possible here. I have gone through the numerous similar problems reported on the web, and evaluated my issue against those. However, it did not solve my problem. So finally I am putting this up.
I have a spring annotated transactional service (annotated on the implemented class method) with default values. The service does some insert (within a loop) and a no-parameter stored procedure call, by directly invoking mybatis mapper methods (autowired in the service).
I find the transaction is not rolling back on exception (runtime exceptions, as Spring would expect). I tested by commenting the stored proc call, inserting without using loop but that did not help.
Looking at the logs, I could find though the transaction synchronization takes place as expected with rollbacks, Spring is switching to manual commit. I am not sure if that is causing the problem?
I tested with JNDI connection on Tomcat and Driver manager connection unit testing, and it was the same behaviour for both. Below is an excerpt from the logs:
- Spring 3.1.1
- mybatis 3.2.1
- mybatis-spring 1.2.2
- mysql-connector 5.1.30
- Mysql DB 5.5.24
======================================
Using DriverManagerDataSource testing
======================================
2014-10-17 12:34:17 [main] DEBUG DataSourceTransactionManager - Creating new transaction with name [com.ericsson.prf.web.service.impl.ReportingService.saveHeadcountCorrectionsJSON]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
2014-10-17 12:34:17 [main] DEBUG DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/PRFDB]
2014-10-17 12:34:17 [main] DEBUG DataSourceTransactionManager - Acquired Connection [com.mysql.jdbc.JDBC4Connection@728cacdc] for JDBC transaction
2014-10-17 12:34:17 [main] DEBUG DataSourceTransactionManager - Switching JDBC Connection [com.mysql.jdbc.JDBC4Connection@728cacdc] to manual commit
2014-10-17 12:34:17 [main] DEBUG SqlSessionUtils - Creating a new SqlSession
2014-10-17 12:34:17 [main] DEBUG SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@114d4c11]
2014-10-17 12:34:17 [main] DEBUG SpringManagedTransaction - JDBC Connection [com.mysql.jdbc.JDBC4Connection@728cacdc] will be managed by Spring
2014-10-17 12:34:17 [main] DEBUG updateHeadcountPlanned - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@728cacdc]
2014-10-17 12:34:17 [main] DEBUG updateHeadcountPlanned - ==> Preparing: UPDATE PRF_HEADCOUNT_PLANNED SET COUNT=? WHERE ID=?
2014-10-17 12:34:17 [main] DEBUG updateHeadcountPlanned - ==> Parameters: 43(Integer), 1629(Long)
2014-10-17 12:34:17 [main] DEBUG SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@114d4c11]
2014-10-17 12:34:17 [main] DEBUG SqlSessionUtils - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@114d4c11] from current transaction
2014-10-17 12:34:17 [main] DEBUG callLoadHCCostPlanned - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@728cacdc]
2014-10-17 12:34:17 [main] DEBUG callLoadHCCostPlanned - ==> Preparing: {call load_sap_hc_cost_planned()}
2014-10-17 12:34:17 [main] DEBUG SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@114d4c11]
2014-10-17 12:34:18 [main] DEBUG SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@114d4c11]
2014-10-17 12:34:18 [main] DEBUG SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@114d4c11]
2014-10-17 12:34:18 [main] DEBUG DataSourceTransactionManager - Initiating transaction rollback
2014-10-17 12:34:18 [main] DEBUG DataSourceTransactionManager - Rolling back JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@728cacdc]
2014-10-17 12:34:18 [main] DEBUG DataSourceTransactionManager - Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@728cacdc] after transaction
2014-10-17 12:34:18 [main] DEBUG DataSourceUtils - Returning JDBC Connection to DataSource
======================================
Using JndiObjectFactoryBean on Tomcat
======================================
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG AnnotationTransactionAttributeSource - Adding transactional method 'saveHeadcountCorrectionsJSON' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DefaultListableBeanFactory - Returning cached instance of singleton bean 'transactionManager'
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceTransactionManager - Creating new transaction with name [com.ericsson.prf.web.service.impl.ReportingService.saveHeadcountCorrectionsJSON]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceTransactionManager - Acquired Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java] for JDBC transaction
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceTransactionManager - Switching JDBC Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java] to manual commit
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Creating a new SqlSession
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46ee04fb]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SpringManagedTransaction - JDBC Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java] will be managed by Spring
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG updateHeadcountPlanned - ooo Using Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG updateHeadcountPlanned - ==> Preparing: UPDATE PRF_HEADCOUNT_PLANNED SET COUNT=? WHERE ID=?
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG updateHeadcountPlanned - ==> Parameters: 43(Integer), 1629(Long)
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46ee04fb]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46ee04fb] from current transaction
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG callLoadHCCostPlanned - ooo Using Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG callLoadHCCostPlanned - ==> Preparing: {call load_sap_hc_cost_planned()}
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46ee04fb]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46ee04fb]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46ee04fb]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceTransactionManager - Initiating transaction rollback
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceTransactionManager - Rolling back JDBC transaction on Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java]
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceTransactionManager - Releasing JDBC Connection [jdbc:mysql://localhost:3306/PRFDB?autoReconnect=true&useUnicode=true&characterEncoding=UTF8, UserName=prf@localhost, MySQL Connector Java] after transaction
2014-10-17 12:53:51 [http-bio-8080-exec-3] DEBUG DataSourceUtils - Returning JDBC Connection to DataSource
