Is rollback needed if java.sql.Connection#commit() throws exception?

Question

According to JAVA documentation, Connection#commit() can throw SQLException. My question is whether or not a rollback should still be issued in this scenario.

For example:

Connection con = null;
try {
    // assume this method returns an opened connection with setAutoCommit(false)
    con = createConnection(); 

    // do DB stuff

    con.commit();
} catch (SQLException e) {
    if (con != null) {
        // what if con.commit() failed, is this still necessary,
        // will it hurt anything?
        con.rollback();
    }
} finally {
    if (con != null) {
        con.close();
    }
}

I actually wrapped the con.rollback() call into another method which ignores any exceptions thrown by it, so I think I'm ok here. I just wondered if this was the best way of handling things.

Answer 1

I would do explicit rollback just for clean-up purposes. Although changes won't be persisted in db either way, it seems nice to explicitly let database know that you're done here. Just like the way you close connection explicitly, without waiting for Connection object to be garbage-collected.

This is, obviously, not a technical answer and I would also be interested to learn whether there's a practical point in doing so.

Answer 2

"Returns an open connection?" If that connection is shared in a pool (and could be in the future) you don't want another transaction committing your earlier work. I've seen MANY customer/solution cases of plugging in pooled connection driver that comply with JDBC interfaces and Connection.close() can also be used to just return the Connection back to a pool.

Also, better try{}catch{} your rollback() (edit, just read your whole post, but I always like to log an exception on rollback)

Answer 3

The usual way I do this is:

boolean bSuccess = false;
Connection con = null;
try {
    // assume this method returns an opened connection with setAutoCommit(false)
    con = createConnection(); 

    // do DB stuff

    bSuccess = true;
} catch (SQLException e) 
{
}
finally 
{
    try
    {
       if (con != null) 
       {
          if(bSuccess)
             con.commit()
          else
             con.rollback();

          con.close();
       }
    }
    catch(SQLException sqle)
    {
      log("Log the error here");
      // do nothing we tried
    }
}

That being said I have never seen a commit or a rollback fail if the queries worked.
If you have pending transactions then most databases have tools to free them. Most app servers will keep retrying the commits and rollbacks until they can connect.

You might want to look at this post: http://stackoverflow.com/questions/2751129/is-it-necessary-to-write-rollback-if-queries-fail