Java JPA and rollback on MySQL
By Pierre-Yves on Monday, June 18 2012, 18:40 - Bioinformatique - Permalink
We (b8e5n and I) have been fighting for too long for this, so there it is dear lazy web.
Using the eclipse JPA (Java Persistence API) to connect to a MySQL database you might experienced some troubles.
Using the following piece of code:
transaction = manager.getTransaction(); transaction.begin(); try { // Add here your logic transaction.commit(); } finally { if (transaction.isActive()) { transaction.rollback(); } }
We kept running into the fact that the rollback never happened. The commit was performed or at least started and if something was going wrong at the database level (like an already existing primary key), the data already commited to the database would remain in there.
So yay for the half-commited data :-s
It took us a while but we finally ended up finding the solution on a lost corner of the web:
** Please take special note for MySQL database users: do make sure that your tables are InnoDB tables instead of MyISAM. If not, rollback will not occur.
Switching from MyISAM to InnoDB solved the problem for us, the transaction is either fully commited or not at all.
Hope this can help.
Comments
Ouch, yeah, MyISAM does not support transactions (nor relationships). It's pretty crappy actually, but could be faster if you have mostly read operations.
Also, does transaction.commit() not throw any exceptions?
if so, i would rewrite the code to:
transaction = manager.getTransaction();
transaction.begin();
try {
// Add here your logic
transaction.commit();
} catch (DBException e) {
transaction.rollback();
}
Since finally is Always executed your code would be the same as:
transaction = manager.getTransaction();
transaction.begin();
transaction.commit();
if (transaction.isActive)
{
transaction.rollback();
}
a try without a catch doesn't make much sense imho.
Hi Frank,
> Also, does transaction.commit() not throw any exceptions?
Yes it does throw an exception but not having the catch here allows to handle the exception at a higher level while still having a clean database since the rollback has happened.
Both approaches are valid :-)