Is there a good way to execute MySQL statements atomically via JDBC?

Question

Suppose I have a table that contains valid data. I would like to modify this data in some way, but I'd like to make sure that if any errors occur with the modification, the table isn't changed and the method returns something to that effect.

For instance, (this is kind of a dumb example, but it illustrates the point so bear with me) suppose I want to edit all the entries in a "name" column so that they are properly capitalized. For some reason, I want either ALL of the names to have proper capitalization, or NONE of them to have proper capitalization (and the starting state of the table is that NONE of them do).

Is there an already-implemented way to run a batch update on the table and be assured that, if any one of the updates fails, all changes are rolled back and the table remains unchanged?

I can think of a few ways to do this by hand (though suggestions are welcomed), but it'd be nice if there was some method I could use that would function this way. I looked at the java.sql.statement.executeBatch() command, but I'm not convinced by the documentation that my table wouldn't be changed if it failed in some manner.

Answer 1

I hit this one too when starting with JDBC - it seemed to fly in the face of what I understood about databases and ACID guarantees.

Before you begin, be sure that your MySQL storage engine supports transactions. MyISAM doesn't support transactions, but InnoDB does.

Then be sure to disable JDBC autoCommit - Connection.setAutoCommit(false), or the JDBC will run each statement as a separate trasaction. The commit will be an all or nothing affair - there will be no partial changes. Then you run your various update statements, and finally call Connection.commit() to commit the transaction.

See the Sun Tutorial for more details about JDBC transactions.

Using a batch does not change the ACID guarantees - you're either transacted or you're not! - batching is more about collecting multiple statements together for improved performance.