commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Steitz" <>
Subject Re: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not reuse/pool the db connections
Date Wed, 20 Jun 2007 02:09:24 GMT
On 6/18/07, Seva Popov <> wrote:
> Hi Phil,
> Here is the config for the physical connections:
>          <Resource name="jdbc/MyCPDS"
>             auth="Container"
>             type="oracle.jdbc.pool.OracleConnectionPoolDataSource"
>             factory="oracle.jdbc.pool.OracleDataSourceFactory"
>             url="jdbc:oracle:thin:@some_url:1521:some_sid"/> >
> Yes, I know about the differences between PerUserPoolDataSource and
> SharedPoolDataSource and I agree that the test for PerUserPoolDataSource
> was not valid as you've indicated and I will try it again.
Actually, looking at the example again, even with the defaults it
should only open two connections.  See comments below.

> However, I've used the same configuration for the SharedPoolDataSource
> with the same negative results.
> Also, in both cases when I've used ds.getConnection() I've obtained the
> raw Oracle connection but I've expected to get
> org.apache.tomcat.dbcp.dbcp.PoolableConnection as with the
> BasicDataSource.  Note, that I am not expecting to get
> OraclePooledConnection as you've indicated as this would be wrong as the
> OraclePooledConnection (that implements javax.sql.PooledConnection) is
> supposed to be used internally by the Pool Manager implementation. The
> OraclePooledConnection does not implement java.sql.Connection.  So, what
> the DBCP's Pool Manager should do is to wrap the OraclePooledConnection
> with the org.apache.tomcat.dbcp.dbcp.PoolableConnection (or some other
> wrapper that implements java.sql.Connection interface) and return the
> latter to the client.
> Is it not the case with DBCP?

InstanceKeyDataSource (which both PerUserPoolDataSource and
SharedPoolDataSource extend) does not wrap connection handles returned
by ConnectionPoolDataSource impls.  The pool maintains
PooledConnections obtained from the configured
ConnectionPoolDataSource and the InstanceKeyDataSource getConnection
method returns the result of getConnection applied to
PooledConnections in the pool.  The client should get back connection
handles provided by the driver.  The pool (actually the factory)
implements the ConnectionEventListener interface.

Tomcat 5.5.9 bundles (repackaged) commons dbcp 1.2.1.  You can see the
sources for this version of dbcp either by downloading the 1.2.1
source distribution here:
or online via the svn viewer:

I am by no means an Oracle expert, but using Oracle Database 10g
Express Edition Release and the client driver jar it ships
with on Fedora Core 4, tomcat 5.5.9,  I think I was able to verify
correct functioning of both PerUserPoolDataSource and
SharedPoolDataSource with your example jsp.  My output for
PerUserPoolDataSource looks like this:

DataSource: org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource@15bdc50
Connection 1: oracle.jdbc.driver.LogicalConnection@e020c9
Connection 2: oracle.jdbc.driver.LogicalConnection@39e5b5
Connection 3: oracle.jdbc.driver.LogicalConnection@117f31e

Note the difference in types from your output.  The LogicalConnections
are handles.  Looking at the Oracle monitor and also ps, I can see
that there are in fact only 2 physical connections open to the
database (at least that what it looks like to me - as I said, I am not
an Oracle expert, I am concluding this from looking at "sessions"
identified in the web monitor and ps -aux | grep Oracle on the command

What version of Oracle and Oracle drivers are you using?  From the docs here:
it looks like previous versions somehow used OracleConnection
instances as handles (as your output above indicates).  Can you check
the number of physical connections open somehow?


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message