Will con.rollback() rollback already commited changes by con.commit()?

Question

I am working on Legacy project now and it requires some deep knowledge of low level JDBC API in some places I see code like this:

try {
    con = ....
    con.setAutoCommit(false);
    //insert_1 pack to db
    con.commit();
    //insert_2 pack to db
    con.commit();
    //insert_3 pack to db
    con.commit();
} catch (SQLException e) {
    try {
        con.rollback();
    } catch (SQLException e) {
        log.warn("SQLException on rolling back the destination connection. ", e);
            throw e;
        }
    throw ex;
}

and sometimes con.rollback(); is not invoked in the the catch:

try {
        con = ....
        con.setAutoCommit(false);
        //insert_1 pack to db
        con.commit();
        //insert_2 pack to db
        con.commit();
        //insert_3 pack to db
        con.commit();
    } catch (SQLException e) {      
        throw new MyBusinessException(ex);
    }

Could you please explain difference from transaction standpoint ?

P.S. I've read java doc for rollback but it doesn't answer my question.

Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled. Throws: SQLException – if a database access error occurs, this method is called while participating in a distributed transaction, this method is called on a closed connection or this Connection object is in auto-commit mode See Also: setAutoCommit

Answer 1

The code looks incorrect.

1. There are several commits executed. This would mean that the explicit rollback could just rollback the state to the beginning of the last commit. This is, I guess, not what the rollback should do.

2. If no explicit rollback is called, at least in a pooled database connection (in a pooled database connection the connection is never closed, but reused) the already executed statements are still in transaction. I.e. calling here a rollback later would lead to strange kind of errors and also rolling back the last executed statements actually not related to the current business process. I see in the Hikari Connection Pool Implementation that dirty states are automatically rolled back, but maybe this correct behavior cannot be assumed for every implementation. E.g. the Apache DBCP is not that straight forward in the code and asking the maintainers if they are doing this should be helpful.

3. I see no close call.

For your questions this means the second code snippet should:

1. In a single non pooled connection not commit the last statements after the last commit, this is what would be correct (by accident).
2. In a pooled connection leave the transactions pending which could lead to the error that the next business logic committing something and reusing the connection will also commit this.