This probably isn’t news to anyone but me, but I thought I’d post it anyway for anyone else who reads the documentation only as a last resort.

I was recently debugging a Java EE application that was performing some errant database updates at times when it should have been rolling back the transaction. My first thought was that the (checked) exception being thrown wasn’t properly marked for rollback. After some investigation, I determined that the transaction was indeed being rolled back, but the database seemed to be ignoring the rollback and updating the database anyway.

Digging in a bit more, I found out that the application was using MySQL 5.1.x. I spent some time reviewing the user manual and discovered that the default database engine in that version of MySQL does not support transactions. I found this a bit surprising (hasn’t transaction support been around for a couple of decades now?) but switching to a different database engine in the MySQL configuration was simple enough. I found the instructions for doing so in the section of the manual entitled Setting the Storage Engine.

Basically it all boiled down to adding this line to the my.cnf configuration file:

default_storage_engine = innodb

It was necessary to drop the application schema and recreate it.

Apparently, later versions of MySQL use the InnoDB storage engine by default.