Database connection settings retrieved from a JNDI connection pool

Question

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.

Answer 1

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.