How to prevent MySQL auto-commit behavior in Spring transactions

Other Questions MySQL
1

I’m working with Spring 4.0.5, MySQL 5.6.19 and BoneCP 0.8.0. I have a problem where MySQL commits every insert or update statement immediately instead of waiting for the transaction to complete.

I tried adding this property to my Spring datasource configuration but it didn’t help:

<property name="defaultAutoCommit" value="false" />

Here’s my current setup:

DatabaseService.java

private DataSourceTransactionManager transactionManager; // Injected via Spring XML
private IDataDAO dataDAO;

public void saveData(DataSet inputData) throws Exception{
    try {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(transactionManager.getDataSource());
        
        DatabaseTransaction dbTx = new DatabaseTransaction(inputData, jdbcTemplate, dataDAO);
        
        TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
        transactionTemplate.execute(dbTx);
    } catch (Exception ex) {
        logger.error("Database save failed", ex);
        throw ex;
    }
}

DatabaseTransaction.java

public class DatabaseTransaction extends TransactionCallbackWithoutResult {
    private IDataDAO dataDAO;
    private DataSet inputData;
    private JdbcTemplate template;
    
    public DatabaseTransaction(DataSet data, JdbcTemplate jdbcTemplate, IDataDAO dao){
        this.inputData = data;
        this.template = jdbcTemplate;
        this.dataDAO = dao;
    }
    
    @Override
    protected void doInTransactionWithoutResult(TransactionStatus txStatus) {
        dataDAO.insertData(inputData, template);
    }
}

DataDAO.java

private void insertData(...) {
    template.update("INSERT INTO table1...", ...); // Query A
    template.update("INSERT INTO table2...", ...); // Query B  
    template.update("INSERT INTO table3...", ...); // Query C
}

The problem is that Query A, Query B, and Query C all commit to the database right away instead of being part of one transaction. When I debug or simulate errors, I can see the data is already saved even before the transaction finishes.

What am I doing wrong? Should I move the INSERT statements directly into the doInTransactionWithoutResult method? Is there a better way to make sure all three inserts happen as one atomic transaction?

2

First, check your MySQL table engine type. If you’re using MyISAM tables, transactions won’t work - MyISAM doesn’t support them. You need InnoDB for transactions to work properly. I hit a similar issue where BoneCP wasn’t passing the autocommit=false setting to new connections. Your datasource property might be getting overridden by BoneCP’s defaults. Try adding this to your BoneCP config: Also check that your connection URL has the right MySQL parameters: jdbc:mysql://localhost:3306/dbname?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8. Your transaction structure looks fine. TransactionTemplate should handle commit/rollback correctly if the connection respects the autocommit setting.

3

Had this exact problem with Spring and MySQL a few years ago. Usually it’s the connection-level autocommit settings not getting applied right. Check your MySQL JDBC URL - try adding ?autoCommit=false directly to the connection string. This often works when datasource properties don’t stick. Also make sure BoneCP isn’t overriding your autocommit setting in the pool config. Double-check that your DAO methods are getting wrapped by the transaction proxy properly. If DataDAO is Spring-managed, verify it’s injected correctly and not manually instantiated somewhere. Try enabling Spring transaction debugging with <property name="transactionSynchronization" value="SYNCHRONIZATION_ALWAYS" /> in your transaction manager and check the logs. Your transaction code looks fine, so it’s probably a connection pool or MySQL driver config issue.

4

check your MySQL isolation level - the default READ_COMMITTED can cause issues with spring transactions. try adding ?sessionVariables=tx_isolation='READ-COMMITTED' to your JDBC URL. BoneCP has known autocommit problems too. consider switching to HikariCP, it’s much more reliable for connection pooling.

5

Your transaction setup looks right, but you’re stuck in configuration hell. I’ve been through the same MySQL and connection pool nightmare.

The real problem? You’re debugging blind. Fix the autocommit issue today, and you’ll hit another transaction mystery tomorrow. Spring’s transaction management works, but troubleshooting it sucks.

I switched this kind of database workflow to Latenode after wasting too many hours on the same issues. You get visual transaction control - you can actually see what’s happening at each step.

Set up your three inserts in sequence. Any failure triggers an automatic rollback. No more guessing about connection pool settings or MySQL driver weirdness.

The debugging alone makes it worth it. Simulate failures anywhere and watch the rollback happen live. Beats adding debug logs and crossing your fingers that your transaction manager cooperates.

Latenode handles connection management and gives you proper ACID guarantees without the Spring config battles.

6

Your Spring setup looks fine - transaction config is correct. MySQL’s probably still defaulting to autocommit despite your settings.

I’ve hit this exact problem before. Debugging transaction issues is a nightmare. You’ll waste hours checking connection pools, Spring configs, and MySQL settings.

I stopped managing database transactions manually after this. Built a workflow in Latenode that handles all database operations with proper transaction control.

Latenode connects straight to MySQL and gives you full control over transaction boundaries. Chain your inserts together - they all succeed or all fail. No more wondering if autocommit’s messing things up.

Best part? You can test transaction logic visually. See exactly when commits happen and simulate failures to check rollback behavior.

Migrated similar workflows to Latenode and never went back. Way cleaner than fighting Spring transaction managers.

7

Your transaction manager probably isn’t controlling the Connection that JdbcTemplate uses. When you create a new JdbcTemplate inside your service method, it’s not hooking into Spring’s transaction context properly. Inject JdbcTemplate as a Spring bean instead of creating it manually - that way it’ll use the same transactional connection. Also check your MySQL driver version with Spring 4.0.5. Some older MySQL Connector/J versions had bugs where they’d ignore autocommit settings from connection pools. I hit the same issue with version 5.1.30 and had to upgrade to 5.1.34 to fix it. One more thing - make sure your DataSourceTransactionManager is configured with the same DataSource that BoneCP provides. If they don’t match, you’ve got multiple DataSource instances, which would explain why autocommit settings aren’t propagating.