How I fixed my JDBC MySQL transaction problem

A JDBC MySQL transactions tip: If your transactions aren't working with your new MySQL database you may have the same problem I just had. I created my database tables with the default MySQL storage engine (MyISAM), and guess what? MyISAM doesn't support transactions.

A cool thing about MySQL is that it supports multiple database engines (storage engines), so to fix the problem where my transactions weren't working, all I had to do was specify a MySQL storage engine that does support MySQL transactions, in this case the MySQL InnoDB storage engine.

Specifying a different MySQL database engine

To tell MySQL that I want to use the InnoDB storage engine, I just have to use the following syntax, specifying the database engine at the end of the MySQL CREATE TABLE command:

create table users (
  id int auto_increment not null unique,
  username varchar(32) not null unique,
  password varchar(16) not null,
  primary key (id)
) ENGINE = InnoDB;

Once I changed my CREATE TABLE statements like this, and rebuilt my database, my JDBC transactions began working as advertised.

Of course in complicated applications there's much more to know about MySQL transaction support and MySQL database storage engines, so I'll refer you to this this MySQL storage engine page.