16

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 ?

Kanagavelu Sugumar
  • 18,766
  • 20
  • 94
  • 101

3 Answers3

12

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.

testing123
  • 11,367
  • 10
  • 47
  • 61
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • A connection pool itself is responsible of restoring the autoCommit() state of returned connections. That is not the responsibility of the person retrieving the connection. – Mark Rotteveel Sep 13 '12 at 18:09
  • @AaronDigulla Could you please elaborate about "rollback() will not always do what you want" ? – Kanagavelu Sugumar Sep 14 '12 at 11:32
  • 1
    When auto commit == true, rollback will only roll back the last statement, not everything since the last commit. – Aaron Digulla Sep 14 '12 at 11:59
  • @AaronDigulla Great! This is new for me :) Could you please answer my update also. I just curious what to do here? – Kanagavelu Sugumar Sep 14 '12 at 12:13
  • @AaronDigulla Thanks for the info that i have to read connection pool documentation. I will. My question here is If i have to perform n DB updates, if all the n updates are successful then only i have to go for commit(). In between the n DB updates how i can guarantee that my connection pool won't perform the DB commit()? – Kanagavelu Sugumar Sep 14 '12 at 12:53
  • 1
    Connection pools never perform commit()s. Your problem is that there might be stale transactions attached to the connection and when the pool gives the same connection to a new piece of code, that second user might commit your buggy transactions. You can find the only way to prevent that in my answer. – Aaron Digulla Sep 14 '12 at 13:15
  • 1
    @AaronDigulla As always you need to read between the lines in the JDBC spec, but section 11.1 of JDBC 4.1 says _"Connection pooling is completely transparent to the client: A client obtains a pooled connection and uses it just the same way it obtains and uses a non pooled connection."_ Given this, the connection pool (or actually the `PooledConnection`-object) is the one who needs to make sure the logical connection it returns looks and behaves exactly like a non-pooled physical connection. This includes making sure the connections handed out are always in a consistent state. – Mark Rotteveel Sep 15 '12 at 08:18
  • 2
    @AaronDigulla Also connection pools can (and I believe most do) commit when the logical connection is closed, because that is also allowed for physical connections (_" If the `close` method is called and there is an active transaction, the results are implementation-defined."_) – Mark Rotteveel Sep 15 '12 at 08:26
  • `conn.setAutoCommit(autoCommit);` should be in `finally` block – kingoleg Oct 12 '21 at 17:27
  • @Aaron Digulla Could you explain rollback behaviour if commit is done several times manually ? – gstackoverflow Oct 21 '21 at 10:42
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.

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
1

In Oracle 12c connection will be defaulted to the autocommit true. But if you set the autocommit as false, you need to reset the autocommit as true before release to the connection pool. conn.setAutoCommit(autoCommit); should move to the finally block

rajeev
  • 59
  • 5