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.

Solution 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.

Solution 2

Rollback is important even if commit failed, according to the Java 1.6 JDBC docs:

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.

This means that if you do not explicitly invoke rollback, some JDBC implementation might invoke commit before closing the connection.

Another good reason to rollback is as Xepoch suggested and when using a connection pool it is even more important. When getting a connection from a connection pool, most implementations will execute connection.setAutoCommit(defaultAutoCommit) before giving you the connection and according to the JavaDocs:

If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed

If the connection.rollback() throws an exception - then it is a tricky one...

Solution 3

"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)

Solution 4

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: Is it necessary to write ROLLBACK if queries fail?

View more solutions

Comments

[BalusC]

Plus, in case of a pooled connection, it will give a clean connection back in the next lease instead of a dirty one.

[Gili]

This should be the accepted answer. Proof by specification is king.