Reference‎ > ‎

Troubleshooting: Transactions

Transactions are a vast topic. Seriously, you could spend a career on that stuff (and many people have made living doing just that).

So it's no wonder things can get a bit tricky at times.

MySQL


The top reason for transaction trouble with MySQL is when people use the MyISAM engine when creating their tables, e.g.:

CREATE TABLE `customer` (
  `name` varchar(30) NOT NULL,
  `balance` decimal(19,4) DEFAULT NULL,
  `credit_limit` decimal(19,4) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The MyISAM storage engine is not transactional. This means that, if you roll back a transaction, you will probably not roll back anything.

For transactions to work as you'd expect, you'll need to use another storage engine. By far the most popular is the InnoDB engine, e.g.:

CREATE TABLE `customer` (
  `name` varchar(30) NOT NULL,
  `balance` decimal(19,4) DEFAULT NULL,
  `credit_limit` decimal(19,4) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

That'll make a world of difference, and a rollback will actually roll back.

Spring

It can sometimes be tempting to declare a controller as transactional, as in:

@Controller
@Transactional  // Do not do that
public class CustomerController {
etc...

Do not do that! Therein lies madness. This is because the controller will be invoked many times, and each invocation will result in a transaction, which will in turn lead to endless confusion, and data being committed when it really shouldn't.

There are many ways to do this properly. One is to declare whatever code you want in a withTransaction block:

Customer.withTransaction() {
if (params.todo)
performAction(params)
}

Another way is to do the work in a service method, and declare the service as transactional.


Vaadin

We have heard reports from some users that Vaadin can behave in unexpected ways with regards to transaction, such as using auto-commit mode. If you use Vaadin with the JPA adapter, please remember that changes must be bracketed by a transaction in order for the logic to execute properly.

In general

If you see data being committed to the database when it shouldn't, for instance if an ABL constraint fails, you can usually pinpoint the culprit by setting a breakpoint in Hibernate's JDBCTransaction.commit. See who calls it. If it gets called more than once within the scope of what you thought was one transaction, something is amiss.

Comments