I spent about 4 hours yesterday trying to figure out why transactions were not rolling back inserts into my mySQL database yesterday, and it turned out to be a problem in somewhere I least expected.
There are a couple of really good and concise tutorials on how to set up transactions in Spring:
What is supposed to happen is if any exception is thrown in your base class, the transaction-wrapped proxy (TransactionProxyFactoryBean) that Spring is using will roll back your object state, which with Hibernate involved, should cancel any db operations that would have normally taken place. I turned on DEBUG logging, and the transaction was noted as being rolled back but the db was still merrily taking inserts from the frontend, even with the exceptions being thrown.
I finally found this forum that mentioned transactions with mySQL specifically:
"1. if you are using MySQL make sure you have InnoDB tables (not MyISAM or something else)."Indeed, “
show table status like 'testTable'\G;” displayed MyISAM, so a simple “alter table testTable TYPE = InnoDB2;” solved my problem. Craziness. I’m not intimately familiar with MySQL, but there are many debates going on about what engine to use, just google “mysql myisam innodb”. MyISAM = speed but does not support transactional rollback
InnoDB = reliability and supports transactions
The best advice I found if I were architecting a mySQL enterprise solution would be to use InnoDB on the master machines, where you might need rollback capability in case of error, and then MyISAM on any slaves, since at the point or replication from the master you should have clean data and the speed of replication would be increased by the usage of MyISAM.
Anyway, hopefully this saves 4 hours of time from someone else who might be mystified by non-rolling back database rows in spring hibernate transactions like I was.
