Database connection settings retrieved from a JNDI connection pool

I have hosted a data source on the Websphere server and I wanted make use of that data source through my application开发者_运维知识库 clients.

After retrieving the database connection from the JNDI data source, I am changing the commit setting of the connection to false. After that, I will make use of this connection and I will close the connection after the completion of the task.

What happens if I forget to change the commit setting of the connection before closing (I mean returning the connection to the pool). If any other client accesses this data source and they get the same connection, does the commit settings still persists or will the server reset these settings of the connections.

Normally connections in the pool are wrapped with a AS-specific implementation, which takes care of the client being too intrusive. When specific methods are called on the connection, it is considered "dirty", and may not be returned to pool, but closed and recreated instead, or reset to original state, if possible. There are often settings on how to deal with those situations, e.g. Remove Infected Connections in Weblogic.

The effect of those self-cleansing though could depend on the driver as well. Therefore I suggest you do a simple test with a 1-connection pool. Set connection to autocommit=false, do not close it, exit and try to use it from another client with a test checking the actual state of autocommit property.

Another thing to consider is that Connection.close() in that AS wrapper doesn't close the connection, but places it into the pool. Therefore if you client disconnects before calling close() (and setting autocommit back right before that), the connection may be not available to other pool clients, creating a connection leak.

Tested on Apache Tomcat + Postgres - if defaultAutoCommit at server.xml <Resource ..../> is not specified - returned database connection remains modified, that is, you have to set it manually conn.setAutoCommit(true); before closing (returning). But defaultAutoCommit="true" helps in this situation. Apache Tomcat 7 - The Tomcat JDBC Connection Pool explains defaultAutoCommit - "(boolean) The default auto-commit state of connections created by this pool. If not set, default is JDBC driver default (If not set then the setAutoCommit method will not be called.)"