Object-Relational-Mappers, such as Hibernate, can be a great help to the database using Java developer. “Just” design your object domain model carefully, add some annotations to the code and you can retrieve, manipulate and store your objects just like that. And with minimal boilerplate code all objects are kept nice and tidy in your database. Inheritance and aggregation/composition included. Nifty!!
However, there are some pitfalls. And there is one I fell into today for about two hours. The pit was named “transactions”.
Transactions are a major help to keep your data model in a consistent state. Cut short, a transaction is a sequence of operations that is either processed successfully or it fails and then the data model remains unchanged. No half constructed objects or dead references if handled correctly.
When trying to test my code today, I made a startling discovery. Although my operations where neatly nested inside a Hibernate transaction and when an exception was thrown the rollback() call executed as intended, the objects were already written to the database!
Checking the code over and over again I could not find the mistake. Then, looking at the transaction object’s methods I found the wasRolledBack() method, that returned true if the rollback was done properly. And I did get “true” as return value despite the fact that the transaction had changed the database. Even turning the Hibernate logging level to DEBUG only showed happy messages like:
org.hibernate.transaction.JDBCTransaction rolled back JDBC Connection
So what went wrong?
When you use Hibernate to handle your OR mapping, you configure the database connection in an configuration XML file. Besides the usual stuff like the JDBC driver class, database URL, username and such, there is one additional property:
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
This property defines what SQL “dialect” your chosen database “speaks”. Or so I thought.
As a matter of fact, it does do more. It can also define what database engine is used for the automagically created tables. And in my particular case the MySQLDialect decided to create MyISAM tables. The MyISAM database engine however does _not_ support transactions! So why oh why did I not get an exception when trying to roll back on a non-supported transaction? Even worse. Why did the wasRolledBack() method report true?
I’m not exactly sure if this is a bug in Hibernate, MySQL or if there is simply no way to properly determine if a transaction is supported or not. But I do know that this is a very annoying behaviour of the Hibernate/MySQL combination because you get no hint at all that something went wrong unless you had a look at the database yourself.
The solution is pretty simple, too. All you have to do it to set the dialect property to the correct value such as:
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
This will make Hibernate use the InnoDB database engine when creating the tables. InnoDB does support transactions and the moment I changed the dialect, everything went smoothly as intended.
