Tomcat connection pool auto-commit

[Justin Carter]

I noticed an interesting fact about our connection pool. When the autoCommit value is changed in a connection, the changed value is kept even after the connection is closed (returned to the pool) and then requested again by another segment of code. In other words, the auto-commit never returns to the default value.

Naturally, this might cause unexpected results if the code doesn't explicitly call setAutoCommit() each time. I was wondering, is this a bug in the connection pool? Does the JDBC spec have any details on what should happen to the auto-commit value when the connection is closed?

We are using the newer Tomcat connection pool (included with Tomcat 7).

Update:

Here is what I found in the JDBC spec regarding auto-commit (JDBC spec 4.0, section 10.1.1):

The default is for auto-commit mode to be enabled when the Connection object is
created. If the value of auto-commit is changed in the middle of a transaction, the
current transaction is committed. If setAutoCommit is called and the value for
auto-commit is not changed from its current value, it is treated as a no-op.

This doesn't mention anything about connection pools, but I would think that code should expect the same behavior from a connection whether it is pooled or otherwise.

[Martin Vashko]

This has to be defined by the connection pool, as the connection itself does not have any means to handle this. When you close a pooled connection, the physical connection probably does not even know about it. The connection pool would have to reset its state. This might be a problem, because database connections have states beyond autocommit, including ones the connection pool has no way of knowing about (such as default transaction isolation level, decimal separator setting for implicit conversions, or even some database specific application contexts, or the current schema in Oracle). It's therefore the developer's responsibility to manage these states.

One possible approach is to reset everything in a custom connection factory method (I do this in one project - the factory method sets autocommit to false and then sets the current schema). Other possibility is to always restore connection states in finally blocks.

Some connection pools have methods which allow you to "tag" connections with some attributes. You might use this to store information about a connection state with the connection. I once considered a scheme which would assign a certain tag to a connection which was already initialized, so I wouldn't have to needlessly reinitialize a connection I obtain from a pool which is already marked with the tag.

[Justin Carter]

Thanks for your response, and your explanation was helpful. I ended up using a factory method to set the auto-commit value to the default value, as you mentioned in one of your solutions.

[Pete Kzzzzzzzzzz]

Hi - I had similar problems with programs we were calling.
We had recently started using the Tomcat JDBC pool instead of the Apache pool instead of the Apache Commons DBCP pooling.
Best info I found was at http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html (where else?!)

The short answer is that you have to set the default autocommit attribute to true AND you have to set JDBC Interceptor value (see below example if you're creating the pool via Java directly, or see the documentation for tomcat for the attributes ).

tcPoolProperties.setDefaultAutoCommit(true); 
tcPoolProperties.setJdbcInterceptors("ConnectionState;StatementFinalizer");

The longer story...
For our app, I wanted connections to default to autocommit=true. I had set default autocommit to true, but it wasn't working as expected - connections would keep the same true-or-false autocommit setting until specifically set - even when returned-to-and-got-back-from the pool.

As soon as I set the jdbcInterceptors attribute it started working.
Stuff to note...
The documentation says that regarding when you don't set defaultAutoCommit : "...If not set then the setAutoCommit method will not be called."
People reading this should note that I recently switched from using Apache DBCP to using Tomcat. The word doesn't seem to be out among all developers that Tomcat 7 now has this improvement.

DBCP as included in Tomcat 7 and there are significant improvements. Yeah, and some gotcha's like this.

I've found some discussions on the web where people have described this but say that after a long time it starts to break again. With that in mind I've decided to put a check in that will set autocommit=true where necessary and log an error for me to look into it further if-and-when it happens.