commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Seva Popov" <>
Subject RE: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not reuse/pool the db connections
Date Wed, 20 Jun 2007 07:06:10 GMT
First of all, let me say thank you for your time and for the most useful detailed responses.
I've just realized that you are one of the DBCP's project developers and it's nice to know
that you guys care about the project and its users.
Now, I understand how the DBCP's InstanceKeyDataSource pool is communicating with the CPDS
implementation. My previous understanding of the contract between a pool manager and a CPDS
was wrong but I guess I got it right now. The provided links to the Oracle documentation proved
to be very useful and explanatory as well.
Yes, I am using an older Oracle version 9i with the ojdbc14-9i client driver jar. The main
source of confusion I believe stems from the fact that in this version of Oracle the OracleDataSource
and the OracleConnectionPoolDataSource both return the object of type OracleConnection when
the client calls ds.getConnection(). However, in the former case the OracleConnection object
represents a physical database connection but in the latter case the same OracleConnection
object is a logical connection i.e. the temporary handle to the physical database connection.
I guess Oracle guys realized that this was not very good and cleaned up their jdbc implementation
in the later releases. Yet, it'd be interesting to decompile the OracleConnection and see
how it's working, for example, look at its close() method.
Anyway, I've tried to check the number of physical connections open in my test. I do not have
Oracle monitor installed nor could I use the ps since I am using the Oracle jdbc thin driver
in my configuration (I guess you used oci driver + oci client but that does not replicate
my test configuration).
But, concluding from the results of netstat -apn | grep 1521, I did verified that there are
only 2 physical connections open to the database after I've run the test.
So, the pool is working correctly and all my questions are resolved. Thanks again for support.


From: Phil Steitz []
Sent: Tue 6/19/2007 7:09 PM
To: Jakarta Commons Users List
Subject: Re: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not reuse/pool the
db connections

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