difference between shareable and unshareable connection in jdbc connection pool?

Question

We notice something strange in our struts web application which was hosted on sun app server enterprise edition 8.1.

The NumConnUsed for Monitoring of JDBC resources stays at 100 over connections even though there was relatively very low user activities.

I try to do some research and found the following links http://j2ee-performance.blogspot.com/ http://www.ibm.com/developerworks/websphere/library/techarticles/0506_johnsen/0506_johnsen.html

"When the application closes a shareable connection, the connection is not truly closed, nor is it returned to the free pool. Rather, it remains in the Shared connection pool, ready for another request within the same LTC for a connection to the same resource."

Base on the above comments, it is true that if my web.xml resource ref scope is set to shareable, when application side close conneciton, it remains in the shared conneciton pool thus the numconnused is always so high?

Answer 1

If I interpret the links in my own special way (;)), the shared vs. unshared connections is based on different connections in the same page.

java.sql.Connection connectionOne = DriverManager.getConnection(...);
...
java.sql.Connection connectionTwo = DriverManager.getConnection(...);

These two, at a glance, seem to be individual - but if your AS is set to shareable connections, the second one will be created with a pointer to the first connection instead of returning a new connection. When the page finishes the connection should be sent back to the pool.

The AS is probably keeping the pool filled with connections to enhance performance.

This is not fact, only my own iterpretation of the links.