Spring Boot JDBCTemplate with MySQL and Maria DB Commit and Rollback Issue

I’m using the Spring Boot framework with JDBCTemplate for database access. I use Transactional annotation to enforce transactions for DB calls.

@Transactional(rollbackFor = IllegalStateException.class, propagation = Propagation.REQUIRES_NEW) 

With Maria DB, I can see below logs the sequence of transactions and my Transaction rollbacks, and nothing gets committed.

o.s.j.d.DataSourceTransactionManager     : Creating new transaction with name [Test]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT,-java.lang.IllegalStateException o.s.j.d.DataSourceTransactionManager     : Acquired Connection [HikariProxyConnection@368558459 wrapping org.mariadb.jdbc.MariaDbConnection@3e839aa3] for JDBC transaction o.s.jdbc.core.JdbcTemplate               : Executing SQL statement [INSERT INTO test (Col1,Col2) values(1,'ABC')] o.s.j.d.DataSourceTransactionManager     : Initiating transaction rollback o.s.j.d.DataSourceTransactionManager     : Rolling back JDBC transaction on Connection [HikariProxyConnection@368558459 wrapping org.mariadb.jdbc.MariaDbConnection@3e839aa3] o.s.j.d.DataSourceTransactionManager     : Releasing JDBC Connection [HikariProxyConnection@368558459 wrapping org.mariadb.jdbc.MariaDbConnection@3e839aa3] after transaction 

However, with MySQl 5.1 database I can see in logs that transaction is rolled back, but DB changes are still committed.

In Debug mode, I was able to see the moment JDBC template.execute call happens records get committed and trscationManager get a notification for rollback as I am throwing explicit Checked Exception and defining.

Why rollback is not happening with MySQL but happens in MariaDB?

My SQL LOG Trail

DataSourceTransactionManager     : Creating new transaction with name [TEST]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; 'jdbcDataSourceTransactionManager'  DataSourceTransactionManager  : Acquired Connection [HikariProxyConnection@436329238 wrapping  com.mysql.jdbc.JDBC4Connection@2b464384] for JDBC transaction JdbcTemplate               : Executing prepared SQL update  JdbcTemplate               : Executing prepared SQL statement [INSERT INTO TEST (dataNetwork,start, end, type, directory, rank) values(?, ?, ?,?,?,?)]  DataSourceTransactionManager     : Initiating transaction rollback  DataSourceTransactionManager     : Rolling back JDBC transaction on Connection [HikariProxyConnection@436329238 wrapping   com.mysql.jdbc.JDBC4Connection@2b464384] DataSourceTransactionManager     : Releasing JDBC Connection [HikariProxyConnection@436329238 wrapping   com.mysql.jdbc.JDBC4Connection@2b464384] after transaction DispatcherServlet        : Failed to complete request:  java.lang.RuntimeException: To Test Roll Back 

Service Method Code:

@Transactional(transactionManager = "jdbcDataSourceTransactionManager") public void copyDataNetwork(WorkingFolderCopyRequest workingFolderCopyRequest,       DataNetworkTransaction transaction) throws RuntimeException {     dataNetoworkDao.copy(DataNetworkTables.DATA_NETWORK.getTableName(),        DataNetworkTables.DATA_NETWORK.getColumnName(),         workingFolderCopyRequest.getSource(),   workingFolderCopyRequest.getDestination());      throw new RuntimeException("To Test Roll Back"); } 

DataNetworkDAO

@Repository public class DataNetworkDAO  {      private NamedParameterJdbcTemplate jdbcTemplate;      @Autowired     DataSource dataSource;      public DataNetworkDaoImpl(@Qualifier("ooretaDataSource")DataSource dataSource) {         this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);     }      public void copy(String tableName, String dataNetworkColumn, String oldNetworkName, String newNetworkName) {         String sql = "INSERT INTO TEST (dataNetwork,start, end, type, directory, rank) values(:dataNetwork, :start, :end,:type,:directory,:rank)";         Map<String, Object> params = new HashMap<>();         params.put("dataNetwork", "WF20");         params.put("start", "2");         params.put("end", "");         params.put("type", "T");         params.put("directory", "Temp");         params.put("rank", 0);          jdbcTemplate.update(sql, params);              } } 

DataSource Config

HikariPool-2 – configuration: allowPoolSuspension………….false autoCommit………………….false HikariPool-2 – Starting… HikariPool-2 – Added connection com.mysql.jdbc.JDBC4Connection@608eb42e HikariPool-2 – Start completed.

o.h.e.j.e.i.JdbcEnvironmentInitiator : Database -> name : MySQL version : 5.1.73-community major : 5 minor : 1

o.h.e.j.e.i.JdbcEnvironmentInitiator : Driver -> name : MySQL Connector Java version : mysql-connector-java-5.1.25 ( Revision: ${bzr.revision-id} ) major : 5 minor : 1

Add Comment
1 Answer(s)

After all struggles, I found the reason. Programmer Happiness I got in this COVID time.

MySql has 2 DB Engines MyISAM and InnoDB. This legacy DB is having all the tables with the MyISAM engine. MyISAM engine doesn’t support transactions. You can’t rollback.

If we use SHOW TABLE STATUS; then it will show which table is with which engine type and then we need to Alter the table engine as below to get transactional support.

ALTER TABLE <<table_name>> ENGINE = ‘InnoDB’;

And finally, rollback worked like a charm.

Link to read more:

http://ronaldbradford.com/blog/using-rollback-with-myisam-2010-03-31/#:~:text=The%20MySQL%20default%20storage%20engine,at%20the%20delta%20over%20time.

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.