Container-managed transactions with JBoss/MySQL

Now that I find myself out of a job, I’ve got lots of time to get caught up on all of the technologies and stuff that I really should know.

I know how to build applications using Java/JavaEE. I know how to develop EJBs (particularly session beans and MDBs). I know how to use JSP and servlets. I know how to use JMS. I know how to use RMI. Most of this stuff is pretty automatic after having worked with these technologies over the past few years. Throw in some helper technologies, such as XDoclet (no more manual updating of deployment descriptors for me, thanks!), and things become even easier. While I can’t rattle off different parts of the J2EE 1.4 specifications, I like to think that I’m competent in my ability to use the technologies.

At least, that’s what I like to tell myself.

I had a bit of a disastrous interview last week. I got asked all kinds of questions about how different parts of J2EE work and, while I was able to come up with good answers for most of it, there were certain questions that I totally botched.

One topic that I really hosed was container-managed transactions (CMT). This is something that I should know something about, but in reality I never actually used them in the applications that were being built at my last job. We pretty much just assigned the “Required” transaction attribute to every EJB method and, as far as I was concerned, everything else just sort of worked. For the CWMP RPC method test system I built, transactions weren’t important at all, so I never really bothered researching about how they worked.

Using that botched interview to try to actually learn something, I set about experimenting with CMT. JBoss is the application server with which I am the most familiar, and MySQL is a free RDBMS that I am also familiar with, so I installed these two products for my testing.

Once everything was set up, I set about creating a simple EJB with a few public methods that my RMI-based test client would call. One method would successfully make changes to the database. A second method would make failed changes to the database. A third method would call the first two methods, allowing me to test multi-level transactions.

Based on what I had read about using transactions (and my own previously limited experience), it should be just a matter of setting up the XDoclet @ejb.transaction attribute on the appropriate methods and the container will take care of the rest.

Here’s an example of my “successful” method:

 /*  
  * @ejb.interface-method  
  * @ejb.transaction  
  *   type="Required"  
  */  
 public boolean updateMovie(MovieDTO movie) {  
   boolean success = false;  
   Connection conn = null;  
   
   try {  
     conn = _ds.getConnection();  
     PreparedStatement stmt =  
       conn.prepareStatement("UPDATE movie SET title = ?, year = ? WHERE id = ?");  
     stmt.setString(1, movie.getTitle());  
     stmt.setInt(2, movie.getYear());  
     stmt.setInt(3, movie.getId());  
     success = stmt.execute();  
   } catch (SQLException e) {  
     e.printStackTrace();  
     throw new EJBException(e.getMessage());  
   } finally {  
     closeConnection(conn);  
   }  
   
   return success;  
 }  

It’s a simple method that takes the contents of a DTO and updates the appropriate record in the database. Nothing fancy.

Here’s the code for the method that always fails:

 /*  
  * @ejb.interface-method  
  * @ejb.transaction  
  *   type="Required"  
  */  
 public boolean updateBroken(MovieDTO movie) {  
   boolean success = false;  
   Connection conn = null;  
   
   try {  
     conn = _ds.getConnection();  
     PreparedStatement stmt = conn.prepareStatement("UPDATE movies SET title = ?, year = ? WHERE id = ?");  
     stmt.setString(1, movie.getTitle());  
     stmt.setInt(2, movie.getYear());  
     stmt.setInt(3, movie.getId());  
     success = stmt.execute();  
   } catch (SQLException e) {  
     e.printStackTrace();  
     throw new EJBException(e.getMessage());  
   } finally {  
     closeConnection(conn);  
   }  
   
   return success;  
 }  

The difference here is that the query will fail because the “movies” table does not exist (it’s called ‘movie’).

When I installed the app and ran my test client, both methods behaved as they should. When I called the working method, the update occurred. When I called the non-working method, the update did not occur.

Then I added the following method:

 /**  
 * @ejb.interface-method  
 * @ejb.transaction  
 *   type="Required"  
 */  
 public void doStuff() {  
   ArrayList movieList = getMovies();  
   
   for (MovieDTO movie : movieList)  
     System.out.println(movie.getId() + ": " + movie.getTitle() + " (" + movie.getYear() + ")");  
   
   MovieDTO movieChange = movieList.get(0);  
   movieChange.setYear(movieChange.getYear() + 1);  
   updateMovie(movieChange);  
   
   movieList = getMovies();  
   
   for (MovieDTO movie : movieList)  
     System.out.println(movie.getId() + ": " + movie.getTitle() + " (" + movie.getYear() + ")");  
   
   updateBroken(movieChange);  
 }  

Again, pretty simple. It does the following:

• Get the list of all movies and output them to the console
  
• Change the year for one of the movies and update it via the “successful” method
  
• Get the list of movies again and output them to the console (the idea to show that the update actually occurred)
  
• Try updating the movie record again via the “unsuccessful” method
  

What should happen here is that the record in question will be updated to reflect the new year value (confirmed by the new fetch and display) and then that update should be completely undone because the second update will fail. That means that if I check the database after running the test method the original year value should be there.

This was not what happened. Every time I ran the doStuff() method, the year value continued to increment, despite the fact that the second update always failed. I was very confused.

And so began my 2 day odyssey to figure out what the heck was wrong.

I asked some former colleagues about our use of transactions with Oracle and MS SQL server. They claimed that everything was working as expected.

I read up on how to specify transactions in the deployment descriptors and verified that XDoclet was doing the right thing.

I started searching the web to see if anyone else was having problems getting transactions to work with MySQL and JBoss. It turned out that lots of people had problems getting them to work, and while people were eventually successful, I couldn’t see what they were doing to get it to work.

At one point, I stumbled across something that talked about needing to use XA drivers in order to get the transactions to work. While this should certainly do the trick, it seems to me that the XA stuff is really meant for establishing transactions across different types of systems (say the EJB container and some remote OSS or billing system). Still, I decided to try using the MySQL XA driver. I tried and tried to get things working properly, but the problem remained. Whenever I ran that test method, the year value was incremented.

At one point I really mucked with the XA driver configuration and things stopped working altogether. At that point, I figured I needed to move into a different direction. This was confirmed when I asked a colleague about XA drivers and he mentioned that the product never used them.

Ok, back to the web and more endless digging.

After a few more hours, I just happened to stumble across this article, which talks about how to get CMT working with Hibernate in a JBoss environment. The key section is “Configuring MySQL”.

I didn’t really pay much attention to the table definitions (which may have actually saved me a lot of time, since I may have come across similar definitions before). Instead, I saw this little gem:

As you can see, we are creating tables of the InnoDB type. That’s important. MySQL’s default type is MyISAM. MyISAM is an improved replacement for ISAM, but it’s a non-transactional storage engine and it follows a different paradigm for data integrity, which MySQL calls “atomicoperations.” Again, it’s non-transactional, meaning that it does not support transactions. Obviously, we need a transactional table type, and in MySQL that means InnoDB.

Well, well, well.

When I created the tables initially, I didn’t specify any sort of engine, thus they defaulted to MyISAM. Since this engine does not support transactions, there was no way for the container to undo the first successful update.

Giddy with excitement, I fired up the excellent MySQL Query Browser tool and modified the table definition for the ‘movie’ table to use the InnoDB engine instead of MyISAM. I then ran my test client against the doStuff() EJB method and…

Success! The console output showed the list of movie data, the data with the updated value, and the failure exception. When I queried the database afterward for the current values, the original values were all still in place. The transaction had been completely rolled back. Hurray!

So that’s it. In order to get container-managed transactions to work with a MySQL database, the tables must be configured to use the InnoDB engine. A full table definition script would look something like this:

CREATE TABLE movie (
    id         int(11) NOT NULL auto_increment,
    title      varchar(255) default NULL,
    year       int(4) default NULL,
    PRIMARY KEY (id)
) TYPE=InnoDB;

I hope that this information helps someone avoid all of the headaches I experienced in trying to find out what turned out to be a very simple solution.