In JDBC, when autocommit is false and no explicit savepoints have been set, is it good style or a waste to rollback?

37
votes

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.

2
javajdbc
Good question. I'm interesting in the answer to your 3rd item because we used to always do rollback before returning a connection, but at one point our DBA noticed this and claimed, quite religiously, that rollbacks were expensive. I didn't get a good motivation, but we gave in and basically made sure rollback() was only called if it was needed (which was easy because it was only one place in the code that was affected). - waxwing
Could anybody answer this question with Java 7 try-with-resource? Would this simplify the code that has to be written? (Related question, but does not explain how to use try-with-resource in combination with autoCommit: stackoverflow.com/questions/8066501/…) - user1202136

2 Answers

46
votes

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.

2
votes

Closing should rollback because it will not commit when the resources are release, but it's good to have your error handling be specific, so if you want to rollback on an exception, do that. Then you can do your cleanup in a finally{} block. The rollback() occurs only on error, in which case your commit() is not successful or was not even reached.

Connection conn = null;
try {
    conn = ...

    ...
    conn.commit();
}
catch (SQLException e) {
    if (conn != null) {
        conn.rollback();
    }
}
finally {
    if (conn != null) {
        conn.close();
    }
}