is setautocommit(true) needed after conn.commit()

Question

Got the db connection (conn) from the pool.

Assume that autocommit is TRUE on that connection.

Now conn.setautocommit(false) has set ;

then after few statement updates and finally conn.commit()/conn.rollback() has done.

Now do i need to do explicitly code setautocommit(true) to revert to the previous conn state?

OR commit()\rollback() will set setautocommit(true) inherently ?

Answer 1

That depends on where you got that connection from. If you created the connection yourself, there is no need to restore the state of auto commit.

If you got it from a data source, you should restore the state to what it was because the data source might keep the connections in a pool and the next piece of code might not expect what you set.

commit() doesn't influence the value of auto commit. Enabling auto commit just makes sure that the JDBC driver calls commit() after each statement that you execute. You can still call commit() as often as you like, it just won't have any effect (except that rollback() will not always do what you want).

[EDIT] How auto commit is handled depends on your connection pool. dbcp has a config option to turn auto commit off before giving you a connection, c3p0 will roll back connections when you return then to the pool. Read the documentation for your connection pool how it works.

If you don't know which pool is used, the safe solution is to set auto commit to false whenever you get a connection and to roll back the connection if you get an exception. I suggest to write a wrapper:

public <T> T withTransaction( TxCallback<T> closure ) throws Exception {
    Connection conn = getConnection();
    try {
        boolean autoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);

        T result = closure.call(conn); // Business code

        conn.commit();
        conn.setAutoCommit(autoCommit);
    } catch( Exception e ) {
        conn.rollback();
    } finally {
        conn.close();
    }
}

This code will correctly handle the connection for you and you don't need to worry about it anymore in your business code.

Answer 2

Interestingly, conn.setAutoCommit(true); implies a commit (if it's in autoCommit(false) mode, see here, but it might be clearer to people if you still break them out.