Is there a good way to execute MySQL statements atomically via JDBC?
Suppose I have a table that contains valid data. I would like to change this data in some way, but I would like to make sure that when any errors occur with the change, the table will not be modified and the method will return something to this effect.
For example, (this is kind of a silly example, but it illustrates the point that carries me). Suppose I want to edit all the entries in the "name" column so that they are correctly capitalized. For some reason, I want either all names to have the correct capitalization, or NONE of them have the correct capitalization (and the initial state of the table is what NONE of them have).
Is there an already implemented way to run a batch update on a table and be sure that if any of the updates fail, all changes will be rolled back and the table will remain unchanged?
I can think of several ways to do this manually (although suggestions are welcome), but it would be nice if there was some method I could use that would function that way. I looked at the command java.sql.statement.executeBatch()
, but I'm not convinced in the documentation that my table won't get modified if it doesn't work somehow.
a source to share
I hit this question too when I started out with JDBC - it seemed to fly in the face of what I understood about databases and ACID guarantees.
Before you start, make sure your MySQL storage engine supports transactions. MyISAM doesn't support transactions, but InnoDB does.
Then be sure to disable autoCommit JDBC - Connection.setAutoCommit(false)
or JDBC will run each statement as a separate transaction. The end will be all or nothing: there will be no partial changes. Then you run various update statements and finally call Connection.commit()
to commit the transaction.
For more information on JDBC transactions, see the Sun Tutorial .
Using a batch does not change the ACID guarantees - you either transact or you don't! - batch processing is more about collecting multiple operators to improve productivity.
a source to share