In JDBC, when autocommit is false and no explicit savepoints have been set, is it good style or a waste to rollback?
java, jdbc
Solution
The normal idiom is the following:
public void executeSomeQuery() throws SQLException {
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try (PreparedStatement statement = connection.prepareStatement(SOME_SQL)) {
// Fire transactional queries here.
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
}
}
}
Note that Java 7's try-with-resources statement always implicitly calls `close()` on the resource when the `try` block finishes, as if it happens in `finally`.
Calling `rollback()` is also mandatory when it concerns a pooled connection. Namely, it will reset the transactional state of the connection. The `close()` of a pooled connection won't do that, only the `commit()` and `rollback()` will do that. Not calling `rollback()` may lead that the next lease of the pooled connection will still have the (successful) queries of the previous transaction in its memory.
See also javadoc of `Connection#close()` (emphasis not mine):
It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the `close` method. If the `close` method is called and there is an active transaction, the results are implementation-defined.
Problem
Say you have the following code: ``` Connection conn; try { conn = ... // get connection conn.setAutoCommit(false); ... // Do some modification queries and logic conn.commit() } catch(SQLException e) { conn.rollback() // Do we need this? conn.close() } ``` In this code, if there is an exception, is it better style to just close the connection (since autocommit is off), or to explicitly roll back and then close the connection? There are no save points. I feel that it might make sense to add the rollback call because: 1) Someone, in the future, might add save points but forget to add the rollback 2) It improves readability 3) It shouldn't cost anything, right ? But obviously, none of these is particularly compelling. Any standard practice? Note: I'm aware of the need to do a repeat try/catch on closing and rollback. I actually have a middleware that abstracts the database access and takes care of that, but I was wondering whether adding it was superfluous.