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.

Related: http://stackoverflow.com/questions/3160756/in-jdbc-when-autocommit-is-false-and-no-explicit-savepoints-have-been-set-is-it — BalusC, Sep 24 '10 at 21:19
I think neither of the answers consider the case when the connection becomes invalid (when DB goes down) in which case the rollback is not possible (it will throw an SQL exception); though internally the DB does a rollback. So you should either catch this when calling rollback or determine the cause of the SQLException, usually parsing the cause. — arpadf, Jan 23 '15 at 10:28

MosheElisha · Answer 1 · 2013-05-16T04:56:24.640

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

This should be the accepted answer. Proof by specification is king. — Gili, May 14 '13 at 23:39

score 6 · Accepted Answer · answered Sep 24 '10 at 21:08

6

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.

answered Sep 24 '10 at 21:08

Nikita Rybak

67,365
22
157
181

4

Plus, in case of a pooled connection, it will give a clean connection back in the next lease instead of a dirty one. – BalusC Sep 24 '10 at 21:19

score 4 · Answer 3 · answered Sep 24 '10 at 21:11

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

score 1 · Answer 4 · edited May 23 '17 at 11:47

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?

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

4 Answers4