During a Java-based system refactoring project—intended to replace an aging PHP application while reusing its existing MySQL database—developers observed inconsistent transaction behavior. Despite correctly applying @Transactional on service methods and deliberately triggering unchecked exceptions (e.g., int result = 1 / 0;), database changes persisted instead of rolling back.

The Spring Boot configuration appeared standard:

@SpringBootApplication
@EnableTransactionManagement
@MapperScan("com.example.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

And the service logic followed expected patterns:

@Service
public class AccountService {

    @Autowired
    private AccountMapper accountMapper;

    @Transactional
    public void transferFunds(Long fromId, Long toId, BigDecimal amount) {
        accountMapper.debit(fromId, amount);
        accountMapper.credit(toId, amount);
        // Simulate failure
        throw new RuntimeException("Simulated error");
    }
}

After exhaustive verification—including checking transaction manager auto-configuration, connection pool settings, exception types, and even deploying to isolated environments—the issue remained unresolved until a critical insight emerged: the underlying storage engine.

Running this query revealed the root cause:

SELECT table_name, engine 
FROM information_schema.tables 
WHERE table_schema = 'wxsm' AND engine = 'MyISAM';

Every table in the legacy schema used MyISAM, a non-transactional engine optimized for read-heavy workloads but lacking support for ACID compliance, row-level locking, or rollback capabilities.

In contrast, InnoDB provides full transaction support—including savepoints, crash recovery, and foreign key enforcement—making it the de facto choice for modern applications requiring data consistency.

To migrate all affected tables safely, execute the following dynamic SQL generation:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS migration_statement
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema = 'wxsm';

Copy and run the rseulting ALTER TABLE statements. For large tables, consider performing migrations during low-traffic windows, as ALTER TABLE ... ENGINE=InnoDB rebuilds the table and may lock it temporarily.

Once converted, Spring-managed transactions behave as expected: exceptions trigger automatic rollback, and committed operations persist atomically across multiple DML statements.

Note: While converting from MyISAM to InnoDB is generally safe—and often recommended—be aware that InnoDB consumes more memory and disk space due to its transaction log (ib_logfile) and clustered index architecture. However, these trade-offs are justified by reliability and correctness guarantees essential for business-critical operations.